Dropdown menu that inserts signature Picture from selected name: Not always working or gets wrong picture

**txjackknife** · 02-03-2023, 03:00 AM

I have a spreadsheet that has 2 tabs with a name and signature dropdown list. When you use drop down menu and select a name, the persons signature will be inserted above the name.

This spreadsheet was done about 12 years ago and I have added new names and signatures but can't get them to work properly. I am not good at doing codes or figuring them out.

Any help you can provide would be so much appreciated!

Thank you in advance

File link below
************************************************
Admin WARNING: You need to download additional software to download this file
https://terabox.com/s/13XeHBtp-p4xIkUH2MHMWbQ
************************************************

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg. 9f6hAjkC0ct9f8jleOui-u
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**sandy666** · 02-03-2023, 06:21 AM

file should be downloaded without logging or without forcing downloading of additional software

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**DocAElstein** · 02-03-2023, 05:20 PM

Hello txjackknife,
Welcome to ExcelFox
As sandy said, I prefer not to have to download extra software in order to download a file. I think also most people would prefer not to download extra software in order to download your file.
Its best if you can upload a file / attach it to a post. Here are some notes on doing that:
https://excelfox.com/forum/showthrea...ll=1#post11279
https://excelfox.com/forum/showthrea...ll=1#post15589
- You will probably need to start at
(ii) For a Reply or when Editing an existing post
and then go on to
2 To add a File to the current post:

_._____

If you must use a file sharing site, its better to use one that allows you directly to download a file
( I use app box ( free ) , but there are many others.
https://www.box.com/pricing/individual
https://i.postimg.cc/T3n616pq/app-box-free.jpg )

_.____

Note also that it’s easier for us to work with a small reduced size sample file that just has enough information for us to see the problem. (If there is any personal or otherwise sensitive data, then its probably best to replace that with imaginary made up data. But is you do change any real data to imaginary data, try to keep the format as similar to possible to the real data.

Alan

**txjackknife** · 02-03-2023, 07:30 PM

Originally Posted by DocAElstein

Hello txjackknife,
Welcome to ExcelFox..........
Alan

Thank You.
I tried to delete everything on the excel file except what is needed and it was still too large.
Thanks for the help!

Brian

https://app.box.com/s/6txll6kxefi4q6ld08gfepftdxre36k7

**sandy666** · 02-03-2023, 07:53 PM

**DocAElstein** · 02-03-2023, 08:19 PM

Originally Posted by txjackknife

Thank You. ....I tried to delete everything on the excel file except what is needed and it was still too large.....
https://app.box.com/s/6txll6kxefi4q6ld08gfepftdxre36k7

**txjackknife** · 02-03-2023, 10:22 PM

Originally Posted by DocAElstein

.....aken a quick look at the file, and when I open it I see this:
https://i.postimg.cc/4NFPtzWz/Signatures.jpg
Signatures.JPG
At first glance I am not quite sure what is going on , or what should happen. Remember you know about this file so any short description, as you gave in your first post, will sound perfectly OK to you for to explain what the problem and to explain what you want. For someone like me seeing the file for the first time it is not immediately obvious what is going on.-----------

So when you go to the COR-Cover sheet and click on the drop down menu and select a name, it is supposed to insert that persons signature from the signature sheet. It works on some names but not others and I am not savy enough to figure out what needs corrected. Several names were added to the list both on the COR-Cover sheet to the right I believe column AA as well as the signature sheet. The Pending COR sheet does not work as it should either. It should function the same as the COR-Cover sheet. I hope this makes sense.

Thank you

**DocAElstein** · 02-04-2023, 04:42 AM

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

**DocAElstein** · 02-04-2023, 04:42 AM

Continued from last post ………

OK, so I don’t know how to sort out that messed up order , (at least not in some easy automated conventional “proper way” , assuming there is one – I expect fiddling around manually would probably do it).
I will try a different approach: I will see if I can insert somehow a picture into worksheet signatures, and then get things to do like you want with it.

_1) Insert a picture into Excel. I had no idea. So I Googled. There are hundreds of Blogs and YouTube videos that explain it a lot better than I can. So I followed a few and was able to insert a small picture of a fox, that was sitting wagging its tail on my desktop, into worksheet signatures
https://i.postimg.cc/FRTQmRHc/Insert...signatures.jpg

_2) I re ran that macro I wrote, and you can see it is showing a new entry , which Excel has given the name Picture 8
https://i.postimg.cc/T3bGmftM/New-Entry-Picture-8.jpg
( So I assume that Picture 8 is some number name thing given by Excel automatically when you insert a picture into Excel, but I don’t know for sure. I never did anything like this before! - My guess is that if I put another picture in , then Excel will give it the name number Picture 9 )

