Syntax problem with code

W

WLMPilot

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
 
J

JLGWhiz

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

Remove the period after CommandButton.

You want CommandButton6 Not CommandButton.6
 
W

WLMPilot

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)
 
D

Dave Peterson

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?
 
D

Dave Peterson

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

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

Rick Rothstein \(MVP - VB\)

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
 
C

chip.gorman

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
 
C

chip.gorman

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top