Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: msocontroldropdown for toolbar

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Location
    Ann Arbor, Michigan
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default msocontroldropdown for toolbar

    I'm sure this has been done before because it could be a very useful tool and easy to construct............

    I would like an msocontroldropdown on my toolbar that will list hyperlinks to my templates. That way it will be easy to load templates. (I have many templates and they are stored in many different folders)
    The templates are listed as hyperlinks down the A column of my personal spreadsheet (the hidden sheet that loads automaticaly).

    I have tested this manually with the
    .additem "template1" command
    and then have a if selected corresponding to it like
    Set wkb1 = Workbooks.Add (template:="c:\sba\template1.xls")

    The trouble with the manual approach is that the list of templates changes everyfew months and I don't want to edit code each time.

    Can the additem refer to a list of hyperlinks? If so how?

    Also I tried to do this with a dialog box listing the hyperlinks but then I would always be prompted to save the personal file at closing even though it wasn't changed.... So even if the toolbar-dropdown-hyperlink approach works referring to the personal file to get the hyperlink lists, can it be prevented from prompting me to save the personal file at closing?

    Thanks in advance.


  2. #2
    Board Regular
    Join Date
    Aug 2002
    Location
    Ann Arbor, Michigan
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: msocontroldropdown for toolbar

    Please help me.
    Here's what I have so far (but it doesn't work):

    Sub CreateToolbar()
    Dim cbar As CommandBar, cbctl As CommandBarControl
    Dim st As CommandBar
    ' get standard toolbar position so the new toolbar can be beside it
    Set st = Application.CommandBars("Standard")
    ' Delete if it exists
    For Each cbar In Application.CommandBars
    If cbar.Name = "Toolbar Example" Then cbar.Delete
    Next
    ' Create a floating toolbar
    Set cbar = Application.CommandBars.Add(Name:="Toolbar Example", _
    Position:=msoBarTop)
    cbar.Visible = True
    With cbar
    .RowIndex = st.RowIndex
    .Left = st.Width
    End With
    ' Add a list box
    Set cbctl = cbar.Controls.Add(Type:=msoControlDropdown)
    ' Add a tag so macro can find it
    cbctl.Tag = "TemplateList"
    cbctl.Visible = True
    cbctl.Caption = "ListCaption"
    ' Set list properties of the list box
    'testing adding to the list in a loop
    Dim intRow As Integer
    intRow = 1
    Do Until IsEmpty(Workbooks("PERSONAL.XLS").Worksheets("sheet1").Range(Cells(intRow, 1)))
    With cbctl
    .AddItem Workbooks("PERSONAL.XLS").Worksheets("sheet1").Range(Cells(intRow, 1)), intRow
    intRow = intRow + 1
    End With
    Loop
    With cbctl
    .DropDownLines = 0
    .DropDownWidth = 75
    ' select nothing to start
    .ListIndex = 0
    End With
    ' Set macro to execute when an item
    ' is selected
    cbctl.OnAction = "ExampleListMacro"
    End Sub
    Sub ExampleListMacro()
    Dim cbctl As CommandBarControl
    'Find the list box control
    Set cbctl = CommandBars("Toolbar Example"). _
    FindControl(Tag:="TemplateList")
    If Not cbctl Is Nothing Then
    MsgBox "You selected " & cbctl.List(cbctl.ListIndex)
    End If
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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