Hi Brian.
I know some general VBA. There is so much stuff in Excel VBA that none of us know it all, and as there are close to infinite ways to do anything in Excel VBA, its always a bad idea trying to do anything wIth anyone else’s coding, so it’s usually a case of trying to figure it out from following through the coding and seeing what’s going on, or not…
So I had a look. I will give you the shortened story, - so much more than that I don’t know myself as I ‘aint done much myself with pictures and images in Excel, so a lot of that stuff is a bit foreign to me…
OK, so it looks like in worksheet COR-Cover we are talking about changing the selection in cell B42.
So something happens / something kicks in, or should, when we change a cell value in that worksheet. Whether we do that manually or by selecting from a drop down list is usually less important as far as VBA coding is concerned.
Now, Right click on that worksheet tab, and select View Code ,
https://i.postimg.cc/XND0zph6/Right-...-View-Code.jpg
, and Bingo, there’s the coding, - as its name suggests, ( Sub Worksheet_Change ) , it kicks in as a result of a change in that worksheet.
( If you are not aware, that large outer window is what is called something like the VB Editor, or code development environment or some such. The biggest window with that code in it can be thought of as either the code window for that worksheet, or in terms of the object orientated programming language nonsense you might consider that and the worksheet spreadsheet as the same basic object – the object of worksheet object which we can see has a normal tab name of COR-Cover and a code name , or code module name of Sheet10 )
Take a quick look at that Sub Worksheet_Change , and we see its set to do something If the address of the changed cell is B42
The second main bit seems to be looking for something in a range "Pictable" , and as far as I can tell that’s something is to do with that range at top left in worksheet Signatures
https://i.postimg.cc/504MZvbb/Range-Pictable.jpg
If it does not find the current range B42 value in that , then top left "Pictable" range then its going to tell you "Name does not match a signature name"
So with the data currently in the workbook, you will get that message if you select ( or type in manually ) in B42 CEO because CEO is not in that top left range.
From this point on I have already exhausted my knowledge of VBA, Lol, so it’s a matter of trying to figure out as best I can by a experimenting…
If I choose / use a name that does exist on the range "Pictable" , then I am going to be going on to that other code bit:
___Else
______Sheet3.Pic……… ….
Without really understanding anything about images and pictures in Excel, it looks like somehow a code bit like for example, _ .Pictures("Picture 7") _ , would somehow be getting hold of the corresponding signature image, in this example the signature for Marty Sonnichsen should be coming up, bit it looks like it maybe getting the wrong one… https://i.postimg.cc/LsBpCvdx/Works-for-Marty.jpg
.
Hmm…..
I haven’t a clue from now on. Never mind. Lucky for me Excel and VBA is not usually to difficult to figure out…
I wrote this macro and ran it in some worksheet code modules.
Code:
Sub WotsThisPicStufInThisWorksheet()
Dim Pik As Picture
Debug.Print Me.CodeName & " " & Me.Name ' write out the two worksheet names of the worksheet object in which this macro currently is
For Each Pik In Me.Pictures
Debug.Print Pik.Name & " " & Pik.Index & " " & Pik.TopLeftCell.Address ' write some properties of each picture
Next Pik
Debug.Print ' this will just give me a blank line in the Immediate window to conveniently separate the outputs of each macro run if I run the macro again
End Sub
' Now Hit Ctrl+g from the VB Editor in order to see the written outputs
What I am doing there is looking at some properties of the Pictures in a worksheet. I had no idea what properties they have , or even if they have any. I just looked at what Intellisense had to offer me as I wrote the code, https://i.postimg.cc/mrG4yJnc/Picture-properties.jpg
After running the macro , or before, if you Hit Ctrl+g, ( whilst in the VB coding environment), you will get a useful window thing called the Immediate Window
https://i.postimg.cc/jSQXx0mh/Immedi...ebug-Print.jpg ( In my German Excel its called DirektBereich ). One thing you can do is write stuff in that window with coding, which is what that Debug.Print thing does
If I run that macro in the Signatures ( Code name Sheet3 ) worksheet code module, and then compare all in the signature worksheet spreadsheet …
https://i.postimg.cc/CxV7pLP1/Messed...everything.jpg
, … then I come immediately to one conclusion…. It looks like Benjamin is OK , but most everything else is in a bit of a pickle. Here are those immediate window results again from that last screenshot:
Sheet3 Signatures
Picture 2 1 $H$1
Picture 3 2 $G$4
Picture 6 3 $G$12
Picture 4 4 $H$7
Picture 7 5 $H$15
Picture 9 6 $G$19
Picture 2 1 $H$1
So it looks like some sort of updating is needed. That may have been what sandy was on about.
I don’t know how to do that.
Never mind.
I will move on to some more experimenting in the next post.
Alan
Reply With Quote
Originally Posted by DocAElstein
Bookmarks