Automatically Create VBA Code

[tubbsy123]

Hi,

I've been using some code to dynamically create a button in an excel worksheet based on the output of a case statement.

The code is as follows:

Sheet1.OLEObjects.Add ClassType:="Forms.CommandButton.1", Left:=0, Top:=0, Width:=126.75, Height:=25.5
Sheet1.Shapes(Sheet1.Shapes.Count).OLEFormat.Object.Object.Caption = "Click Me"

Is it possible to automatically write the on_click code for the button at the time of the buttons creation?

The code I will be using is:

Private Sub btnNext_Click()
    Call Macro1
End Sub

Any help would be greatly received.

Regards

tubbsy123

 

[HughLerwill]

While the end result of what you need is rather different some of the basic techniques are demonstrated in thread707-1587091

 

[SkipVought]

Hi,

Is there a finite number of controls that can be pre-defined by the requirements?

It is possible to minimize the number of control on a sheet by making ONE control of a type and hiding or displaying in context with the user's range selection on the sheet. This is especially applicable to ComboBox controls, where in column A the control list might be a vendor id and in column B the control list might be an area id, based on the selection in a for that row.

Another way of approching sheet control objects: Make ALL the controls and hide them until needed, rather than create/delete on the fly.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

I have to agree with Skip. It is MUCH mo repreferable to have all yuor controls in place. If you need to not visible, then have them not visible. This is a single instruction and easily used. I have seen very very few cases where creating controls dynamically was really required.

If you really realy have to ( and I doubt this), yes you can write to VBA using VBA. Writinf line by line works, but an alternative is to have the procedure - your _Click event - already written, and simply import it.

Gerry

 

[MajP]

Just google this. There are plenty of examples.

http://en.kioskea.net/faq/1105-adding-a-vba-commandbutton-with-its-respective-the-code

 

[fumei]

I just want to point out that tyhe code at that link has a surprisingly fatal error. I.e. it will not do anything because the code for the Click event Sub is:

Sub ButtonTest_Click()

and the button actual name is:

Obj.Name = "TestButton"

A surprising error IMO.

Clicking the button will NEVER fire Sub ButtonTest_Click.

Gerry

 

[combo]

If for any reason you still need to create controls on the worksheet in the fly, I recommend having all the code ready and using old form controls:

Sub CreateFormsButton()
With ActiveSheet.Buttons.Add(364.5, 68.25, 96.75, 21.75)
    .Name = "TestButton"
    .OnAction = "Test"
    .Caption = "Click me"
End With
End Sub

Sub Test()
MsgBox "Fired!"
End Sub

You can find them in the object browser after displaying hidden members.

The advantages:
- no dynamic code,
- more worksheet friendly controls,
- [tt]Application.Caller[/tt] returns the name of the control, so one procedure can easily handle multiple controls.

combo