Hey!
I have a basic self taught understanding of VBA which works for most of the things I try and do, but I need some help with buttons!
I have a sub that successfully creates a button in consecutive cells and names each button after consecutive worksheets. It looks like it works fine and I can assign the macro I want to use ok.
What I really need tho, is a way of copying out the name of the button, as that is what I would need to run the other macros (to select the worksheet I need, run the calculations for the product number etc).
I'm sure its not the most pretty or efficient code (but it works and more importantly I understand what it's doing!) This creates the buttons...
Sub BUTTON_ADD()
Dim btn As Button
Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete
Dim t As Range
For i = 21 To 22
Set t = ActiveSheet.Range(Cells((i - 18), 6), Cells((i - 18), 6))
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.OnAction = "BUTTON_CLICK"
.Caption = Range("A1").Offset(i - 19, 3).Value
'.Name = "TEST"
End With
Next i
Application.ScreenUpdating = True
End Sub
But I need Sub BUTTON_CLICK to copy the caption (I don't seem to need a name for the button?) so that I can run other macros... eg Sheet 21 and the first button are named 185632, so I need to be able to paste that number in a cell somewhere!
Thanks in advance for your help!!!
Matt
I have a basic self taught understanding of VBA which works for most of the things I try and do, but I need some help with buttons!
I have a sub that successfully creates a button in consecutive cells and names each button after consecutive worksheets. It looks like it works fine and I can assign the macro I want to use ok.
What I really need tho, is a way of copying out the name of the button, as that is what I would need to run the other macros (to select the worksheet I need, run the calculations for the product number etc).
I'm sure its not the most pretty or efficient code (but it works and more importantly I understand what it's doing!) This creates the buttons...
Sub BUTTON_ADD()
Dim btn As Button
Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete
Dim t As Range
For i = 21 To 22
Set t = ActiveSheet.Range(Cells((i - 18), 6), Cells((i - 18), 6))
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.OnAction = "BUTTON_CLICK"
.Caption = Range("A1").Offset(i - 19, 3).Value
'.Name = "TEST"
End With
Next i
Application.ScreenUpdating = True
End Sub
But I need Sub BUTTON_CLICK to copy the caption (I don't seem to need a name for the button?) so that I can run other macros... eg Sheet 21 and the first button are named 185632, so I need to be able to paste that number in a cell somewhere!
Thanks in advance for your help!!!
Matt