Argument with onAction in a menu.

  • Thread starter Thread starter Zeth Larsson
  • Start date Start date
Z

Zeth Larsson

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
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

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
 
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)

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
 
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
 
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
 
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

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
 
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

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
 
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
 
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.
 
Back
Top