Syntax problem with code

Below is a macro I am testing. I am trying to create 26 command buttons,
starting with Commandbutton6, but a caption starting with number 1.
Something is wrong with the "Activesheet.OLEObject" line and I don't know
what.

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

Can someone tell me what is wrong? Thanks

One other question. I have seen the syntax similar to what I show below.
If using this syntax, what is "newbutton" DIM as?

Set newbutton = Applicationsheet.OLEObject.Add(Forms.CommandButton....)

Thanks,
Les

JLGWhiz
Sat Mar 15 14:20:01 CDT 2008

:="Forms.CommandButton." & k + 5

Remove the period after CommandButton.

You want CommandButton6 Not CommandButton.6

"WLMPilot" wrote:

> Below is a macro I am testing. I am trying to create 26 command buttons,
> starting with Commandbutton6, but a caption starting with number 1.
> Something is wrong with the "Activesheet.OLEObject" line and I don't know
> what.
>
> Private Sub CommandButton2_Click()
> Dim h, w, t, l As Long
> Dim k As Integer
> h = 21
> w = 91.5
> l = 50
> t = 92
> For k = 1 To 26
> ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton." & k + 5,
> Left = l, Top = t, Width = w, Height = h)
> .Object.Caption = "Button " & k + 5
> t = t + 38
> If k = 7 Or k = 14 Or k = 20 Then
> l = l + 145
> t = 92
> End If
> Next k
> End Sub
>
> Can someone tell me what is wrong? Thanks
>
> One other question. I have seen the syntax similar to what I show below.
> If using this syntax, what is "newbutton" DIM as?
>
> Set newbutton = Applicationsheet.OLEObject.Add(Forms.CommandButton....)
>
> Thanks,
> Les

WLMPilot
Sat Mar 15 14:58:03 CDT 2008

Thanks, but it is still coming up with a syntax error. Here is the current
line, which appears on one line (vs two lines).

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton" & k + 5,
Left:=l, Top:=t, Width:=w, Height:=h)

"JLGWhiz" wrote:

> :="Forms.CommandButton." & k + 5
>
> Remove the period after CommandButton.
>
> You want CommandButton6 Not CommandButton.6
>
> "WLMPilot" wrote:
>
> > Below is a macro I am testing. I am trying to create 26 command buttons,
> > starting with Commandbutton6, but a caption starting with number 1.
> > Something is wrong with the "Activesheet.OLEObject" line and I don't know
> > what.
> >
> > Private Sub CommandButton2_Click()
> > Dim h, w, t, l As Long
> > Dim k As Integer
> > h = 21
> > w = 91.5
> > l = 50
> > t = 92
> > For k = 1 To 26
> > ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton." & k + 5,
> > Left = l, Top = t, Width = w, Height = h)
> > .Object.Caption = "Button " & k + 5
> > t = t + 38
> > If k = 7 Or k = 14 Or k = 20 Then
> > l = l + 145
> > t = 92
> > End If
> > Next k
> > End Sub
> >
> > Can someone tell me what is wrong? Thanks
> >
> > One other question. I have seen the syntax similar to what I show below.
> > If using this syntax, what is "newbutton" DIM as?
> >
> > Set newbutton = Applicationsheet.OLEObject.Add(Forms.CommandButton....)
> >
> > Thanks,
> > Les

Dave
Sat Mar 15 15:15:53 CDT 2008

Don't change classtype.

It should always be:
..., ClassType:="Forms.CommandButton.1", ...

Without using the variables, this is how I'd add that commandbutton:

Dim OLEObj As OLEObject

Set OLEObj = ActiveSheet.OLEObjects.Add _
(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=323.25, Top:=50.25, Width:=110.25, _
Height:=24.75)

OLEObj.Object.Caption = "Hi there"

======
ps.

This line:
Dim h, w, t, l As Long
is the same as:
Dim h as variant, w as variant, t as variant, l As Long

Is that what you really meant to do?


WLMPilot wrote:
>
> Below is a macro I am testing. I am trying to create 26 command buttons,
> starting with Commandbutton6, but a caption starting with number 1.
> Something is wrong with the "Activesheet.OLEObject" line and I don't know
> what.
>
> Private Sub CommandButton2_Click()
> Dim h, w, t, l As Long
> Dim k As Integer
> h = 21
> w = 91.5
> l = 50
> t = 92
> For k = 1 To 26
> ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton." & k + 5,
> Left = l, Top = t, Width = w, Height = h)
> .Object.Caption = "Button " & k + 5
> t = t + 38
> If k = 7 Or k = 14 Or k = 20 Then
> l = l + 145
> t = 92
> End If
> Next k
> End Sub
>
> Can someone tell me what is wrong? Thanks
>
> One other question. I have seen the syntax similar to what I show below.
> If using this syntax, what is "newbutton" DIM as?
>
> Set newbutton = Applicationsheet.OLEObject.Add(Forms.CommandButton....)
>
> Thanks,
> Les

