cosmarchy
10-13-2010, 10:51 AM
Hi,
I have a class in a worksbook which is created when the workbook is opened.
When the class is initialised, it creates a custom menu which is also removed when the workbook is closed.
I am having problems with the OnAction method of the button in the menu.
What I want is to be able to call a function within the class and so far I have this:
Option Explicit
Private Sub create()
Dim Custom As CommandBar
Dim i As Integer
On Error Resume Next
If Custom Is Nothing Then
Set Custom = Application.CommandBars.Add(Name:="Custom")
For i = 1 To 1
Custom.Controls.Add Type:=msoControlButton
With Custom.Controls(i)
Select Case i
Case 1
.OnAction = "!<me.hello>"
.FaceId = 984
.TooltipText = "Do Something"
.Tag = "Custom"
End Select
End With
Next
End If
Custom.Enabled = True
Custom.Visible = True
End Sub
Private Sub Class_Initialize()
Call create
End Sub
Function hello()
MsgBox "Hello"
End Function
When I run this and click the menu button nothing happens.
Can someone please advise of the correct syntax to enable me to use functions within the same class?
Thanks
I have a class in a worksbook which is created when the workbook is opened.
When the class is initialised, it creates a custom menu which is also removed when the workbook is closed.
I am having problems with the OnAction method of the button in the menu.
What I want is to be able to call a function within the class and so far I have this:
Option Explicit
Private Sub create()
Dim Custom As CommandBar
Dim i As Integer
On Error Resume Next
If Custom Is Nothing Then
Set Custom = Application.CommandBars.Add(Name:="Custom")
For i = 1 To 1
Custom.Controls.Add Type:=msoControlButton
With Custom.Controls(i)
Select Case i
Case 1
.OnAction = "!<me.hello>"
.FaceId = 984
.TooltipText = "Do Something"
.Tag = "Custom"
End Select
End With
Next
End If
Custom.Enabled = True
Custom.Visible = True
End Sub
Private Sub Class_Initialize()
Call create
End Sub
Function hello()
MsgBox "Hello"
End Function
When I run this and click the menu button nothing happens.
Can someone please advise of the correct syntax to enable me to use functions within the same class?
Thanks