Using CommandBar Captions (2000)

Topic

New Reply

Hi all,

Below I have some code which adds the contents of 30 cells to a right click menu bar structure. The text in each cell becomes the caption for a control button.

How do I capture the caption of the button later? In other words, if someone clicks on a button that has a caption of “5am – 1pm” then I want that cell to contain “5am – 1pm”

Would prefer to use one onaction sub and somehow tranfer the caption to it instead of writing 30 separate subs for each button.

Sub RC()
Dim mybar As CommandBar
Dim b As Integer
Application.CommandBars("cell").Enabled = False
 On Error Resume Next
    Set mybar = CommandBars.Add(Name:="RCShifts", Position:=msoBarPopup, _
        Temporary:=False)
        With CommandBars("RCShifts")
            For b = 1 To 30
                .Controls.Add Type:=msoControlButton
                With CommandBars("RCShifts").Controls(
                    .Caption = ActiveSheet.Cells(46 + b, 3).Value
                    .OnAction = "EntShifts"
                End With
            Next
        End With
    Set a = Nothing
End Sub


Sub EntShifts()
    'Cell.Value = Whatever the caption of the button pressed is
End Sub

Reply | Quote

Replies

You could use

Sub EntShifts()
ActiveCell.Value = CommandBars.ActionControl.Caption
End Sub

Remarks:

• Putting On Error Resume Next in your code without checking if an error occurred (Err 0) is dangerous – you’ll never know if something went wrong and why.
• I would use With mybar instead of With CommandBars(“RCShifts”)
• I would use With .Controls.Add(Type:=msoControlButton) instead of .Controls.Add Type:=msoControlButton and With CommandBars(“RCShifts”).Controls(
• What is a in Set a = Nothing?
• Shouldn’t you make the commandbar visible?
  [/list]HTH

Reply | Quote

Hey thanks Hans, I never knew that ActionControl even existed! The “Set a=nothing” is a leftover from some earlier stuff I was doing and the bar is called from the before right click sheet event. Thanks for the tips on the other stuff too.

Reply | Quote