OnAction Syntax

[cosmarchy]

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

[austenr]

Try this:

With Custom.Controls(i)
Select Case i
Case 1
Custom.OnAction = "hello"
Custom.FaceId = 984
Custom.TooltipText = "Do Something"
Custom.Tag = "Custom"
End Select
End With

[cosmarchy]

Try this:

With Custom.Controls(i)
Select Case i
Case 1
Custom.OnAction = "hello"
Custom.FaceId = 984
Custom.TooltipText = "Do Something"
Custom.Tag = "Custom"
End Select
End With


Thanks but all I get is this
http://img163.imageshack.us/img163/7208/90645887.jpg
and I know the macros aren't disabled!!

[PhilC]

Hi Cosmarchy,

2 things, The code that Austenr should have the "Custom" keyword removed on the 4 lines of code in the case statement. I'm not sure why it didn't error out on you. The IDE read the code
Custom.Controls(1)Custom.OnAction = ... The correct code should be something like this:



With Custom.Controls(i)


Select Case i


Case 1
.OnAction = "!<hello>"
.FaceId = 984
.TooltipText = "Do Something"
.Tag = "Custom"

End Select
End With


The hello() function may cause a problem when using this code, I can't test my theory as I am running Excel 2007, and I can't access command bars like you can in earlier versions. Simply change function hello() to Sub hello() and you should be alright.

[Paul_Hossler]

Question: I've never seen


.OnAction = "!<hello>"


.OnAction used like that, with the ! and the < >

Only ever seen something like

.OnAction = "Hello"


unless I wanted to specify the Hello() in a particular module

Is it because it's part of the Class?

Paul

[cosmarchy]

Is it because it's part of the Class?

That's what I believe is the case???? The trouble is what is the correct syntax to access a function within a class?

That is, if that is what's wrong!!

[austenr]

Not sure what you are looking for but have a look at Chip Pearsons page

http://www.cpearson.com/excel/VbeMenus.aspx

[Paul_Hossler]

The way Chip's page does it is the way I've always seen it

.OnAction = "'" & ThisWorkbook.Name & "'!Procedure_Two"


Sometimes with ActiveWorkbook.Name

But never with the < and >


Maybe try


.OnAction = "'" & ThisWorkbook.Name & "'!Hello"


And with Hello in a standard module, not a Class

Paul