Fastest way to create clickable buttons in Excel via VBA

[Charles]

Hi there

I am generating worksheets in VBA with lots of buttons, i.e. cells
that launch a macro when clicked. The neatest way I found so far is to
create a rectangular shape to which I assign an "onaction" property.
The thing is that I need to add a lot of them in a spreadsheet (a few
hundreds at least), and it is slowing down my code significantly.

I was wondering if there wasn't a smarter way to launch theses macro
from excel. I was thinking to adding some events in the sheet, but
that would require to be able to add some code to the sheet via VBA
(the onchange subs), which I am not sure is doable.

Would someone know a faster alternative?

thanks in advance
Charles

 

[Leith Ross]

Hello Charles,

This is a rather difficult question to answer without seeing how you
workbook is laid out and where these buttons are to placed on th
worksheet. Another piece of puzzle is how many macros do you need t
assign to these buttons? These will determine what events need t
programmed, if any. If you post your workbook here a
www.thecodecage.com, I could you a more detailed and accurate answer.

Sincerely,
Leith Ros

 

[Gary''s Student]

You don't need a separate button for each macro. Setup a list of the macros
in some column. Thenset Data Validation on some cell, say B9, to pick from
the list.

The have an event macro monitor B9 and call the selected macro for you:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("B9"), Target) Is Nothing Then Exit Sub
Application.Run Target.Value
End Sub

 

[Rick Rothstein]

To give you an idea of how the event would work, try the following. Right
click the tab at the bottom of the worksheet you want this functionality on
(event code must go in the worksheet's code module), select View Basic on
the popup menu and copy/paste the following code into the code window (the
worksheet's code module) that appeared...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A12")) Is Nothing Then
If Target.Row = 1 And Target.Column = 1 Then
MsgBox "You entered into cell A1"
ElseIf Target.Row = 1 And Target.Column = 2 Then
MsgBox "You entered into cell B1"
ElseIf Target.Row = 1 And Target.Column = 3 Then
MsgBox "You entered into cell C1"
ElseIf Target.Row = 1 And Target.Column = 4 Then
MsgBox "You entered into cell D1"
ElseIf Target.Row = 2 And Target.Column = 1 Then
MsgBox "You entered into cell A2"
ElseIf Target.Row = 2 And Target.Column = 2 Then
MsgBox "You entered into cell B2"
ElseIf Target.Row = 2 And Target.Column = 3 Then
MsgBox "You entered into cell C2"
ElseIf Target.Row = 2 And Target.Column = 4 Then
MsgBox "You entered into cell D2"
End If
End If
End Sub

The above code is set up for the range A12 (go back to the worksheet and
click the cells in this range to see the code in action), but the range
could be expanded in the Range call in the first If..Then statement and then
add additional ElseIf statements to cover the additional cells. It is
possible that then ElseIf structure could be simplified depending on if
there are common functions being performed for multiple cells; but, as a
worse case scenario, you can just include an ElseIf statement for each cell.
Oh, and obviously, you would replace my MsgBox lines with the code you
actually want to run (calls to your other subroutines I would guess).

 

[Charles]

Thanks. As I said, the worksheet_change could be a solution
(particularly coupled with range names). The thing is I create the
sheet with VBA. I would need to add the code "Private Sub
Worksheet_SelectionChange(ByVal Target As Range)..." to the newly
created sheet with VBA. Is this feasible?

Charles

 

[NA_AB]

hey charles, i don't have a solution to your problem but "onAction" on your
post interests me, I was doing the same and now I have the "rectangular
shape" on my cell but am not able to assign an event handler that handles its
click! could you please elaborate on how to use 'onAction' to create a Click
handle?!

 

[Charles]

Dim R2 as Range
Dim Sh As Shape

Set R2 = ActiveSheet.Range("B2")
Set Sh = R2.Worksheet.Shapes.AddTextbox
(msoTextOrientationHorizontal, R2.Left, R2.Top, R2.Width, R2.Height)
Sh.Placement = xlMoveAndSize
Sh.OnAction = "'TheSubName NumericAgument'"
alternatively Sh.OnAction = "'TheSubName ""NonNumericAgument""'"

Charles