| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-25-07, 00:58
|
|
Registered User
|
|
Join Date: Sep 2006
Posts: 162
|
|
|
Image into single cell
|
|
How can i get an image to resize and fit in a single cell? Ideal would be able to drag and drop pics, but this won't work unless there is a work around using vba.
Another option is to have a button/link to add image.
TIA
|
|
02-27-07, 16:18
|
|
Registered User
|
|
Join Date: Sep 2006
Posts: 162
|
|
Any code that will ask for location of image and resize and insert??
|
|
02-28-07, 13:31
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
|
|
Perhaps another work around is to put the image into a Comment for that cell. Will that work?
A picture can be included in a Comment to store a photo of a product or employee near the cells that contain that specific information.
1. Select cell that contains a Comment, right-click and from the shortcut menu select Show Comment.
2. Select the edge of Comment so that Comment is surrounded by dots, not by slashes.
3. Right-click and select Format Comment > Colors and Lines > Fill.
4. Open the Color box > Fill Effects > click the Picture tab, and click select Picture.
5. Select a picture and click OK. Resize Comment as appropriate.
|
Last edited by shades; 02-28-07 at 13:37.
|
02-28-07, 13:50
|
|
Registered User
|
|
Join Date: Sep 2006
Posts: 162
|
|
I was looking into that as an option. What would I have to do to make a vba code to do this auto. I want a link that will ask location of file then add to comment for that cell.
|
|
02-28-07, 15:44
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
Tom Urtis had developed something that you might adapt.
Code:
Sub PlaceGraph()
' Page 306
Dim x As String, z As Range
Application.ScreenUpdating = False
x = "C:\XWMJGraph.gif"
Set z = Worksheets("ChartInComment").Range("A3")
On Error Resume Next
z.Comment.Delete
On Error GoTo 0
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Export x
With z.AddComment
With .Shape
.Height = 322
.Width = 465
.Fill.UserPicture x
End With
End With
Range("A1").Activate
Application.ScreenUpdating = True
Set z = Nothing
End Sub
This puts a graph into the comment for cell A3. Modify the path, and cell as needed. Depending on your set up, you could have all images in one directory and labeled in such a way that you could set up a loop in this code and assign each image in one step.
the reason this is a preferred action to actually in the cell itself, is that you don't have to worry about screen resolution, and pixel differences and positioning of the cell relative to pixel placement of image.
|
|
02-28-07, 15:52
|
|
Registered User
|
|
Join Date: Sep 2006
Posts: 162
|
|
The pic is gonna change on a often, its for a qc tech who will take photos of work that need to be added to the spreadsheet as he goes. Any code available to pull a prompt up to browse for the image.
Found this
Quote:
Sub Macro1()
Dim fn
fn = Application.GetOpenFilename 'can add parameters. See help for details.
If fn = False Then
MsgBox "Nothing Chosen"
Else
MsgBox "You chose " & fn
'now that you have the name, you can open it or do something else
End If
End Sub
|
How can i set the file i select as a variable and make it a fill for a comment.
|
Last edited by Jay59; 02-28-07 at 16:19.
|
02-28-07, 16:27
|
|
Registered User
|
|
Join Date: Sep 2006
Posts: 162
|
|
Found this, gonna work on using it with the last snippet.
Quote:
Sub AddPictureToComment()
Dim rng As Range
Dim shp As Comment
Set rng = ActiveCell
If Not rng.Comment Is Nothing Then
rng.Comment.Delete
End If
If rng.Text <> "" Then
Set shp = rng.AddComment("")
shp.Shape.Fill.UserPicture rng.Text
End If
End Sub
|
|
|
02-28-07, 16:34
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
On the code I posted you can omit these lines:
Code:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Export x
Since that was related to a chart, not an image.
|
|
02-28-07, 16:37
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
To look for a file, try this:
Code:
myFile = Application.GetOpenFilename
Workbooks.Open Filename:=myFile
|
|
03-01-07, 17:44
|
|
Registered User
|
|
Join Date: Sep 2006
Posts: 162
|
|
This is what I came up with.
Quote:
Sub Macro()
Dim rng As Range
Dim shp As Comment
Set rng = ActiveCell
If ActiveCell.Value = "Click to add pic" Then
Dim fn
fn = Application.GetOpenFilename
If fn = False Then
Else
Set shp = rng.AddComment("")
shp.Shape.Fill.UserPicture fn
shp.Shape.Height = 322
shp.Shape.Width = 465
End If
ActiveCell = "QC Pic"
Else
End If
End Sub
|
|
Last edited by Jay59; 03-01-07 at 17:52.
|
03-01-07, 20:23
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
So, I assume you got it to work the way you want?
|
|
03-01-07, 20:29
|
|
Registered User
|
|
Join Date: Sep 2006
Posts: 162
|
|
Yeap, just gotta get it to run the macro when you select the cell.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Image into single cell