_3) So I write in Fox and Picture 8
https://i.postimg.cc/xTc9fKHd/write-...-Picture-8.jpg
and
_4 ) I write in Fox
https://i.postimg.cc/mrQLxB9N/write-in-Fox.jpg

Don’t ask me why I did steps _3) and _4) . I don’t know really. It just seemed like a good idea at the time, Lol! It seems to make some sense even though I don’t really know what I am doing here..

_5) I notice that Fox is not in the drop down list yet… I don’t know about drop down lists, I never used them. But apparently it has something to do with data validation, what ever that is. I fiddled around and found this thing
https://i.postimg.cc/Kc9Zh91V/Drop-d...ods-wollop.jpg
(note: I had cell B42 selected while i was fiddling about )

_6) I changes 47 to 48 because that Fox is in cell AA48. ( I wrote it there in step _4) )
https://i.postimg.cc/s2Fj0vMg/Changed-47-to-48.jpg

_7) Fox is now in the drop down list. https://i.postimg.cc/K83cNnvW/Fox-appears.jpg
Select it….
Bingo!
https://i.postimg.cc/vHNHfPM9/Bingo-Full-Fox.jpg
_._______________________________

So I think that has shown a way to get what you want, at least one way.

I expect a macro could be written to go through a set of pictures in a folder somewhere and bring them in and do all that stuff for all of them. I probably could not do it very quickly as I am new to a lot of the stuff here, but someone with more experience of playing around with pictures in excel could probably do it a bit easier.

See how you get on with all that,

Alan

Edit
Here just some temporary conclusions and remarks. They are based on my initial experiments so bear that in mind. There could be more to it, depending on what the full workbook is all about

_a) It looks like the column AA in worksheet COR-Cover is used to fill the drop down list. Without knowing too much aboput drop down lists and data validation stuff, I think you can figure out from these screenshots that I have it currently set to the range AA38 – AA48
https://i.postimg.cc/9fbtL7vy/Column...-COR-Cover.jpg
https://i.postimg.cc/fLb7pbNp/Column...-COR-Cover.jpg
https://i.postimg.cc/MTr0S8ZT/Column...-COR-Cover.jpg

_b) The two column list top left in worksheet Signatures is used to match up two things:
_column B, the
Picture x ( where x is 1 2 3 4 …. Etc … )
That being the name given by Excel to an inserted picture,
_column A: the name you choose to give it, the picture that Excel calls Picture x . I chose Fox, for example, for the picture I inserted, which Excel decided to call Picture 8 .
You obviously want to give the name of the name in the signature.

The crux of your problem seems to be that things have possibly been added a bit randomly , possibly also some things taken out, and that has resulted in a muddle with the lists

What I have basically done in this and the previous post is to show one way of how to figure out what name given by Excel relates to which signature picture already in the worksheet. I did it for the first time and wrote the post as I went along. So it may not be totally clear. See how you get on, and if you need more clarity, ask, and I will take another look, ( may not be for a day or two).

**txjackknife** · 02-07-2023, 02:07 AM

Thank you so much for your help with this. With what you figured out and the screenshots you sent, I was able to figure out what to do to make this all work!

Much appreciated!
Brian