assign code to control object in run time VBA

  • Thread starter Thread starter Steven D
  • Start date Start date
S

Steven D

I have a procedure in VBA (in personal.xls) that creates
a command button, automatically calling it
[commandbutton1] & putting it on a sheet called "Graphs".
I can set usual properties for it such as:

..caption
..accelertor &
..value

in my code at run time but I can't find a way to assign a
sub procedure to the command button and it's driving me
up the wall ;(

For example, all I would like to be able to would be
start with something simple like have code assigned to
the button such as:

sub commandbutton1()
msgbox "hi"
end sub

Is this possible?

Currently the only way I know of to create my spreadsheet
with my command button in and be able to click it and
come up with that message box is to have a separate
procedure alreay in my personal.xls. If I were to send
other people the spreadsheet I'd have to copy the module
with all this code in it, in to the new workbook for the
command button's code to run. Which I don't fancy doing!

Any help would really be appreciated. I can get round the
problem as mentionned but having the code actually 'in'
the new workbooks command button would be better.

Regards

Steven D
 
Steven,

Is it a Forms button or a worksheet control button.

The former can be assigned with .OnAction = , if the latter, it is event
code in the worksheet

Private Sub Commandbutton1_Click()
'code here
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,
I put it there using this code:
------------------------------------------------
ActiveSheet.OLEObjects.Add
(ClassType:="Forms.CommandButton.1", Link:=False,
DisplayAsIcon:=False, Left:=383.25, Top:=53.25,
Width:=42, Height:=9.75).Select
------------------------------------------------
so I presume that means it's a Forms button - I'm not
sure, now we're into the realms beyond my experience!


Thanks
Steven D


-----Original Message-----
Steven,

Is it a Forms button or a worksheet control button.

The former can be assigned with .OnAction = , if the latter, it is event
code in the worksheet

Private Sub Commandbutton1_Click()
'code here
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Steven D said:
I have a procedure in VBA (in personal.xls) that creates
a command button, automatically calling it
[commandbutton1] & putting it on a sheet called "Graphs".
I can set usual properties for it such as:

.caption
.accelertor &
.value

in my code at run time but I can't find a way to assign a
sub procedure to the command button and it's driving me
up the wall ;(

For example, all I would like to be able to would be
start with something simple like have code assigned to
the button such as:

sub commandbutton1()
msgbox "hi"
end sub

Is this possible?

Currently the only way I know of to create my spreadsheet
with my command button in and be able to click it and
come up with that message box is to have a separate
procedure alreay in my personal.xls. If I were to send
other people the spreadsheet I'd have to copy the module
with all this code in it, in to the new workbook for the
command button's code to run. Which I don't fancy doing!

Any help would really be appreciated. I can get round the
problem as mentionned but having the code actually 'in'
the new workbooks command button would be better.

Regards

Steven D
Click to expand...


.
Click to expand...
 
Steven,

No that is not a Forms command button, it is the worksheet control button
that I mentioned.

Goto menu Tools>Customize>Toolbars
Make sure that the Control Toolbox entry is selected
On this toolbar, click the design mode icon (A blue-green triangle icon)
Double-click your button, and a code module will open up with the event
procedure opened out (as per my original post_
Put your code in here
Click out of design mode on the worksheet

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Steven Douglas said:
Bob,
I put it there using this code:
------------------------------------------------
ActiveSheet.OLEObjects.Add
(ClassType:="Forms.CommandButton.1", Link:=False,
DisplayAsIcon:=False, Left:=383.25, Top:=53.25,
Width:=42, Height:=9.75).Select
------------------------------------------------
so I presume that means it's a Forms button - I'm not
sure, now we're into the realms beyond my experience!


Thanks
Steven D


-----Original Message-----
Steven,

Is it a Forms button or a worksheet control button.

The former can be assigned with .OnAction = , if the latter, it is event
code in the worksheet

Private Sub Commandbutton1_Click()
'code here
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Steven D said:
I have a procedure in VBA (in personal.xls) that creates
a command button, automatically calling it
[commandbutton1] & putting it on a sheet called "Graphs".
I can set usual properties for it such as:

.caption
.accelertor &
.value

in my code at run time but I can't find a way to assign a
sub procedure to the command button and it's driving me
up the wall ;(

For example, all I would like to be able to would be
start with something simple like have code assigned to
the button such as:

sub commandbutton1()
msgbox "hi"
end sub

Is this possible?

Currently the only way I know of to create my spreadsheet
with my command button in and be able to click it and
come up with that message box is to have a separate
procedure alreay in my personal.xls. If I were to send
other people the spreadsheet I'd have to copy the module
with all this code in it, in to the new workbook for the
command button's code to run. Which I don't fancy doing!

Any help would really be appreciated. I can get round the
problem as mentionned but having the code actually 'in'
the new workbooks command button would be better.

Regards

Steven D
Click to expand...


.
Click to expand...
Click to expand...
 
Cheers,
I'll have to get all the code into the spreadsheet later
then. Bummer

Ta
Steven D
-----Original Message-----
Steven,

No that is not a Forms command button, it is the worksheet control button
that I mentioned.

Goto menu Tools>Customize>Toolbars
Make sure that the Control Toolbox entry is selected
On this toolbar, click the design mode icon (A blue- green triangle icon)
Double-click your button, and a code module will open up with the event
procedure opened out (as per my original post_
Put your code in here
Click out of design mode on the worksheet

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob,
I put it there using this code:
------------------------------------------------
ActiveSheet.OLEObjects.Add
(ClassType:="Forms.CommandButton.1", Link:=False,
DisplayAsIcon:=False, Left:=383.25, Top:=53.25,
Width:=42, Height:=9.75).Select
------------------------------------------------
so I presume that means it's a Forms button - I'm not
sure, now we're into the realms beyond my experience!


Thanks
Steven D


-----Original Message-----
Steven,

Is it a Forms button or a worksheet control button.

The former can be assigned with .OnAction = , if the latter, it is event
code in the worksheet

Private Sub Commandbutton1_Click()
'code here
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have a procedure in VBA (in personal.xls) that creates
a command button, automatically calling it
[commandbutton1] & putting it on a sheet called "Graphs".
I can set usual properties for it such as:

.caption
.accelertor &
.value

in my code at run time but I can't find a way to assign a
sub procedure to the command button and it's driving me
up the wall ;(

For example, all I would like to be able to would be
start with something simple like have code assigned to
the button such as:

sub commandbutton1()
msgbox "hi"
end sub

Is this possible?

Currently the only way I know of to create my spreadsheet
with my command button in and be able to click it and
come up with that message box is to have a separate
procedure alreay in my personal.xls. If I were to send
other people the spreadsheet I'd have to copy the module
with all this code in it, in to the new workbook for the
command button's code to run. Which I don't fancy doing!

Any help would really be appreciated. I can get
Click to expand...
round
the
problem as mentionned but having the code actually 'in'
the new workbooks command button would be better.

Regards

Steven D



.
Click to expand...
Click to expand...


.
Click to expand...
 
Steven,

What is the problem in that? Maybe we can help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Steven D said:
Cheers,
I'll have to get all the code into the spreadsheet later
then. Bummer

Ta
Steven D
-----Original Message-----
Steven,

No that is not a Forms command button, it is the worksheet control button
that I mentioned.

Goto menu Tools>Customize>Toolbars
Make sure that the Control Toolbox entry is selected
On this toolbar, click the design mode icon (A blue- green triangle icon)
Double-click your button, and a code module will open up with the event
procedure opened out (as per my original post_
Put your code in here
Click out of design mode on the worksheet

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob,
I put it there using this code:
------------------------------------------------
ActiveSheet.OLEObjects.Add
(ClassType:="Forms.CommandButton.1", Link:=False,
DisplayAsIcon:=False, Left:=383.25, Top:=53.25,
Width:=42, Height:=9.75).Select
------------------------------------------------
so I presume that means it's a Forms button - I'm not
sure, now we're into the realms beyond my experience!


Thanks
Steven D



-----Original Message-----
Steven,

Is it a Forms button or a worksheet control button.

The former can be assigned with .OnAction = , if the
latter, it is event
code in the worksheet

Private Sub Commandbutton1_Click()
'code here
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have a procedure in VBA (in personal.xls) that
creates
a command button, automatically calling it
[commandbutton1] & putting it on a sheet
called "Graphs".
I can set usual properties for it such as:

.caption
.accelertor &
.value

in my code at run time but I can't find a way to
assign a
sub procedure to the command button and it's driving me
up the wall ;(

For example, all I would like to be able to would be
start with something simple like have code assigned to
the button such as:

sub commandbutton1()
msgbox "hi"
end sub

Is this possible?

Currently the only way I know of to create my
spreadsheet
with my command button in and be able to click it and
come up with that message box is to have a separate
procedure alreay in my personal.xls. If I were to send
other people the spreadsheet I'd have to copy the
module
with all this code in it, in to the new workbook for
the
command button's code to run. Which I don't fancy
doing!

Any help would really be appreciated. I can get round
the
problem as mentionned but having the code actually 'in'
the new workbooks command button would be better.

Regards

Steven D



.
Click to expand...


.
Click to expand...
Click to expand...
 
One way to get you started:


Option Explicit
Sub testme01()
Dim wks As Worksheet
Dim oleObj As OLEObject
Set wks = ActiveSheet

With wks.Range("b7")
Set oleObj = .Parent.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
End With

With oleObj
.Object.Caption = "Click Me"
.Name = "myButton"
End With

With ThisWorkbook.VBProject.VBComponents(wks.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", oleObj.Name) + 1, _
"Msgbox ""Hi"""
End With

End Sub

==============
Chip Pearson has a bunch of notes about writing code with code at:
http://www.cpearson.com/excel/vbe.htm

And I'm not sure what you're doing, but an alternative may exist.

If you're creating the worksheet, maybe set up a template worksheet (maybe
within the .xla file) or a common accessible location (LAN drive???) and have
the button and code already there.

Then just add that to the workbook instead of starting from new.



Steven D wrote:
<<snipped>>
 
Back
Top