Control Click Subroutine (BHatMJ)

[BHatMJ]

HELP! I am adding a dynamic number of control buttons to a worksheet
dependent upon user input during run time (see my code below). I need to add
code to each of these buttons (also during run time) so that a subroutine is
called when the user selects the button.

ANY help would be greatly appreciated!!!




Sub AddCtrl(nCnt As Integer)

topPos = 20
' nCnt is an integer set at run time by the user
For lp = 1 to nCnt
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=100,
Top:=topPos, Width:=50, Height:= 30).Select
topPos = topPos + 75
Next lp

End Sub

 

[Dave Peterson]

Here's some code that does one commandbutton:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("a1:b2")
Set OLEObj = .Parent.OLEObjects.Add _
(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height)
End With

With .Parent.VBProject.VBComponents(.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", OLEObj.Name) + 1, _
"Msgbox ""Hi"""
End With
End With

End Sub

=====
I'm not sure how married you are to the idea of using commandbuttons from the
control toolbox toolbar, but you may want to consider using buttons from the
Forms toolbar.

Then you can use the same macro (in a general module) for each button. Your
code would have to figure out what button called it, though (depending on what
your subroutine did).

 

[BHatMJ]

Thank you! It's the first solid indication I've had that this can actually
be done. I was beginning to think that it wasn't possible.

That said, the code line to insert text into the code module causes an Excel
error that closes Excel down every time. I can try to track down why but am
sure it would take me forever. Can you explain or give me an example of code
that would use buttons from the Forms toolbar?

Thank you very much for all of your help!!!

 

[Dave Peterson]

If I have a choice, I'll use the controls from the Forms toolbar. They seem to
"weigh down" excel a lot less. You can't do as many things with them, but
that's ok with me.

This first routine adds a bunch of buttons to a range and assigns the same macro
to each of those buttons.

The second routine just gives you an idea how to know what button was
clicked--and it clears a cell a long way away on the same sheet (so use a test
worksheet!).

Option Explicit
Sub FillARangeWithButtons()
Dim myRng As Range
Dim myCell As Range
Dim myBTN As Button

With ActiveSheet
Set myRng = .Range("b2:b10")
.Buttons.Delete 'nice for testing
End With

For Each myCell In myRng.Cells
With myCell
Set myBTN = .Parent.Buttons.Add(Top:=.Top, Left:=.Left, _
Width:=.Width, Height:=.Height)
End With

With myBTN
.Caption = "BTN_" & .TopLeftCell.Address(0, 0)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "myBTNMac"
End With
Next myCell
End Sub
Sub myBTNMac()
Dim myBTN As Button

Set myBTN = ActiveSheet.Buttons(Application.Caller)

With myBTN
MsgBox .Caption & vbLf & .TopLeftCell.Address & vbLf & .Name
.TopLeftCell.Offset(0, 12).ClearContents
End With
End Sub

Both of these routines would be placed in a General module--not behind the
worksheet.

 

[BHatMJ]

Thank you, thank you, thank you!! This is EXACTLY what I needed. Have a
great day.