--

Dave Peterson

Dave
Sat Mar 15 15:17:11 CDT 2008

You may want to add a name to each button, too:

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



JLGWhiz wrote:
>
> :="Forms.CommandButton." & k + 5
>
> Remove the period after CommandButton.
>
> You want CommandButton6 Not CommandButton.6
>
> "WLMPilot" wrote:
>
> > Below is a macro I am testing. I am trying to create 26 command buttons,
> > starting with Commandbutton6, but a caption starting with number 1.
> > Something is wrong with the "Activesheet.OLEObject" line and I don't know
> > what.
> >
> > Private Sub CommandButton2_Click()
> > Dim h, w, t, l As Long
> > Dim k As Integer
> > h = 21
> > w = 91.5
> > l = 50
> > t = 92
> > For k = 1 To 26
> > ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton." & k + 5,
> > Left = l, Top = t, Width = w, Height = h)
> > .Object.Caption = "Button " & k + 5
> > t = t + 38
> > If k = 7 Or k = 14 Or k = 20 Then
> > l = l + 145
> > t = 92
> > End If
> > Next k
> > End Sub
> >
> > Can someone tell me what is wrong? Thanks
> >
> > One other question. I have seen the syntax similar to what I show below.
> > If using this syntax, what is "newbutton" DIM as?
> >
> > Set newbutton = Applicationsheet.OLEObject.Add(Forms.CommandButton....)
> >
> > Thanks,
> > Les

--

Dave Peterson

Rick
Sat Mar 15 15:22:10 CDT 2008

Try removing the parentheses...

ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton" & _
k + 5, Left:=l, Top:=t, Width:=w, Height:=h

Although I would also suggest adding a CStr function call around the k+5
also...

ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton" & _
CStr(k + 5), Left:=l, Top:=t, Width:=w, Height:=h

Rick


"WLMPilot" <WLMPilot@discussions.microsoft.com> wrote in message
news:38F907B6-114B-435F-BEA2-A217478FD554@microsoft.com...
> Thanks, but it is still coming up with a syntax error. Here is the
> current
> line, which appears on one line (vs two lines).
>
> ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton" & k + 5,
> Left:=l, Top:=t, Width:=w, Height:=h)
>
> "JLGWhiz" wrote:
>
>> :="Forms.CommandButton." & k + 5
>>
>> Remove the period after CommandButton.
>>
>> You want CommandButton6 Not CommandButton.6
>>
>> "WLMPilot" wrote:
>>
>> > Below is a macro I am testing. I am trying to create 26 command
>> > buttons,
>> > starting with Commandbutton6, but a caption starting with number 1.
>> > Something is wrong with the "Activesheet.OLEObject" line and I don't
>> > know
>> > what.
>> >
>> > Private Sub CommandButton2_Click()
>> > Dim h, w, t, l As Long
>> > Dim k As Integer
>> > h = 21
>> > w = 91.5
>> > l = 50
>> > t = 92
>> > For k = 1 To 26
>> > ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton." & k +
>> > 5,
>> > Left = l, Top = t, Width = w, Height = h)
>> > .Object.Caption = "Button " & k + 5
>> > t = t + 38
>> > If k = 7 Or k = 14 Or k = 20 Then
>> > l = l + 145
>> > t = 92
>> > End If
>> > Next k
>> > End Sub
>> >
>> > Can someone tell me what is wrong? Thanks
>> >
>> > One other question. I have seen the syntax similar to what I show
>> > below.
>> > If using this syntax, what is "newbutton" DIM as?
>> >
>> > Set newbutton = Applicationsheet.OLEObject.Add(Forms.CommandButton....)
>> >
>> > Thanks,
>> > Les

chip
Sat Mar 15 15:29:13 CDT 2008

I think you actually want ClassType to = Forms.CommandButton.1"

That seems to define the type of button vs. its number/name.

I got the following to work. Notice that I have .Select at the end of
the first

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=l, Top:=t, Width:=w, Height:=h).Select

