Link shape to drop down list

[toci1980]

Hello guys,
I am trying to figure out how to make this but without success at this moment
So, I would like to link "shape 1" in Sheet 1 and when click on it to go into Sheet 2 and select "1" in drop down menu.
Is it possible to do this and how?

Thank you in advance.

I'm not able to put example here so I'm putting picture.

 

[toci1980]

I have some trouble with posting pictures....

 

[toci1980]

anything new?

 

[Domenic]

First, right-click the sheet tab for Sheet1, select View Code, and copy/paste the following into the code module for Sheet1...

Private Sub UpdateDropDown()
    Dim rTarget As Range
    Set rTarget = Worksheets("Sheet2").Range("B1")
    Select Case LCase(Application.Caller)
        Case "shape 1"
            rTarget.Value = 1
        Case "shape 2"
            rTarget.Value = 2
        Case "shape 3"
            rTarget.Value = 3
        Case "shape 4"
            rTarget.Value = 4
    End Select
End Sub

Then, assign the above macro to each shape...

1) Right-click the shape.

2) Select Assign Macro.

3) Enter the macro name as follows:

Sheet1.UpdateDropDown

Note that Sheet1 should be the code name for the sheet, not the name of the sheet, which may or may not be the same. You can find the code name under the Properties window in the Visual Basic Editor.

Hope this helps!

 

[toci1980]

Hi Domenic.
Thanks for help but I am a little confused with the step 3, Enter the macro name as follows: Sheet1.UpdateDropDown
VBA doesn't allow me to put this name for macro. Also I didn't understand you well all steps. I have copied the macro into the Sheet 1, then click on "shape 1" ---> Assign macro and put the same code, bur as I said the problem with the code name. Have you tried this?

 

[Domenic]

Yes, I tested the solution before posting it. Okay, let's take it one step at a time.

The code that I gave you is copied only once into the code module for your sheet. Since your shapes are located in Sheet1, we'll copy/paste the code into the code module for Sheet1. So you can right-click the sheet tab for Sheet1, select View Code, and the code module for the sheet will be displayed. Copy/paste the code into this module.

Now we can assign this macro to each shape in Sheet1. So, to assign it to your first shape, right-click the shape, and select Assign Macro. A dialog box will pop-up. For the macro name, enter...

Sheet1.UpdateDropDown

...and then click OK. One thing to keep in mind, though, the reference to Sheet1 here is actually the code name for the sheet. If you haven't changed the code name for the sheet, then it should be the same as your sheet name. If you're not sure, you can go into the Visual Basic Editor, by pressing Alt+F11, select Project Explorer, by pressing Ctrl+R, select/click the sheet that contains your shapes for your workbook, select Properties Window, by pressing F4, and look at Name under properties. If the code name was never changed, it should be the same as the sheet name on the tab itself. If it's different, then you'll need to use whatever name you see there instead of Sheet1 when specifying the macro name in assigning the macro.

Now you can do the same for each of your other shapes.

Does this help?

 

[Domenic]

Also, if the name of your shapes (the name that appears in the Name box to the left of the formula bar) differ than "shape 1", "shape 2", etc, you'll need to change the names in the code accordingly. If so, make sure that when you specify the names you do so in lower case. That's because Select Case is case-sensitive.

 

[toci1980]

It seems that I have some problem with this "Private" in front of "Sub". If I remove "Private" then I can see macro and I can assign it to shapes, but after that nothing happens when I press the shape. If I back "Private" in front of "Sub" I don't see macro....
As you can see from the images bellow I have put macro in right place.
is there any way to find you file with macro to see what's going on with my?
Thanks a lot!

 

[Domenic]

Actually, I can see from your image that you've placed the code in the wrong module. You've placed it in a regular module, instead of the sheet module. If you right-click Sheet1 under Microsoft Excel Objects in the Project Explorer window, and select View Code, the code module for your sheet will pop up. Or, alternatively, you can right-click the sheet tab, and select View Code, which I've already mentioned.

Also, the declaration for the sub should be...

Private Sub UpdateDropDown()

And, I see from your image that the code name for your sheet is indeed Sheet1. So, when assigning the macro to your shapes, the macro name should be as I've already mentioned...

Sheet1.UpdateDropDown

Does this help?

 

[toci1980]

I have corrected and record macro into Sheet1, but when I want to assign the macro for the shape I don't see any macro. Also, I have tried to put the name you have given to me "Sheet1.UpdateDropDown" but still nothing happens when I click on shape.