Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using CommandBar Captions (2000)

    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.

    <pre>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([img]/forums/images/smilies/cool.gif[/img]
    .Caption = ActiveSheet.Cells(46 + b, 3).Value
    .OnAction = "EntShifts"
    End With
    Next
    End With
    Set a = Nothing
    End Sub


    Sub EntShifts()
    <font color=448800> 'Cell.Value = Whatever the caption of the button pressed is</font color=448800>
    End Sub
    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using CommandBar Captions (2000)

    You could use

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

    Remarks:
    <UL><LI>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.
    <LI>I would use With mybar instead of With CommandBars("RCShifts")
    <LI>I would use With .Controls.Add(Type:=msoControlButton) instead of .Controls.Add Type:=msoControlButton and With CommandBars("RCShifts").Controls([img]/forums/images/smilies/cool.gif[/img]
    <LI>What is a in Set a = Nothing?
    <LI>Shouldn't you make the commandbar visible?[/list]HTH

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using CommandBar Captions (2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •