Couple of things, vbUpperCase is a numeric constant rather than a text value.
Your argument is the same name as the function, StrConv.
You can pass an argument on the OnAction
Set ctlSub2 = Ctl.Controls.Add(msoControlButton, , , , True)
With ctlSub2
.Caption = "vbUpperCase"
.OnAction = "'CaseTool " & vbUpperCase & "'"
.Style = msoButtonCaption
End With
Public Sub CaseTool(ConvType As VbStrConv)
Dim Cell As Range
For Each Cell In Selection
Cell.Value = StrConv(Cell.Value, ConvType)
Next Cell
End Sub
But I think it would be better to use the Parameter property.
Set ctlSub2 = Ctl.Controls.Add(msoControlButton, , , , True)
With ctlSub2
.Caption = "vbUpperCase"
.OnAction = "CaseTool2"
.Style = msoButtonCaption
.Parameter = vbUpperCase
End With
Public Sub CaseTool2()
Dim Cell As Range
Dim lngConvType As VbStrConv
lngConvType = Application.CommandBars.ActionControl.Parameter
For Each Cell In Selection
Cell.Value = StrConv(Cell.Value, lngConvType)
Next Cell
End Sub
You could also use the ActionControl object to get the caption
Msgbox Application.CommandBars.ActionControl.Caption
Bookmarks