Change cell contents based on shape being clicked with Macro assigned

[gregonometry]

Hello All,

long time user, first time poster!

I would like to have several shapes(buttons) assigned with the same macro (this macro activates a userform). When i click a given shape(button) with a macro assigned i would like to assign a number to a worksheet cell based on which shape(button) i click.

For example.

Shape(button) names:

btn1
btn2

When i click the shape(button) btn1 i would like the action to run the assigned macro and simultaneously assign a 1 to Sheet2.Range("A1"). Similarly, when i click shape(button) btn2 i would like the action to run the assigned macro and simultaneously assign a 2 to Sheet2.Range("A1"); overwriting the previous value.

Any help would be much appreciated!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub greg()
   With ActiveSheet.Shapes(Application.Caller)
      If .Name = "btn1" Then
         Sheet2.Range("A1").Value = 1
         Call macro1
      ElseIf .Name = "btn2" Then
         Sheet2.Range("A1").Value = 2
         Call macro2
      End If
   End With
End Sub

 

[Marc L]

Hi,​

​

in each button properties enter the value in its Alternative Text field​

then paste this VBA demonstration in the worksheet module where are located the buttons :​

Sub Demo1()
    V = Application.Caller
    If Not IsError(V) Then Sheet2.[A1].Value2 = Shapes(V).AlternativeText
End Sub

 

[gregonometry]

Hi & welcome to MrExcel.
How about

Sub greg()
   With ActiveSheet.Shapes(Application.Caller)
      If .Name = "btn1" Then
         Sheet2.Range("A1").Value = 1
         Call macro1
      ElseIf .Name = "btn2" Then
         Sheet2.Range("A1").Value = 2
         Call macro2
      End If
   End With
End Sub

Works like an absolute charm!

Thank you kindly

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[gregonometry]

Hi,​

​

in each button properties enter the value in its Alternative Text field​

then paste this VBA demonstration in the worksheet module where are located the buttons :​

Sub Demo1()
    V = Application.Caller
    If Not IsError(V) Then Sheet2.[A1].Value2 = Shapes(V).AlternativeText
End Sub

oh this looks interesting. I will also try it this way to try to learn more.

Thank you very much for your response!