Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I programatically change photos in one worksheet
I have a worksheet that displays the performance for a single retail
outlet. I change the location name and via lookups I change the data in the worksheet to reflect the sale performance for the new location. I would like to have a unique photo of each retail location when I select a different location. Is there any way to programmatically have a new photo automatically display when a new location is selected? Steve Sammons |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I programatically change photos in one worksheet
Steve
Programmatically............see JE McGimpsey's site for a method of showing hidden pictures based upon a value in a cell. http://www.mcgimpsey.com/excel/lookuppics.html Gord Dibben MS Excel MVP On 30 Nov 2006 08:49:52 -0800, "Steve" wrote: I have a worksheet that displays the performance for a single retail outlet. I change the location name and via lookups I change the data in the worksheet to reflect the sale performance for the new location. I would like to have a unique photo of each retail location when I select a different location. Is there any way to programmatically have a new photo automatically display when a new location is selected? Steve Sammons |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I programatically change photos in one worksheet
Gord:
This is wonderful! However, when I paste the code into my worksheet and begin putting it together I get a runtime error "Unable to set the Visual property of the Picture class". The debugger highlights the line that contains "Me.Pictures.Visible = False" Any thoughts? Steve Gord Dibben wrote: Steve Programmatically............see JE McGimpsey's site for a method of showing hidden pictures based upon a value in a cell. http://www.mcgimpsey.com/excel/lookuppics.html Gord Dibben MS Excel MVP On 30 Nov 2006 08:49:52 -0800, "Steve" wrote: I have a worksheet that displays the performance for a single retail outlet. I change the location name and via lookups I change the data in the worksheet to reflect the sale performance for the new location. I would like to have a unique photo of each retail location when I select a different location. Is there any way to programmatically have a new photo automatically display when a new location is selected? Steve Sammons |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I programatically change photos in one worksheet
Steve
Do you have pictures on the worksheet? Have you got the lookup table on sheet2? If you didn't download JE's example file you should do so to see how he has it set up. His workbook does as advertised. Gord On 30 Nov 2006 13:54:49 -0800, "Steve" wrote: Gord: This is wonderful! However, when I paste the code into my worksheet and begin putting it together I get a runtime error "Unable to set the Visual property of the Picture class". The debugger highlights the line that contains "Me.Pictures.Visible = False" Any thoughts? Steve Gord Dibben wrote: Steve Programmatically............see JE McGimpsey's site for a method of showing hidden pictures based upon a value in a cell. http://www.mcgimpsey.com/excel/lookuppics.html Gord Dibben MS Excel MVP On 30 Nov 2006 08:49:52 -0800, "Steve" wrote: I have a worksheet that displays the performance for a single retail outlet. I change the location name and via lookups I change the data in the worksheet to reflect the sale performance for the new location. I would like to have a unique photo of each retail location when I select a different location. Is there any way to programmatically have a new photo automatically display when a new location is selected? Steve Sammons |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I programatically change photos in one worksheet
I do have the lookup table on a sheet 2 and I do have the images on the
sheet that has the formula. If I asterisk out the "Me.Pictures.Visible = False" it works. The only problem is that all the images are visible and the order of the photos doesn't change. Everything works...I just can't make all the images disappear. On this same sheet are several other Graphs but I inserted a bunch of graphs into the sample file and that didn't mess things up. I have removed all the photos and put new ones in to see if there was an issue with the property of the images. None of these tactics has led to success. Any thoughts? Steve |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I programatically change photos in one worksheet
And I have downloaded JE file and it works fine. I also moved the two
sheets from his file over to the file that I am working on to see if it would work there and it does. But my master sheet doesn't like the hide pictures line at all. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I programatically change photos in one worksheet
This now is very weird. I copied the two sheets that are in my master
file off to a new document. My main document is 21 meg and this new document has no data in it; just the format and graphs that don't link anywhere. And now the routine works just fine. So there could be some relationship to the size of the file. I am going to try it the other way where I eliminate all the sheets and see if it begins to work. Have you ever heard of anything like this? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I programatically change photos in one worksheet
Steve
Beats me<g JE may drop by and give some insight......hint, hint, nudge, nudge. Gord On 1 Dec 2006 07:19:55 -0800, "Steve" wrote: This now is very weird. I copied the two sheets that are in my master file off to a new document. My main document is 21 meg and this new document has no data in it; just the format and graphs that don't link anywhere. And now the routine works just fine. So there could be some relationship to the size of the file. I am going to try it the other way where I eliminate all the sheets and see if it begins to work. Have you ever heard of anything like this? Gord Dibben MS Excel MVP |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I programatically change photos in one worksheet
I will now close this because I figured out a work around.
Because the sheet failed when the code "me.pictures.visible = false" was used here's the work around. I turned all the photos off that didn't match and then turned on the one that did. I also added an additional line to turn the company's logo on. It works...not perfect but it works. I have no clue why "me.pictures.visible = false" makes it bomb but I thank you for the help!!!! Here's the Code: Option Explicit Private Sub Worksheet_Calculate() Dim oPic As Picture Application.ScreenUpdating = False 'Me.Pictures.Visible = False With Range("N7") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic For Each oPic In Me.Pictures If oPic.Name < .Text Then oPic.Visible = False oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With With Range("A2") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I programatically change photos in one worksheet
Gord Dibben wrote:
Steve Beats me<g JE may drop by and give some insight......hint, hint, nudge, nudge. Gord On 1 Dec 2006 07:19:55 -0800, "Steve" wrote: This now is very weird. I copied the two sheets that are in my master file off to a new document. My main document is 21 meg and this new document has no data in it; just the format and graphs that don't link anywhere. And now the routine works just fine. So there could be some relationship to the size of the file. I am going to try it the other way where I eliminate all the sheets and see if it begins to work. Have you ever heard of anything like this? Gord Dibben MS Excel MVP Hi Steve and Gord, I've encountered a few other instances where projects using this code have failed because of the same problem. In each case, they have added the code and it works well, then they have added more pictures to the sheet only to discover that a point is reached when the code falls down at... Me.Pictures.Visible = False I'm guessing Steve hit the problem straight away because his worksheet already had enough pictures to cause the problem when the code was added. I've been lucky enough to find a solution but I'm not smart enough to figure out why the problem occurs in the first place. My solution to the problem has been to avoid dimensioning the pictures as Picture objects. I have dimensioned them as Shape objects and then separated the pictures from all of the other shapes using their Type property, which is msoPicture or 13. After making the above change, as far as I can tell, it is then no longer possible to make all the pictures invisible in one fell swoop the way that "Me.Pictures.Visible = False" does, so a loop must be used in its place. The following code should work for Steve after he either edits the code so that it contains the name that Excel uses for his Logo or he changes his logo's name (in the Name box on the left side of the Formula bar) to "Logo"... Private Sub Worksheet_Calculate() Dim oPic As Shape Application.ScreenUpdating = False For Each oPic In Me.Shapes If oPic.Type = 13 And oPic.Name < "Logo" Then oPic.Visible = False End If Next oPic With Range("N7") For Each oPic In Me.Shapes If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub Note that on his site JE adds the following warning... "Note that for large numbers of pictures (like, say, a parts catalog), a database solution would likely be much easier and much more efficient." Ken Johnson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I programatically change photos in one worksheet
Here's possibly a better solution, since it uses one loop instead of
two... Private Sub Worksheet_Calculate() Dim oPic As Shape Application.ScreenUpdating = False For Each oPic In Me.Shapes Select Case oPic.Type Case 13 Select Case oPic.Name Case "Logo" 'Excludes logo from changes. 'NB either edit above line 'to match the name xl uses 'for your company logo 'OR change its xl name 'to "Logo" w/o quotes Case Range("N7").Text oPic.Visible = True With Range("N7") oPic.Top = .Top oPic.Left = .Left End With Case Else oPic.Visible = False End Select End Select Next oPic End Sub Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to use Replace function to change worksheet names | Excel Discussion (Misc queries) | |||
Help Please, Worksheet Change | Excel Worksheet Functions | |||
How to change number to text in new column from another worksheet? | Excel Worksheet Functions | |||
Comparing a list to a Calendar worksheet. | Excel Worksheet Functions | |||
how to change the default font on the worksheet tabs? | Excel Discussion (Misc queries) |