Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument with onAction in a menu.
Hi out there!
I'v create a menu in a XL worksheet with commands like With Application.CommandBars.Add("Certifiering", , False, True) .Visible = True .Position = msoBarLeft With .Controls With .Add(msoControlPopup) .Caption = "&CertDatabas" With .Controls With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data" End With Now I want to execute a sub from a .OnAction like With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data argValue" End With Would realy appreciate a hint on how to do that, if it can be done! Regards Zeth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument with onAction in a menu.
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Zeth Larsson" wrote in message ... Hi out there! I'v create a menu in a XL worksheet with commands like With Application.CommandBars.Add("Certifiering", , False, True) .Visible = True .Position = msoBarLeft With .Controls With .Add(msoControlPopup) .Caption = "&CertDatabas" With .Controls With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data" End With Now I want to execute a sub from a .OnAction like With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data argValue" End With Would realy appreciate a hint on how to do that, if it can be done! Regards Zeth |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument with onAction in a menu.
Zeth,
This is the command format .OnAction = "'" & ThisWorkbook.Name & "'!'Export_Data ""abc""'" Note the ' and " in places. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Zeth Larsson" wrote in message ... Hi out there! I'v create a menu in a XL worksheet with commands like With Application.CommandBars.Add("Certifiering", , False, True) .Visible = True .Position = msoBarLeft With .Controls With .Add(msoControlPopup) .Caption = "&CertDatabas" With .Controls With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data" End With Now I want to execute a sub from a .OnAction like With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data argValue" End With Would realy appreciate a hint on how to do that, if it can be done! Regards Zeth |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument with onAction in a menu.
Another way:
With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data" .Parameter = "argValue" End With Public Sub Export_Data() Dim parm As String parm = CommandBars.ActionControl.Parameter MsgBox parm '--- displays "argValue" End Sub In article , "Zeth Larsson" wrote: Hi out there! I'v create a menu in a XL worksheet with commands like With Application.CommandBars.Add("Certifiering", , False, True) .Visible = True .Position = msoBarLeft With .Controls With .Add(msoControlPopup) .Caption = "&CertDatabas" With .Controls With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export Data" End With Now I want to execute a sub from a .OnAction like With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export Data argValue" End With Would realy appreciate a hint on how to do that, if it can be done! Regards Zeth |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument with onAction in a menu.
I'm not familiar with this forum yet, I hope this reply
comes right. Many Thanks Bob Phillips and J.E. McGimpsey! I'v could verify both your solutions as perfectly functional. It spare me lots of trouble with "work around". Hope you both get a Merry Chrismas and a Happy New Year 2004! Zeth Larsson Valbo Sweden -----Original Message----- Hi out there! I'v create a menu in a XL worksheet with commands like With Application.CommandBars.Add("Certifiering", , False, True) .Visible = True .Position = msoBarLeft With .Controls With .Add(msoControlPopup) .Caption = "&CertDatabas" With .Controls With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data" End With Now I want to execute a sub from a .OnAction like With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data argValue" End With Would realy appreciate a hint on how to do that, if it can be done! Regards Zeth . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument with onAction in a menu.
Zeth,
On behalf of myself and (I am sure) JE, you're more than welcome. Have a happy holiday. The one thing neither JE or I mentioned is that the great strength of arguments is changing them on the fly. Both solutions are static, the argument value is set up front. It is very easy to set it just as easily on the fly with code such as With Application.CommandBars("Certifiering") With .Controls("&CertDatabas").Controls("Spara öppen kopia") .OnAction = "'" & ThisWorkbook.Name & "'!'Export_Data ""xyz""'" End With End With JE's code (not tested) would be With Application.CommandBars("Certifiering") With .Controls("&CertDatabas").Controls("Spara öppen kopia") .Parameter = "New Value" End With End With Regards Bob "Zeth Larsson" wrote in message ... I'm not familiar with this forum yet, I hope this reply comes right. Many Thanks Bob Phillips and J.E. McGimpsey! I'v could verify both your solutions as perfectly functional. It spare me lots of trouble with "work around". Hope you both get a Merry Chrismas and a Happy New Year 2004! Zeth Larsson Valbo Sweden -----Original Message----- Hi out there! I'v create a menu in a XL worksheet with commands like With Application.CommandBars.Add("Certifiering", , False, True) .Visible = True .Position = msoBarLeft With .Controls With .Add(msoControlPopup) .Caption = "&CertDatabas" With .Controls With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data" End With Now I want to execute a sub from a .OnAction like With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data argValue" End With Would realy appreciate a hint on how to do that, if it can be done! Regards Zeth . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument with onAction in a menu.
Note that Bob's method has been reported not to be supported in xl2002
(possibly xl2003) and in the latest service release of xl2000. I don't know what versions you were planning on using this with, but if you use that method, you may want to do some more extensive testing. -- Regards, Tom Ogilvy Zeth Larsson wrote in message ... I'm not familiar with this forum yet, I hope this reply comes right. Many Thanks Bob Phillips and J.E. McGimpsey! I'v could verify both your solutions as perfectly functional. It spare me lots of trouble with "work around". Hope you both get a Merry Chrismas and a Happy New Year 2004! Zeth Larsson Valbo Sweden -----Original Message----- Hi out there! I'v create a menu in a XL worksheet with commands like With Application.CommandBars.Add("Certifiering", , False, True) .Visible = True .Position = msoBarLeft With .Controls With .Add(msoControlPopup) .Caption = "&CertDatabas" With .Controls With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data" End With Now I want to execute a sub from a .OnAction like With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data argValue" End With Would realy appreciate a hint on how to do that, if it can be done! Regards Zeth . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument with onAction in a menu.
Tom,
That's interesting, I wonder why that would be? I have 2000, and I thought I had the latest SR, so I had better check. I also have 2002 on another machine., must try it there. Shame if you are right, it seems the proper (sic!) way to me, but as ever, thanks for the heads up. Regards Bob "Tom Ogilvy" wrote in message ... Note that Bob's method has been reported not to be supported in xl2002 (possibly xl2003) and in the latest service release of xl2000. I don't know what versions you were planning on using this with, but if you use that method, you may want to do some more extensive testing. -- Regards, Tom Ogilvy Zeth Larsson wrote in message ... I'm not familiar with this forum yet, I hope this reply comes right. Many Thanks Bob Phillips and J.E. McGimpsey! I'v could verify both your solutions as perfectly functional. It spare me lots of trouble with "work around". Hope you both get a Merry Chrismas and a Happy New Year 2004! Zeth Larsson Valbo Sweden -----Original Message----- Hi out there! I'v create a menu in a XL worksheet with commands like With Application.CommandBars.Add("Certifiering", , False, True) .Visible = True .Position = msoBarLeft With .Controls With .Add(msoControlPopup) .Caption = "&CertDatabas" With .Controls With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data" End With Now I want to execute a sub from a .OnAction like With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data argValue" End With Would realy appreciate a hint on how to do that, if it can be done! Regards Zeth . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Argument with onAction in a menu.
I am just restating what I have seen posted here and I could be wrong on
xl2000, but I believe that has been posted as well. I don't claim to be an authority on it. Since it was never officially supported, there is no reason Microsoft would feel they needed to retain it. Always a risk with use of undocumented methods. -- Regards, Tom Ogilvy Bob Phillips wrote in message ... Tom, That's interesting, I wonder why that would be? I have 2000, and I thought I had the latest SR, so I had better check. I also have 2002 on another machine., must try it there. Shame if you are right, it seems the proper (sic!) way to me, but as ever, thanks for the heads up. Regards Bob "Tom Ogilvy" wrote in message ... Note that Bob's method has been reported not to be supported in xl2002 (possibly xl2003) and in the latest service release of xl2000. I don't know what versions you were planning on using this with, but if you use that method, you may want to do some more extensive testing. -- Regards, Tom Ogilvy Zeth Larsson wrote in message ... I'm not familiar with this forum yet, I hope this reply comes right. Many Thanks Bob Phillips and J.E. McGimpsey! I'v could verify both your solutions as perfectly functional. It spare me lots of trouble with "work around". Hope you both get a Merry Chrismas and a Happy New Year 2004! Zeth Larsson Valbo Sweden -----Original Message----- Hi out there! I'v create a menu in a XL worksheet with commands like With Application.CommandBars.Add("Certifiering", , False, True) .Visible = True .Position = msoBarLeft With .Controls With .Add(msoControlPopup) .Caption = "&CertDatabas" With .Controls With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data" End With Now I want to execute a sub from a .OnAction like With .Add(msoControlButton) .Caption = "Spara öppen kopia" .OnAction = "Export_Data argValue" End With Would realy appreciate a hint on how to do that, if it can be done! Regards Zeth . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OnAction of Menu Bar with variable parameters | Excel Discussion (Misc queries) | |||
Set up OnAction of a Menu Bar button to a function with variable in the parameters | Excel Discussion (Misc queries) | |||
Passing Parameters through OnAction | Excel Programming | |||
OnAction | Excel Programming | |||
OnAction | Excel Programming |