View Full Version : Solved: Using OnAction correctly
makako
08-04-2006, 04:24 PM
Hi, I created a toolbar with some controls that work pretty much the same way ( all of them delete rows with specific variables ), in my code, i can only get the ".OnAction = 'MySub' " to be a sub with no arguments. The code is very long and I would like to have it all in one sub, is there any way i can use the same sub? So far I have tried
".OnAction = 'MySub(1)'"
".OnAction = 'MySub 1'"
".OnAction = 'MySub' & '(1)' "
".OnAction = 'MySub' & ' 1' "
etc but i keep getting error saying vba cannot find the sub (ex: ...MySub(1)).
I have an idea by using application.caller etc but im not sure how to use it, thanks.
Bob Phillips
08-05-2006, 07:45 AM
It is possible to create an argumnet via the OnACtion, but I strongly suggest that you avoid the temptation, it is bad-coding IMO, a maintenance headache.
Far better to set the Tag or Parameter property of the control, and pick that up in the OnAction procedure
With Application.CommandBars.ActionControl
Select Case .Parameter
Case 1: MsgBox 1
Case 2: MsgBox 2
End Select
End With
makako
08-07-2006, 05:12 PM
Private Sub Workbook_Open()
Dim Menu As CommandBarPopup
Dim BMenu As CommandBarButton
Dim BMenuPop As CommandBarControl
Dim Count As Integer
With Menu
'General Menu
Set BMenuPop = Menu.Controls.Add(msoControlPopup)
With BMenuPop
'Generates Control 1
Set BMenu = BMenuPop.Controls.Add(msoControlButton)
With BMenu
.Caption = "Control 1"
.OnAction = "subControl(1)"
.FaceId = 52
.Style = msoButtonIconAndCaption
End With
'Generates Control 2
Set BMenu = BMenuPop.Controls.Add(msoControlButton)
With BMenu
.Caption = "Control 2"
.OnAction = "subControl(2)"
.FaceId = 52
.Style = msoButtonIconAndCaption
End With
End With
End With
End Sub
sorry, i dont get it, do i have to set instead of the onaction property an action control parameter? I have never used it. haw (and where ) do i create it in this toolbar?
pd: I have no clue if this has something to do but the toolbar is created and deleted everytime i run a specific workbook so i dont get the toolbar in all excel. thanks
Bob Phillips
08-08-2006, 12:54 AM
As I said previously, set the Tag or Parameter property of the CONTROL.
Private Sub Workbook_Open()
Dim Menu As CommandBarPopup
Dim BMenu As CommandBarButton
Dim BMenuPop As CommandBarControl
Dim Count As Integer
With Menu
'General Menu
Set BMenuPop = Menu.Controls.Add(msoControlPopup)
With BMenuPop
'Generates Control 1
Set BMenu = BMenuPop.Controls.Add(msoControlButton)
With BMenu
.Caption = "Control 1"
.OnAction = "subControl"
.Tag = 1
.Parameter = 1 'take your pick
.FaceId = 52
.Style = msoButtonIconAndCaption
End With
'Generates Control 2
Set BMenu = BMenuPop.Controls.Add(msoControlButton)
With BMenu
.Caption = "Control 2"
.OnAction = "subControl"
.Tag = 2
.Parameter = 2 'take your pick
.FaceId = 52
.Style = msoButtonIconAndCaption
End With
End With
End With
End Sub
Zack Barresse
08-08-2006, 09:17 AM
I think you'll have a hard time passing variables via the OnAction parameter.
Bob Phillips
08-08-2006, 11:41 AM
I think you'll have a hard time passing variables via the OnAction parameter.
It can be done, but it is convoluted and a maintenance nightmare. Not worth the effort.
Zack Barresse
08-08-2006, 11:57 AM
Not worth the effort.
Exactly. :)
makako
08-08-2006, 05:43 PM
thanks, that worked perfectly. few questions about it,
in Application.CommandBars.ActionControl Im creating a reference to the commanbar that called the procedure? or in toolbars in general?, is the parameter unique in a toolbar? or is it shared by the entire application? (sorry, Ive never used it and havent found info about it)
mdmackillop
08-09-2006, 12:32 AM
I'm getting a runtime error 91 on this line. Any thoughts?
Set BMenuPop = Menu.Controls.Add(msoControlPopup)
Bob Phillips
08-09-2006, 01:52 AM
I'm getting a runtime error 91 on this line. Any thoughts?
Set BMenuPop = Menu.Controls.Add(msoControlPopup)
It is because he hasn't defined Menu at that point, it is still Nothing.
Bob Phillips
08-09-2006, 01:52 AM
thanks, that worked perfectly. few questions about it,
in Application.CommandBars.ActionControl Im creating a reference to the commanbar that called the procedure? or in toolbars in general?, is the parameter unique in a toolbar? or is it shared by the entire application? (sorry, Ive never used it and havent found info about it)
The calling commandbar control.
makako
08-09-2006, 08:39 AM
thanks
mdmackillop
08-09-2006, 10:29 AM
It is because he hasn't defined Menu at that point, it is still Nothing.
Thanks Bob.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.