Macro to insert command buttons
 

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.Comma ndButton.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

Macro to insert command buttons
 

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.



WLMPilot wrote:

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.Comma ndButton.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

Macro to insert command buttons
 

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" wrote in message
...
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.



WLMPilot wrote:

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.Comma ndButton.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

Macro to insert command buttons
 

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 wrote:

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" wrote in message
...
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.



WLMPilot wrote:

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.Comma ndButton.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

--

Dave Peterson

Macro to insert command buttons
 

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

Regards,
Peter T

"Dave Peterson" wrote in message
...
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 wrote:

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
<snip

Macro to insert command buttons
 

I don't understand what you mean.



Peter T wrote:

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

Regards,
Peter T

"Dave Peterson" wrote in message
...
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 wrote:

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
<snip

--

Dave Peterson

Macro to insert command buttons
 

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

Works perfectly!!!!

Les

"Dave Peterson" wrote:

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.



WLMPilot wrote:

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.Comma ndButton.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

Macro to insert command buttons
 

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" wrote in message
...
I don't understand what you mean.



Peter T wrote:

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

Regards,
Peter T

"Dave Peterson" wrote in message
...
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 wrote:

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
<snip

--

Dave Peterson

Macro to insert command buttons
 

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


Peter T wrote:

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" wrote in message
...
I don't understand what you mean.



Peter T wrote:

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

Regards,
Peter T

"Dave Peterson" wrote in message
...
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 wrote:

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
<snip

--

Dave Peterson

--

Dave Peterson