With Selection
.Object.Caption = "Button " & k + 5
End With





On Mar 15, 2:58 pm, WLMPilot <WLMPi...@discussions.microsoft.com>
wrote:
> Thanks, but it is still coming up with a syntax error. Here is the current
> line, which appears on one line (vs two lines).
>
> ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton" & k + 5,
> Left:=l, Top:=t, Width:=w, Height:=h)
>
> "JLGWhiz" wrote:
> > :="Forms.CommandButton." & k + 5
>
> > Remove the period after CommandButton.
>
> > You want CommandButton6 Not CommandButton.6
>
> > "WLMPilot" wrote:
>
> > > Below is a macro I am testing. I am trying to create 26 command buttons,
> > > starting with Commandbutton6, but a caption starting with number 1.
> > > Something is wrong with the "Activesheet.OLEObject" line and I don't know
> > > what.
>
> > > Private Sub CommandButton2_Click()
> > > Dim h, w, t, l As Long
> > > Dim k As Integer
> > > h = 21
> > > w = 91.5
> > > l = 50
> > > t = 92
> > > For k = 1 To 26
> > > ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton." & k + 5,
> > > Left = l, Top = t, Width = w, Height = h)
> > > .Object.Caption = "Button " & k + 5
> > > t = t + 38
> > > If k = 7 Or k = 14 Or k = 20 Then
> > > l = l + 145
> > > t = 92
> > > End If
> > > Next k
> > > End Sub
>
> > > Can someone tell me what is wrong? Thanks
>
> > > One other question. I have seen the syntax similar to what I show below.
> > > If using this syntax, what is "newbutton" DIM as?
>
> > > Set newbutton = Applicationsheet.OLEObject.Add(Forms.CommandButton....)
>
> > > Thanks,
> > > Les

chip
Sat Mar 15 15:42:23 CDT 2008

Combining a few of the notes I would end up with something like:

Sub CommandButton2_Click()
Dim h As Long, w As Long, t As Long, l As Long
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
With Selection
.Object.Caption = "Button " & k + 5
.Name = "CommandButton" & k + 5
End With
t = t + 38
If k = 7 Or k = 14 Or k = 20 Then
l = l + 145
t = 92
End If

Next k
Range("a1").Select

End Sub


On Mar 15, 3:29 pm, chip.gor...@gmail.com wrote:
> I think you actually want ClassType to = Forms.CommandButton.1"
>
> That seems to define the type of button vs. its number/name.
>
> I got the following to work. Notice that I have .Select at the end of
> the first
>
> ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
> Left:=l, Top:=t, Width:=w, Height:=h).Select
>
> With Selection
> .Object.Caption = "Button " & k + 5
> End With
>
> On Mar 15, 2:58 pm, WLMPilot <WLMPi...@discussions.microsoft.com>
> wrote:
>
> > Thanks, but it is still coming up with a syntax error. Here is the current
> > line, which appears on one line (vs two lines).
>
> > ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton" & k + 5,
> > Left:=l, Top:=t, Width:=w, Height:=h)
>
> > "JLGWhiz" wrote:
> > > :="Forms.CommandButton." & k + 5
>
> > > Remove the period after CommandButton.
>
> > > You want CommandButton6 Not CommandButton.6
>
> > > "WLMPilot" wrote:
>
> > > > Below is a macro I am testing. I am trying to create 26 command buttons,
> > > > starting with Commandbutton6, but a caption starting with number 1.
> > > > Something is wrong with the "Activesheet.OLEObject" line and I don't know
> > > > what.
>
> > > > Private Sub CommandButton2_Click()
> > > > Dim h, w, t, l As Long
> > > > Dim k As Integer
> > > > h = 21
> > > > w = 91.5
> > > > l = 50
> > > > t = 92
> > > > For k = 1 To 26
> > > > ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton." & k + 5,
> > > > Left = l, Top = t, Width = w, Height = h)
> > > > .Object.Caption = "Button " & k + 5
> > > > t = t + 38
> > > > If k = 7 Or k = 14 Or k = 20 Then
> > > > l = l + 145
> > > > t = 92
> > > > End If
> > > > Next k
> > > > End Sub
>
> > > > Can someone tell me what is wrong? Thanks
>
> > > > One other question. I have seen the syntax similar to what I show below.
> > > > If using this syntax, what is "newbutton" DIM as?
>
> > > > Set newbutton = Applicationsheet.OLEObject.Add(Forms.CommandButton....)
>
> > > > Thanks,
> > > > Les