Change cell contents based on shape being clicked with Macro assigned

gregonometry

New Member
Joined
May 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,600
Office Version
  1. 2010
Platform
  1. Windows
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 :​
VBA Code:
Sub Demo1()
    V = Application.Caller
    If Not IsError(V) Then Sheet2.[A1].Value2 = Shapes(V).AlternativeText
End Sub
 

gregonometry

New Member
Joined
May 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

gregonometry

New Member
Joined
May 25, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 :​
VBA Code:
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!
 

Forum statistics

Threads
1,144,340
Messages
5,723,804
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top