Programmatically Adding Controls (Macro) eg Button on A SpreadSheet

  • Thread starter Thread starter Martin SChukrazy
  • Start date Start date
M

Martin SChukrazy

Hi
Is there any way that i can add a control for .e.g. a button and some
associated macro code for it programmatically when a sheet is activated in
Excel ????

Thanks
Martin
 
I wrote some code in this NG to demonstrate adding menu items recently.

There were two procedures..one to add the menu & one to remove it.

In your worksheet's activate event you can add code to make the menu visible
and in the deactivate event add code to make it invisible

In a standard module add:

sub auto_open()
Set_menu
end sub
Sub Set_Menus()
Dim cmd As CommandBarPopup , ctrl As CommandBarControl , ctrldrop As
CommandBarControl
Kill_Menus
Set cmd = CommandBars("Worksheet Menu
Bar").Controls.Add(msoControlPopup, temporary:=True)
With cmd
.Caption = "M&yTools"
With cmd.Controls.Add(msoControlButton)
.Caption = "Ctrl &1"
.Visible = True
.OnAction = "menu1"
End With
With .Controls.Add(msoControlPopup)
.Caption = "Subs 1"
With .Controls.Add(msoControlButton)
.Caption = "Sub1 &1"
.OnAction = "menu2"
End With
With .Controls.Add(msoControlButton)
.Caption = "Sub1 &2"
.OnAction = "menu2"
End With
End With
With .Controls.Add(msoControlPopup)
.Caption = "Subs 2"
With .Controls.Add(msoControlButton)
.Caption = "Sub2 &1"
.OnAction = "menu2"
End With
With .Controls.Add(msoControlButton)
.Caption = "Sub2 &2"
.OnAction = "menu2"
End With
End With
End With
Set cmd = Nothing
End Sub
Sub Kill_Menus()
On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("MyTools").Delete
On Error GoTo 0
End Sub
Sub menu1()
MsgBox "Menu 1"
End Sub
Sub menu2()
MsgBox "Menu 2"
End Sub

In the selected worksheets code page add:
Private Sub Worksheet_Activate()
CommandBars("Worksheet Menu Bar").Controls("MyTools").Visible = True
End Sub

Private Sub Worksheet_Deactivate()
CommandBars("Worksheet Menu Bar").Controls("MyTools").Visible = False
End Sub

The only thing missing is a trap to ensure th emenu exists...but this truns
OK as is
 
Hi Martin,

Here is some code that adds a control toolbox button and creates the event
code for it

Sub CreateControlButton()
Dim oWs As Worksheet
Dim oOLE As OLEObject

Set oWs = ActiveSheet

Set oOLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=200, Top:=100, Width:=80, Height:=32)

'To set with a cell
'With Range("H2")
' Set oOLE =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
' Left:=.Left, Top:=.Top, Width:=.Width,
Height:=.Height)
'End With

With oOLE
.Object.Caption = "Run myMacro"
.Name = "myMacro"
End With

With ThisWorkbook.VBProject.VBComponents(oWs.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", oOLE.Name) + 1, _
vbTab & "If Range(""A1"").Value > 0 Then " & vbCrLf & _
vbTab & vbTab & "Msgbox ""Hi""" & vbCrLf & _
vbTab & "End If"

End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob,
Would it be possible to import a module.bas file to create a set of macros
for the activeX controls or do these need to sit behind the worksheet?

Also what about using a template file with the code already installed?

Cheers
Nigel
 
Hi Nigel,

No you couldn't import a code module, as the code for these type of controls
have to be in the worksheet that the control resides on. As you add a
control to your worksheet, it is already present.

You should be able to do that with Form buttons though (he blithely says!).

You can also create the code in advance of adding the control, although it
does beg the question that if you know what code you want in advance would
you not know you want a control, so add it then?

The other thing you can do is set it all up in advance and hide the control
until you need it.

Lots of options.

--

HTH

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

Nigel said:
Hi Bob,
Would it be possible to import a module.bas file to create a set of macros
for the activeX controls or do these need to sit behind the worksheet?

Also what about using a template file with the code already installed?

Cheers
Nigel

activated




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
Click to expand...
=---
 
Back
Top