Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.Comma ndButton." & 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem with code
:="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.Comma ndButton." & 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem with code
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.Comma ndButton" & 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.Comma ndButton." & 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem with code
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.Comma ndButton." & 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem with code
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.Comma ndButton." & 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem with code
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" wrote in message ... 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.Comma ndButton" & 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.Comma ndButton." & 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem with code
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.Comma ndButton.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 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.Comma ndButton" & 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.Comma ndButton." & 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem with code
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.Comma ndButton.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, 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.Comma ndButton.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 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.Comma ndButton" & 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.Comma ndButton." & 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax problem with code | Excel Discussion (Misc queries) | |||
Help with Syntax in Code | Excel Programming | |||
Help with Syntax in Code | Excel Programming | |||
Syntax of this code | Excel Programming | |||
Syntax Problem with formula code | Excel Programming |