Insert Picture using Macro
in [Programming]
|
From: marc747 on 9 Apr 2010 19:50 Hi, The first Code is working but not the way that I can use. The last one I can't make it work. Is it possible the keep my original codes most of it as is and only add a few lines of code so that instead of the macro looking for the picture by name and extension to only look for the name of the picture file by name only no matter what the extension is. I appreciate your help.... Thanks On Apr 8, 5:08 am, "Peter T" <peter_t(a)discussions> wrote: > Well the sample code I suggested does work, though it may not with your > implementation of it. > > From what I understand, I'd suggest you maintain a Lookup table somewhere, > perhaps on a hidden sheet or columns. > > UserDisplayName : FileName > Green Apples : grnApples.jpg > Pink Roses : pnkRoses.jpg > unknown : Picture_not_Available.jpg > > ' warning air-code ! > s = Range("L9" ).text > > arr = range("fileList").value ' where fileList refers to the lookup table > > For i = 1 to ubound(arr) > if s = arr(i,1) then > sFile = arr(i,2) > ExitFor > end If > Next > > ifLen(sFile) = 0 then > sFile = Picture_not_Available.jpg > end if > pos = InStrRev(sFile, ".") > sPicName = Left$(sFile, pos - 1) > > on error resume next > Set shr = activesheet.Pictures(sPicName).ShapeRange > on error goto 0 > > if shr is nothing then > sFile = thisworkbook.path & "\" & sFile > Set shr = activesheet.Pictures.Insert(sFile).ShapeRange > End if > > shr.name = sPicName > > etc ' change properties of shr for location, size etc > > Regards, > Peter T > > "marc747" <marc...(a)excite.com> wrote in message > > news:61675848-09eb-4125-8c2e-caa02562fec4(a)q23g2000yqd.googlegroups.com... > Hi, > > The code did not work. > Basically what I use the code for is that I have in Cell "L21" a > Validation List with Picture names and every time I select a name from > the list the picture in cell "L9" changes and insert the picture with > the name on the list that I selected and incase there is no picture > with the name that I selected it will insert a default picture called > "Picture_not_Available.jpg" > > The thing is that I don't want the name of the pictures on my list to > show the picture file name with the file extension, therefore I wanted > to make a change in the Macro that I have so that it will look for the > picture by name only no mater what the file extension is. > > Thanks, > > Armond > > ******************************************************* > > On Apr 7, 2:13 am, "Peter T" <peter_t(a)discussions> wrote: > > > > > Just for what you are doing you don't need the name at all (though you > > might > > want to give it a name and store it for some future process) > > > Dim shr As ShapeRange > > > 'code > > > Set shr = activesheet.Pictures.Insert(filename).ShapeRange > > > now replace all "Selection.ShapeRange" with "shr" > > > Hmm, had anther glance at your code, not sure I quite follow what you > > aiming > > to do overall. If the above is not enough revert back and explain. > > > Regards, > > Peter T > > > "marc747" <marc...(a)excite.com> wrote in message > > >news:04edf68e-1568-49ee-8653-78677f424c8f(a)5g2000yqj.googlegroups.com... > > > > Hi, > > > > I have the following Macro but it seems that I need the name of the > > > picture along with the extension in order to work, is there a way to > > > include the extensions (.gif, .jpg, .jpeg, and more that I don't > > > know ........) into the Macro so that I don't need to include the > > > picture name with the extension. > > > Thank You. > > > > ----------------------------------- > > > Private Sub Worksheet_Change(ByVal Target As Range) > > > Dim myScale As Double > > > If Target.Address <> "$L$21" Then Exit Sub > > > > 'Select the cell where the picture is placed > > > Application.EnableEvents = False > > > On Error Resume Next > > > ActiveSheet.Shapes("KnownPictureName").Delete > > > On Error GoTo 0 > > > > Range("L10").Select > > > 'Insert the picture > > > On Error GoTo NoPic > > > > 'this is the one with the link to the file that I need the extension > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\" & > > > Range("L21").Value).Select > > > > GoTo GotPic > > > NoPic: > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\No Pic.jpg").Select > > > GotPic: > > > 'scale the picture to the width of the column > > > myScale = 42 / Selection.ShapeRange.Height > > > Selection.Name = "KnownPictureName" > > > Selection.ShapeRange.ScaleWidth myScale, msoFalse, > > > msoScaleFromTopLeft > > > Selection.ShapeRange.ScaleHeight myScale, msoFalse, > > > msoScaleFromTopLeft > > > Range("L22").Select > > > > Application.EnableEvents = True > > > End Sub > > > ------------------------------------------------ Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text -
From: Peter T on 11 Apr 2010 07:56 I don't follow what you are asking in addition to what I already suggested. Try and explain the logic of how you want to get from a file name to the name of a picture, eg, did you already name the picture as that of the filename without the extension. Better still, describe why you cannot use the "first" code and you cannot make the "last" one work. Not sure what you mean by first and last, there was only one set of code. Regards, Peter T "marc747" <marc747(a)excite.com> wrote in message news:1fab5e4f-76e1-43a6-8a59-9373ea12123a(a)e7g2000yqf.googlegroups.com... Hi, The first Code is working but not the way that I can use. The last one I can't make it work. Is it possible the keep my original codes most of it as is and only add a few lines of code so that instead of the macro looking for the picture by name and extension to only look for the name of the picture file by name only no matter what the extension is. I appreciate your help.... Thanks On Apr 8, 5:08 am, "Peter T" <peter_t(a)discussions> wrote: > Well the sample code I suggested does work, though it may not with your > implementation of it. > > From what I understand, I'd suggest you maintain a Lookup table somewhere, > perhaps on a hidden sheet or columns. > > UserDisplayName : FileName > Green Apples : grnApples.jpg > Pink Roses : pnkRoses.jpg > unknown : Picture_not_Available.jpg > > ' warning air-code ! > s = Range("L9" ).text > > arr = range("fileList").value ' where fileList refers to the lookup table > > For i = 1 to ubound(arr) > if s = arr(i,1) then > sFile = arr(i,2) > ExitFor > end If > Next > > ifLen(sFile) = 0 then > sFile = Picture_not_Available.jpg > end if > pos = InStrRev(sFile, ".") > sPicName = Left$(sFile, pos - 1) > > on error resume next > Set shr = activesheet.Pictures(sPicName).ShapeRange > on error goto 0 > > if shr is nothing then > sFile = thisworkbook.path & "\" & sFile > Set shr = activesheet.Pictures.Insert(sFile).ShapeRange > End if > > shr.name = sPicName > > etc ' change properties of shr for location, size etc > > Regards, > Peter T > > "marc747" <marc...(a)excite.com> wrote in message > > news:61675848-09eb-4125-8c2e-caa02562fec4(a)q23g2000yqd.googlegroups.com... > Hi, > > The code did not work. > Basically what I use the code for is that I have in Cell "L21" a > Validation List with Picture names and every time I select a name from > the list the picture in cell "L9" changes and insert the picture with > the name on the list that I selected and incase there is no picture > with the name that I selected it will insert a default picture called > "Picture_not_Available.jpg" > > The thing is that I don't want the name of the pictures on my list to > show the picture file name with the file extension, therefore I wanted > to make a change in the Macro that I have so that it will look for the > picture by name only no mater what the file extension is. > > Thanks, > > Armond > > ******************************************************* > > On Apr 7, 2:13 am, "Peter T" <peter_t(a)discussions> wrote: > > > > > Just for what you are doing you don't need the name at all (though you > > might > > want to give it a name and store it for some future process) > > > Dim shr As ShapeRange > > > 'code > > > Set shr = activesheet.Pictures.Insert(filename).ShapeRange > > > now replace all "Selection.ShapeRange" with "shr" > > > Hmm, had anther glance at your code, not sure I quite follow what you > > aiming > > to do overall. If the above is not enough revert back and explain. > > > Regards, > > Peter T > > > "marc747" <marc...(a)excite.com> wrote in message > > >news:04edf68e-1568-49ee-8653-78677f424c8f(a)5g2000yqj.googlegroups.com... > > > > Hi, > > > > I have the following Macro but it seems that I need the name of the > > > picture along with the extension in order to work, is there a way to > > > include the extensions (.gif, .jpg, .jpeg, and more that I don't > > > know ........) into the Macro so that I don't need to include the > > > picture name with the extension. > > > Thank You. > > > > ----------------------------------- > > > Private Sub Worksheet_Change(ByVal Target As Range) > > > Dim myScale As Double > > > If Target.Address <> "$L$21" Then Exit Sub > > > > 'Select the cell where the picture is placed > > > Application.EnableEvents = False > > > On Error Resume Next > > > ActiveSheet.Shapes("KnownPictureName").Delete > > > On Error GoTo 0 > > > > Range("L10").Select > > > 'Insert the picture > > > On Error GoTo NoPic > > > > 'this is the one with the link to the file that I need the extension > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\" & > > > Range("L21").Value).Select > > > > GoTo GotPic > > > NoPic: > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\No Pic.jpg").Select > > > GotPic: > > > 'scale the picture to the width of the column > > > myScale = 42 / Selection.ShapeRange.Height > > > Selection.Name = "KnownPictureName" > > > Selection.ShapeRange.ScaleWidth myScale, msoFalse, > > > msoScaleFromTopLeft > > > Selection.ShapeRange.ScaleHeight myScale, msoFalse, > > > msoScaleFromTopLeft > > > Range("L22").Select > > > > Application.EnableEvents = True > > > End Sub > > > ------------------------------------------------ Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text -
From: marc747 on 12 Apr 2010 12:38 Hi, Yes, my file names are without file extensions. I have the file names in a validation list in Cell "L21" and everytime that I pick a file name from that list the picture in cell "L9" changes and if no picture is availbale for that file name a default picture is inserted "C: \mypictures\Picture_not_Available.jpg". Thanks. On Apr 11, 4:56 am, "Peter T" <peter_t(a)discussions> wrote: > I don't follow what you are asking in addition to what I already suggested. > Try and explain the logic of how you want to get from a file name to the > name of a picture, eg, did you already name the picture as that of the > filename without the extension. > > Better still, describe why you cannot use the "first" code and you cannot > make the "last" one work. Not sure what you mean by first and last, there > was only one set of code. > > Regards, > Peter T > > "marc747" <marc...(a)excite.com> wrote in message > > news:1fab5e4f-76e1-43a6-8a59-9373ea12123a(a)e7g2000yqf.googlegroups.com... > Hi, > > The first Code is working but not the way that I can use. The last one > I can't make it work. > > Is it possible the keep my original codes most of it as is and only > add a few lines of code so that instead of the macro looking for the > picture by name and extension to only look for the name of the picture > file by name only no matter what the extension is. I appreciate your > help.... > > Thanks > > On Apr 8, 5:08 am, "Peter T" <peter_t(a)discussions> wrote: > > > > > Well the sample code I suggested does work, though it may not with your > > implementation of it. > > > From what I understand, I'd suggest you maintain a Lookup table somewhere, > > perhaps on a hidden sheet or columns. > > > UserDisplayName : FileName > > Green Apples : grnApples.jpg > > Pink Roses : pnkRoses.jpg > > unknown : Picture_not_Available.jpg > > > ' warning air-code ! > > s = Range("L9" ).text > > > arr = range("fileList").value ' where fileList refers to the lookup table > > > For i = 1 to ubound(arr) > > if s = arr(i,1) then > > sFile = arr(i,2) > > ExitFor > > end If > > Next > > > ifLen(sFile) = 0 then > > sFile = Picture_not_Available.jpg > > end if > > pos = InStrRev(sFile, ".") > > sPicName = Left$(sFile, pos - 1) > > > on error resume next > > Set shr = activesheet.Pictures(sPicName).ShapeRange > > on error goto 0 > > > if shr is nothing then > > sFile = thisworkbook.path & "\" & sFile > > Set shr = activesheet.Pictures.Insert(sFile).ShapeRange > > End if > > > shr.name = sPicName > > > etc ' change properties of shr for location, size etc > > > Regards, > > Peter T > > > "marc747" <marc...(a)excite.com> wrote in message > > >news:61675848-09eb-4125-8c2e-caa02562fec4(a)q23g2000yqd.googlegroups.com.... > > Hi, > > > The code did not work. > > Basically what I use the code for is that I have in Cell "L21" a > > Validation List with Picture names and every time I select a name from > > the list the picture in cell "L9" changes and insert the picture with > > the name on the list that I selected and incase there is no picture > > with the name that I selected it will insert a default picture called > > "Picture_not_Available.jpg" > > > The thing is that I don't want the name of the pictures on my list to > > show the picture file name with the file extension, therefore I wanted > > to make a change in the Macro that I have so that it will look for the > > picture by name only no mater what the file extension is. > > > Thanks, > > > Armond > > > ******************************************************* > > > On Apr 7, 2:13 am, "Peter T" <peter_t(a)discussions> wrote: > > > > Just for what you are doing you don't need the name at all (though you > > > might > > > want to give it a name and store it for some future process) > > > > Dim shr As ShapeRange > > > > 'code > > > > Set shr = activesheet.Pictures.Insert(filename).ShapeRange > > > > now replace all "Selection.ShapeRange" with "shr" > > > > Hmm, had anther glance at your code, not sure I quite follow what you > > > aiming > > > to do overall. If the above is not enough revert back and explain. > > > > Regards, > > > Peter T > > > > "marc747" <marc...(a)excite.com> wrote in message > > > >news:04edf68e-1568-49ee-8653-78677f424c8f(a)5g2000yqj.googlegroups.com.... > > > > > Hi, > > > > > I have the following Macro but it seems that I need the name of the > > > > picture along with the extension in order to work, is there a way to > > > > include the extensions (.gif, .jpg, .jpeg, and more that I don't > > > > know ........) into the Macro so that I don't need to include the > > > > picture name with the extension. > > > > Thank You. > > > > > ----------------------------------- > > > > Private Sub Worksheet_Change(ByVal Target As Range) > > > > Dim myScale As Double > > > > If Target.Address <> "$L$21" Then Exit Sub > > > > > 'Select the cell where the picture is placed > > > > Application.EnableEvents = False > > > > On Error Resume Next > > > > ActiveSheet.Shapes("KnownPictureName").Delete > > > > On Error GoTo 0 > > > > > Range("L10").Select > > > > 'Insert the picture > > > > On Error GoTo NoPic > > > > > 'this is the one with the link to the file that I need the extension > > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\" & > > > > Range("L21").Value).Select > > > > > GoTo GotPic > > > > NoPic: > > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\No Pic.jpg").Select > > > > GotPic: > > > > 'scale the picture to the width of the column > > > > myScale = 42 / Selection.ShapeRange.Height > > > > Selection.Name = "KnownPictureName" > > > > Selection.ShapeRange.ScaleWidth myScale, msoFalse, > > > > msoScaleFromTopLeft > > > > Selection.ShapeRange.ScaleHeight myScale, msoFalse, > > > > msoScaleFromTopLeft > > > > Range("L22").Select > > > > > Application.EnableEvents = True > > > > End Sub > > > > ------------------------------------------------ Hide quoted text - > > > > - Show quoted text -- Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text -
From: Peter T on 13 Apr 2010 05:15 I am finding all this very difficult to understand. Do you mean - a) the file-names themselves do not have an extension, or b) do you mean the file-names do have an extension but you removed the extensions from the list that papers in cells. I think I already assume the shapes do not have extensions. Are all the files in the same folder or different folders, where is/are the path(s) stored. Regards, Peter T "marc747" <marc747(a)excite.com> wrote in message news:36ad8b3b-0e83-47e5-ba2f-28a4e192fd47(a)b33g2000yqc.googlegroups.com... Hi, Yes, my file names are without file extensions. I have the file names in a validation list in Cell "L21" and everytime that I pick a file name from that list the picture in cell "L9" changes and if no picture is availbale for that file name a default picture is inserted "C: \mypictures\Picture_not_Available.jpg". Thanks. On Apr 11, 4:56 am, "Peter T" <peter_t(a)discussions> wrote: > I don't follow what you are asking in addition to what I already > suggested. > Try and explain the logic of how you want to get from a file name to the > name of a picture, eg, did you already name the picture as that of the > filename without the extension. > > Better still, describe why you cannot use the "first" code and you cannot > make the "last" one work. Not sure what you mean by first and last, there > was only one set of code. > > Regards, > Peter T > > "marc747" <marc...(a)excite.com> wrote in message > > news:1fab5e4f-76e1-43a6-8a59-9373ea12123a(a)e7g2000yqf.googlegroups.com... > Hi, > > The first Code is working but not the way that I can use. The last one > I can't make it work. > > Is it possible the keep my original codes most of it as is and only > add a few lines of code so that instead of the macro looking for the > picture by name and extension to only look for the name of the picture > file by name only no matter what the extension is. I appreciate your > help.... > > Thanks > > On Apr 8, 5:08 am, "Peter T" <peter_t(a)discussions> wrote: > > > > > Well the sample code I suggested does work, though it may not with your > > implementation of it. > > > From what I understand, I'd suggest you maintain a Lookup table > > somewhere, > > perhaps on a hidden sheet or columns. > > > UserDisplayName : FileName > > Green Apples : grnApples.jpg > > Pink Roses : pnkRoses.jpg > > unknown : Picture_not_Available.jpg > > > ' warning air-code ! > > s = Range("L9" ).text > > > arr = range("fileList").value ' where fileList refers to the lookup > > table > > > For i = 1 to ubound(arr) > > if s = arr(i,1) then > > sFile = arr(i,2) > > ExitFor > > end If > > Next > > > ifLen(sFile) = 0 then > > sFile = Picture_not_Available.jpg > > end if > > pos = InStrRev(sFile, ".") > > sPicName = Left$(sFile, pos - 1) > > > on error resume next > > Set shr = activesheet.Pictures(sPicName).ShapeRange > > on error goto 0 > > > if shr is nothing then > > sFile = thisworkbook.path & "\" & sFile > > Set shr = activesheet.Pictures.Insert(sFile).ShapeRange > > End if > > > shr.name = sPicName > > > etc ' change properties of shr for location, size etc > > > Regards, > > Peter T > > > "marc747" <marc...(a)excite.com> wrote in message > > >news:61675848-09eb-4125-8c2e-caa02562fec4(a)q23g2000yqd.googlegroups.com... > > Hi, > > > The code did not work. > > Basically what I use the code for is that I have in Cell "L21" a > > Validation List with Picture names and every time I select a name from > > the list the picture in cell "L9" changes and insert the picture with > > the name on the list that I selected and incase there is no picture > > with the name that I selected it will insert a default picture called > > "Picture_not_Available.jpg" > > > The thing is that I don't want the name of the pictures on my list to > > show the picture file name with the file extension, therefore I wanted > > to make a change in the Macro that I have so that it will look for the > > picture by name only no mater what the file extension is. > > > Thanks, > > > Armond > > > ******************************************************* > > > On Apr 7, 2:13 am, "Peter T" <peter_t(a)discussions> wrote: > > > > Just for what you are doing you don't need the name at all (though you > > > might > > > want to give it a name and store it for some future process) > > > > Dim shr As ShapeRange > > > > 'code > > > > Set shr = activesheet.Pictures.Insert(filename).ShapeRange > > > > now replace all "Selection.ShapeRange" with "shr" > > > > Hmm, had anther glance at your code, not sure I quite follow what you > > > aiming > > > to do overall. If the above is not enough revert back and explain. > > > > Regards, > > > Peter T > > > > "marc747" <marc...(a)excite.com> wrote in message > > > >news:04edf68e-1568-49ee-8653-78677f424c8f(a)5g2000yqj.googlegroups.com... > > > > > Hi, > > > > > I have the following Macro but it seems that I need the name of the > > > > picture along with the extension in order to work, is there a way to > > > > include the extensions (.gif, .jpg, .jpeg, and more that I don't > > > > know ........) into the Macro so that I don't need to include the > > > > picture name with the extension. > > > > Thank You. > > > > > ----------------------------------- > > > > Private Sub Worksheet_Change(ByVal Target As Range) > > > > Dim myScale As Double > > > > If Target.Address <> "$L$21" Then Exit Sub > > > > > 'Select the cell where the picture is placed > > > > Application.EnableEvents = False > > > > On Error Resume Next > > > > ActiveSheet.Shapes("KnownPictureName").Delete > > > > On Error GoTo 0 > > > > > Range("L10").Select > > > > 'Insert the picture > > > > On Error GoTo NoPic > > > > > 'this is the one with the link to the file that I need the extension > > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\" & > > > > Range("L21").Value).Select > > > > > GoTo GotPic > > > > NoPic: > > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\No Pic.jpg").Select > > > > GotPic: > > > > 'scale the picture to the width of the column > > > > myScale = 42 / Selection.ShapeRange.Height > > > > Selection.Name = "KnownPictureName" > > > > Selection.ShapeRange.ScaleWidth myScale, msoFalse, > > > > msoScaleFromTopLeft > > > > Selection.ShapeRange.ScaleHeight myScale, msoFalse, > > > > msoScaleFromTopLeft > > > > Range("L22").Select > > > > > Application.EnableEvents = True > > > > End Sub > > > > ------------------------------------------------ Hide quoted text - > > > > - Show quoted text -- Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text -
From: marc747 on 13 Apr 2010 12:18 Hi, Sorry for that, I just have hard time explaining.... The actual picture files have extensions most of which are ".jpg" some ".gif" All pictures are in the same folder "C:\mypictures\ Default picture "C:\mypictures\Picture_not_Available.jpg" The file names that I entered in Excel are without extensions, example: actual picture name "138-58798.jpg" name entered in excel "138-58798" Thanks. ----------------------------------------------------------- On Apr 13, 2:15 am, "Peter T" <peter_t(a)discussions> wrote: > I am finding all this very difficult to understand. Do you mean - > > a) the file-names themselves do not have an extension, or > b) do you mean the file-names do have an extension but you removed the > extensions from the list that papers in cells. > > I think I already assume the shapes do not have extensions. > > Are all the files in the same folder or different folders, where is/are the > path(s) stored. > > Regards, > Peter T > > "marc747" <marc...(a)excite.com> wrote in message > > news:36ad8b3b-0e83-47e5-ba2f-28a4e192fd47(a)b33g2000yqc.googlegroups.com... > Hi, > Yes, my file names are without file extensions. I have the file names > in a validation list in Cell "L21" and everytime that I pick a file > name from that list the picture in cell "L9" changes and if no picture > is availbale for that file name a default picture is inserted "C: > \mypictures\Picture_not_Available.jpg". > > Thanks. > > On Apr 11, 4:56 am, "Peter T" <peter_t(a)discussions> wrote: > > > > > I don't follow what you are asking in addition to what I already > > suggested. > > Try and explain the logic of how you want to get from a file name to the > > name of a picture, eg, did you already name the picture as that of the > > filename without the extension. > > > Better still, describe why you cannot use the "first" code and you cannot > > make the "last" one work. Not sure what you mean by first and last, there > > was only one set of code. > > > Regards, > > Peter T > > > "marc747" <marc...(a)excite.com> wrote in message > > >news:1fab5e4f-76e1-43a6-8a59-9373ea12123a(a)e7g2000yqf.googlegroups.com... > > Hi, > > > The first Code is working but not the way that I can use. The last one > > I can't make it work. > > > Is it possible the keep my original codes most of it as is and only > > add a few lines of code so that instead of the macro looking for the > > picture by name and extension to only look for the name of the picture > > file by name only no matter what the extension is. I appreciate your > > help.... > > > Thanks > > > On Apr 8, 5:08 am, "Peter T" <peter_t(a)discussions> wrote: > > > > Well the sample code I suggested does work, though it may not with your > > > implementation of it. > > > > From what I understand, I'd suggest you maintain a Lookup table > > > somewhere, > > > perhaps on a hidden sheet or columns. > > > > UserDisplayName : FileName > > > Green Apples : grnApples.jpg > > > Pink Roses : pnkRoses.jpg > > > unknown : Picture_not_Available.jpg > > > > ' warning air-code ! > > > s = Range("L9" ).text > > > > arr = range("fileList").value ' where fileList refers to the lookup > > > table > > > > For i = 1 to ubound(arr) > > > if s = arr(i,1) then > > > sFile = arr(i,2) > > > ExitFor > > > end If > > > Next > > > > ifLen(sFile) = 0 then > > > sFile = Picture_not_Available.jpg > > > end if > > > pos = InStrRev(sFile, ".") > > > sPicName = Left$(sFile, pos - 1) > > > > on error resume next > > > Set shr = activesheet.Pictures(sPicName).ShapeRange > > > on error goto 0 > > > > if shr is nothing then > > > sFile = thisworkbook.path & "\" & sFile > > > Set shr = activesheet.Pictures.Insert(sFile).ShapeRange > > > End if > > > > shr.name = sPicName > > > > etc ' change properties of shr for location, size etc > > > > Regards, > > > Peter T > > > > "marc747" <marc...(a)excite.com> wrote in message > > > >news:61675848-09eb-4125-8c2e-caa02562fec4(a)q23g2000yqd.googlegroups.com.... > > > Hi, > > > > The code did not work. > > > Basically what I use the code for is that I have in Cell "L21" a > > > Validation List with Picture names and every time I select a name from > > > the list the picture in cell "L9" changes and insert the picture with > > > the name on the list that I selected and incase there is no picture > > > with the name that I selected it will insert a default picture called > > > "Picture_not_Available.jpg" > > > > The thing is that I don't want the name of the pictures on my list to > > > show the picture file name with the file extension, therefore I wanted > > > to make a change in the Macro that I have so that it will look for the > > > picture by name only no mater what the file extension is. > > > > Thanks, > > > > Armond > > > > ******************************************************* > > > > On Apr 7, 2:13 am, "Peter T" <peter_t(a)discussions> wrote: > > > > > Just for what you are doing you don't need the name at all (though you > > > > might > > > > want to give it a name and store it for some future process) > > > > > Dim shr As ShapeRange > > > > > 'code > > > > > Set shr = activesheet.Pictures.Insert(filename).ShapeRange > > > > > now replace all "Selection.ShapeRange" with "shr" > > > > > Hmm, had anther glance at your code, not sure I quite follow what you > > > > aiming > > > > to do overall. If the above is not enough revert back and explain. > > > > > Regards, > > > > Peter T > > > > > "marc747" <marc...(a)excite.com> wrote in message > > > > >news:04edf68e-1568-49ee-8653-78677f424c8f(a)5g2000yqj.googlegroups.com.... > > > > > > Hi, > > > > > > I have the following Macro but it seems that I need the name of the > > > > > picture along with the extension in order to work, is there a way to > > > > > include the extensions (.gif, .jpg, .jpeg, and more that I don't > > > > > know ........) into the Macro so that I don't need to include the > > > > > picture name with the extension. > > > > > Thank You. > > > > > > ----------------------------------- > > > > > Private Sub Worksheet_Change(ByVal Target As Range) > > > > > Dim myScale As Double > > > > > If Target.Address <> "$L$21" Then Exit Sub > > > > > > 'Select the cell where the picture is placed > > > > > Application.EnableEvents = False > > > > > On Error Resume Next > > > > > ActiveSheet.Shapes("KnownPictureName").Delete > > > > > On Error GoTo 0 > > > > > > Range("L10").Select > > > > > 'Insert the picture > > > > > On Error GoTo NoPic > > > > > > 'this is the one with the link to the file that I need the extension > > > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\" & > > > > > Range("L21").Value).Select > > > > > > GoTo GotPic > > > > > NoPic: > > > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\No Pic.jpg").Select > > > > > GotPic: > > > > > 'scale the picture to the width of the column > > > > > myScale = 42 / Selection.ShapeRange.Height > > > > > Selection.Name = "KnownPictureName" > > > > > Selection.ShapeRange.ScaleWidth myScale, msoFalse, > > > > > msoScaleFromTopLeft > > > > > Selection.ShapeRange.ScaleHeight myScale, msoFalse, > > > > > msoScaleFromTopLeft > > > > > Range("L22").Select > > > > > > Application.EnableEvents = True > > > > > End Sub > > > > > ------------------------------------------------ Hide quoted text - > > > > > - Show quoted text -- Hide quoted text - > > > > - Show quoted text -- Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text -
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Select list box entries in multiple rows? Next: Macro to copy subtotaled data |