Macro to insert command buttons

[WLMPilot]

I have posted this before and I am still having problems. I have researched
myself and cannot come up with anything.

I have a macro that will create 26 commandbuttons on the active sheet. I
get one button created with caption "CommandButton4". I also get Run-time
Error 424: Object required.

I cannot figure it out. The "Activesheet" line matches everything I have
researched.

Any help is greatly appreciated!!!

Private Sub CommandButton2_Click()
Dim h, w, t, l As Long 'h=height, w=width, t=top, l=left
Dim k As Integer
h = 21
w = 91.5
l = 50
t = 92
For k = 1 To 26
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=l,
Top:=t, Width:=w, Height:=h).Select
OLEObject.Caption = "Button " & k
OLEObject.Name = "Week " & k
t = t + 38
If k = 7 Or k = 14 Or k = 20 Then
l = l + 145
t = 92
End If
Next k
End Sub

The code is suppose to create 26 buttons equal in size and space them,
aligning then equally apart vertically and horizontally.

Much appreciation for anyone helping with this!!!!

Les

 

[Dave Peterson]

Option Explicit
Private Sub CommandButton2_Click()
Dim h As Double
Dim w As Double
Dim t As Double
Dim l As Long 'h=height, w=width, t=top, l=left
Dim k As Long
Dim OLEObj As OLEObject
h = 21
w = 91.5
l = 50
t = 92
For k = 1 To 26
Set OLEObj = ActiveSheet.OLEObjects.Add _
(ClassType:="Forms.CommandButton.1", _
Left:=l, Top:=t, Width:=w, Height:=h)

OLEObj.Object.Caption = "Button " & k
OLEObj.Name = "Week " & k

t = t + 38
If k = 7 Or k = 14 Or k = 20 Then
l = l + 145
t = 92
End If
Next k
End Sub

Personally, I find plopping the buttons over cells a little easier to layout.

 

[Peter T]

Just to add, might be an idea to change

OLEObj.Name = "Week " & k

to
OLEObj.Name = "Week_" & k

IOW, avoid naming ActiveX controls with spaces or punctuation.

Regards,
Peter T

 

[Dave Peterson]

I don't like spaces in my names, either.

In fact, I'd use:

OLEObj.Name = "Week_" & format(k,"00")

So that they would all look the same (kind of).

 

[Peter T]

Ah, but type of name is not just a matter of preference with ActiveX
controls !

Regards,
Peter T

I don't like spaces in my names, either.

In fact, I'd use:

OLEObj.Name = "Week_" & format(k,"00")

So that they would all look the same (kind of).

<snip>

 

[Dave Peterson]

I don't understand what you mean.

Ah, but type of name is not just a matter of preference with ActiveX
controls !

Regards,
Peter T


<snip>

 

[WLMPilot]

Thank you, Thank you, Thank you!!!!

Works perfectly!!!!

Les

 

[Peter T]

Although you can name an ActiveX say "Week 1", the only way it could ever
be referenced with that name would be as a string, eg ws.OleObjects("week
1"). You could even give multiple controls the same name. However event
code would not respond with such a name, any event would use its original
object name.

Try either of the following

Programmatically add a CommandButton and give it a name with a space or
punctuation. In design mode double click the object with the expectation of
creating its click event in the module - the name in event will be not be
the one it was renamed as with the space.

Manually add a CommandButton, while selected, in the Names box rename it
with a space (it should stick). Now click the properties icon on the Control
toolbox toolbar and attempt to change "CommandButtonX" to the name with the
space - illegal name !

Regards,
Peter T

 

[Dave Peterson]

It wouldn't bother me to go through the OLEObjects collection. The mismatch
between the name and the event name would bother me more.