PDA

View Full Version : [SOLVED] CommandButton



Paleo
01-19-2005, 07:30 PM
I have created a CommandButton through a macro using this code:



ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=52.5, Top:=305.25, Width:=202.5, Height _
:=26.25).Select


My problem now is:

1) How on earth may I personalize my CommandButton (like Caption)?
2) How may I attribute code to it?

:banghead: :banghead: :dunno :help :help :help :help :dunno :banghead: :banghead:

johnske
01-19-2005, 08:07 PM
Hi Paleo,

Go to View/Toolbars click Control Toolbox. On the control toolbox that appears - click design Mode then click 'Properties' for captions etc., or 'View Code' to insert your code.

HTH
John

tony_813
01-19-2005, 10:42 PM
Hello Paleo,
Question 1:


Dim myCmdObj As OLEObject
Set myCmdObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=52.5, Top:=305.25, _
Width:=202.5, Height:=26.25)
myCmdObj.Name = "NameOfCommandButton" '<--Name you gave the command button
myCmdObj.Object.Caption = "Button 1"

Question 2:
-In the worksheet class where you have the commandbutton
-Enter:


Private Sub NameOfCommandButton_click() '<--- The name of your button then: _Click()
MsgBox "Command Button"
End Sub


I hope this helps
tony_813

Paleo
01-20-2005, 10:14 AM
Hi johske,

you answer is miles away from my question, but thanks for the help anyway.

tony_813 you answer where GREAT, solved problem 1 and of course problem 2 too, but I need it a little different because in problem 2 I need to creat the private sub for the command button dinamically. I mean, without human manipulation. The vba itself must create that VBA in the activesheet code. Is that possible?

Paleo
01-20-2005, 10:30 AM
Hi tony_813,

I found in the knowledge base a code that must do it. Just need to adapt it.The code is this one:


Sub TestingIt()
Dim prmtrs As String, toAdd As String
prmtrs = "Key1:=Range(""C1""), Order1:=xlAscending, Header:=xlNo"
toAdd = "Sub CreatedMacro()" & vbCrLf & " Cells.Sort " & prmtrs & vbCrLf & "'Range(""" & _
"A1:Z100"").Sort Key1:=Range(""D9""), Order1:=xlDescending, Header:=xlGuess" & vbCrLf & _
"End Sub"
delCode "CreatedMacro", "a1b2c3d4e5f6g7h8i9"
AddCode toAdd, "a1b2c3d4e5f6g7h8i9"
End Sub

tony_813
01-20-2005, 11:01 AM
Hello... Yes it is possible...
Excell 2002 and later though may not allow you to do so depending on the Macro Settings

Here is something that may work for you:


Dim Code As String
Dim NextLine As Integer
Code = "Sub CommandButton1_Click()" & vbCrLf
Code = Code & " 'code here" & vbCrLf
Code = Code & "End Sub"
With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, Code
End With

tony_813

johnske
01-20-2005, 06:21 PM
Hi johske,

you answer is miles away from my question, but thanks for the help anyway....etc.Sorry Paleo, but I thought I gave an exact answer to your stated question, if you meant to do all this programatically, try this:


Sub addbutton()
Dim myCmdObj As OLEObject, N%
Set myCmdObj = Worksheets("Sheet1").OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=52.5, Top:=50, _
Width:=202.5, Height:=26.25)
myCmdObj.Name = "MyButton"
myCmdObj.Object.Caption = "Button 1"
With ThisWorkbook.VBProject.VBComponents("Sheet1").Codemodule
N = .CountOfLines
.InsertLines N + 1, "Private Sub MyButton_Click()"
'//insert your own code below - here's an example...
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "MsgBox " & """" & "Insert your own code" & """"
.InsertLines N + 4, vbNewLine
.InsertLines N + 5, "End Sub"
End With
End Sub

Paleo
01-20-2005, 08:25 PM
Gee Johnske,

Now you got it great! Your code works but I need it a little different because I dont know on which sheet its actually running. It will run on multiple sheets so I need the line:


With ThisWorkbook.VBProject.VBComponents("Sheet1").Codemodule

To accept any sheet. I wont know if it is Sheet1 as in your exemplo or sheet28. Is there a way I may use the ActiveSheet there?

I have tried two ways already but none worked:
1)

With ThisWorkbook.VBProject.VBComponents(ActiveSheet).Codemodule

2)


Num = ActiveSheet.Index
With ThisWorkbook.VBProject.VBComponents(Num).Codemodule


