OnAction help needed...

Posted by Dank on October 05, 2001 5:37 AM

Hello,

This is the code that I'm currently using:-

Private Sub AddBar()
'Add a command button to the worksheet menu bar
Dim Combar As CommandBar
Dim Combut As CommandBarButton
Set Combar = Application.CommandBars("Worksheet Menu Bar")
Set Combut = Combar.Controls.Add(msoControlButton)
Combut.Style = msoButtonCaption
Combut.Caption = "&Transaction Report"
Combut.OnAction = "RunReport"
End Sub

I want to be able to pass an integer argument to the procedure RunReport but don't know how. Does anyone? RunReport(1) or RunReport 1 don't work because VBA thinks that is the full macro name.

Regards,
Daniel.

Posted by Jerid on October 05, 2001 5:42 AM

Daniel, I would do it this way.

Sub CallRunReport()
RunReport(1)
End Sub

Jerid

Posted by Dank on October 05, 2001 7:44 AM

Thanks,

That works. The actual reason I was asking is because I don't want the macro RunReport to be visible in the Tools, Macros window and I thought this would be a cheeky way of getting around it. If I use this method then the macro CallRunReport will be visible. Know of any way around this? Plus, I'm setting the OnAction property value in the ThisWorkbook code module so the macro RunReport has to be Public not Private.

Thanks for any further advice,
Daniel.

Posted by Jerid on October 05, 2001 10:33 AM

Try this, both procedure are private so you can't see them from the Macro Window.

Private Sub RunReport()
MsgBox "It worked"
End Sub