vba - indentifying buttons

[jbesant]

To talented VBAers:

Any help on the problem below would be greatly appreciated!

Concept for my excel sheet: A user to clicks any button on a sheet with (6X4) 24 buttons; depending on what button is clicked, certain information is sent to certain cells in another tab.

My problem is that I don't know how to identify which button is clicked except by assigning a macro to each button specifically. If I could identify the button that was clicked by looking at the text inside, or some other way, I'd be able to simplify my code and it would be much easier to manage and alter.

Thanks so much for any help on this!

Here's the messy code of my first attempt. Its functional, but really hard to adapt and work with:


Sub time1()
Dim time As Integer
time = 4
Call CopyOrders1(time)
End Sub
Sub time2()
Dim time As Integer
time = 5
Call CopyOrders1(time)
End Sub
Sub time3()
Dim time As Integer
time = 6
Call CopyOrders1(time)
End Sub
Sub time4()
Dim time As Integer
time = 7
Call CopyOrders1(time)
End Sub
Sub time5()
Dim time As Integer
time = 8
Call CopyOrders1(time)
End Sub
Sub time6()
Dim time As Integer
time = 9
Call CopyOrders1(time)
End Sub




Sub time21()
Dim time As Integer
time = 4
Call CopyOrders2(time)
End Sub
Sub time22()
Dim time As Integer
time = 5
Call CopyOrders2(time)
End Sub
Sub time23()
Dim time As Integer
time = 6
Call CopyOrders2(time)
End Sub
Sub time24()
Dim time As Integer
time = 7
Call CopyOrders2(time)
End Sub
Sub time25()
Dim time As Integer
time = 8
Call CopyOrders2(time)
End Sub
Sub time26()
Dim time As Integer
time = 9
Call CopyOrders2(time)
End Sub







Sub time31()
Dim time As Integer
time = 4
Call CopyOrders3(time)
End Sub

Sub time32()
Dim time As Integer
time = 5
Call CopyOrders3(time)
End Sub
Sub time33()
Dim time As Integer
time = 6
Call CopyOrders3(time)
End Sub
Sub time34()
Dim time As Integer
time = 7
Call CopyOrders3(time)
End Sub
Sub time35()
Dim time As Integer
time = 8
Call CopyOrders3(time)
End Sub
Sub time36()
Dim time As Integer
time = 9
Call CopyOrders3(time)
End Sub










Sub time41()
Dim time As Integer
time = 4
Call CopyOrders4(time)
End Sub
Sub time42()
Dim time As Integer
time = 5
Call CopyOrders4(time)
End Sub
Sub time43()
Dim time As Integer
time = 6
Call CopyOrders4(time)
End Sub
Sub time44()
Dim time As Integer
time = 7
Call CopyOrders4(time)
End Sub
Sub time45()
Dim time As Integer
time = 8
Call CopyOrders4(time)
End Sub
Sub time46()
Dim time As Integer
time = 9
Call CopyOrders4(time)
End Sub






Sub CopyOrders1(time)
Dim name As Integer
Dim orderName As String
orderName = Range("C2")
name = Range("A1")
Cells(30, 2) = Cells(30, 2) & " * " & orderName
Sheets("Orders").Cells(time, name) = Sheets("Orders").Cells(time, name) & " * " & orderName
End Sub

Sub CopyOrders2(time)
Dim name As Integer
Dim orderName As String
orderName = Range("E2")
name = Range("A1")
Cells(30, 2) = Cells(30, 2) & " * " & orderName
Sheets("Orders").Cells(time, name) = Sheets("Orders").Cells(time, name) & " * " & orderName
End Sub

Sub CopyOrders3(time)
Dim name As Integer
Dim orderName As String
orderName = Range("G2")
name = Range("A1")
Cells(30, 2) = Cells(30, 2) & " * " & orderName
Sheets("Orders").Cells(time, name) = Sheets("Orders").Cells(time, name) & " * " & orderName
End Sub

Sub CopyOrders4(time)
Dim name As Integer
Dim orderName As String
orderName = Range("I2")
name = Range("A1")
Cells(30, 2) = Cells(30, 2) & " * " & orderName
Sheets("Orders").Cells(time, name) = Sheets("Orders").Cells(time, name) & " * " & orderName
End Sub

 

[Evagrius Ponticus]

Hi - I am not sure I understand the code but maybe,

if application.caller = "button name" then . . . .

 

[rpaulson]

this will return button name

c = ActiveSheet.Shapes(Application.Caller).Name

 

[jbesant]

Hey guys -

Thanks so much for you help...almost there.

For some reason, ActiveSheet.Shapes(Application.Caller).name returns names like "button 4", rather than the text I've typed into the button. Is there a way to either change the underlying name of the button, or to refer directly to the text isnide the button?

Thanks!