Any ideas?

johnske
01-20-2005, 09:07 PM
Sure, try this:


Sub AddButtonToActiveSheet()
Dim myCmdObj As OLEObject, N%
Set myCmdObj = Worksheets(ActiveSheet.Name).OLEObjects. _
Add(ClassType:="Forms.CommandButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=52.5, Top:=50, _
Width:=202.5, Height:=26.25)
myCmdObj.Name = "MyButton"
myCmdObj.Object.Caption = "Button 1"
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Name).Codemodule
N = .CountOfLines
.InsertLines N + 1, "Private Sub MyButton_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "MsgBox " & """" & "Insert your own code" & """"
.InsertLines N + 4, vbNewLine
.InsertLines N + 5, "End Sub"
End With
End Sub

Paleo
01-20-2005, 09:20 PM
Hi johnske,

I am using this code:



Sub AddButtonToActiveSheet()
Dim myCmdObj As OLEObject, N%
Set myCmdObj = ActiveSheet.OLEObjects. _
Add(ClassType:="Forms.CommandButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=52.5, Top:=50, _
Width:=202.5, Height:=26.25)
myCmdObj.Name = "MyButton"
myCmdObj.Object.Caption = "Button 1"
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Name).Codemodule
N = .CountOfLines
.InsertLines N + 1, "Private Sub MyButton_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "MsgBox " & """" & "Insert your own code" & """"
.InsertLines N + 4, vbNewLine
.InsertLines N + 5, "End Sub"
End With
End Sub

As you suggested but am getting an error 9 in line:


With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Name).Codemodule


Any suggestions?

johnske
01-20-2005, 09:30 PM
Copied your code and it works fine on my machine (Win98SE, Office 2000)...

What version of office are you using?

PS dont run the code a 2nd time on the same sheet without deleting the code inserted the 1st time round - you'll get an error then...

Paleo
01-20-2005, 09:37 PM
Hi johnske,



my machine is WinXP Pro, Office 2003.

If I use


With ThisWorkbook.VBProject.VBComponents("Plan11").CodeModule

it works fine, but if I use

With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule

I got error 9.

Paleo
01-20-2005, 09:46 PM
Hi johnske,

problem finally solved. My solution was:



With ThisWorkbook.VBProject.VBComponents.(ActiveSheet.CodeName).CodeModule


Thanks for your help. I woldnt have get it working without you!

johnske
01-20-2005, 10:06 PM
OK, I think the problem lay with the sheet being renamed (I got the same error when I renamed the sheet) - try this now:


Sub AddButtonToActiveSheet1()
Dim myCmdObj As OLEObject, N%, MySheet$
MySheet = "Sheet" & ActiveSheet.Index
Set myCmdObj = ActiveSheet.OLEObjects. _
Add(ClassType:="Forms.CommandButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=52.5, Top:=50, _
Width:=202.5, Height:=26.25)
myCmdObj.Name = "MyButton"
myCmdObj.Object.Caption = "Button 1"
With ThisWorkbook.VBProject.VBComponents(MySheet).Codemodule
N = .CountOfLines
.InsertLines N + 1, "Private Sub MyButton_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "MsgBox " & """" & "Insert your own code" & """"
.InsertLines N + 4, vbNewLine
.InsertLines N + 5, "End Sub"
End With
End Sub

johnske
01-20-2005, 10:13 PM
We must have been posting at the same time, yes, the solution you found is neater than the one I gave in my last post - use it.

Glad to see you got it working OK.

John

tony_813
01-20-2005, 10:24 PM
Hello Everyone,
Just got in from work....
Talk about team work....
I'm glad everything worked out for you Paleo...

tony_813

Paleo
01-24-2005, 06:17 AM
Hi tony and john,

yes it worked just fine. Many thanks for your help guys. Hey you may count on me on whatever I may help you both too.

johnske
01-25-2005, 08:51 PM
Glad to see you've got it working OK. Have started working on a web page recently and have put an expansion on this (with a sting in the tail) as my first example there. If you wanna check it out, click here (http://www.geocities.com/johnske100/AddOLEButtonPage.html) :devil:

Paleo
01-26-2005, 03:22 AM
Hi John,

great, congrats. I am doing some changes in it by my own too and as soon as it gets done I will provide you with the final code too, to see if its usefull for you.

Paleo
01-26-2005, 07:16 AM
Hi John,

I have put in the KB my new version from this post to help others. Take a look over it and let me know if you think I should change something.