Custom Floatable Toolbar Popup Menu

[Pflugs]

I know how to make custom toolbars in Excel and through VBA. Is it possible
to make popup menus within a toolbar that are floatable (similar to the font
and cell color pallettes)?

Thanks,
Pflugs

 

[Peter T]

Just for ideas -

Sub CustomBar()
Dim cbr As CommandBar
Dim cbt As CommandBarButton
On Error Resume Next
Application.CommandBars("TestBar").Delete
On Error GoTo 0

Set cbr = Application.CommandBars.Add("TestBar")

Set cbt = cbr.Controls.Add(1)

With cbt
.Style = msoButtonCaption
.Caption = "click for Pop-up"
.Visible = True
.OnAction = "myPopup"
End With

cbr.Position = msoBarFloating
cbr.Visible = True

End Sub

Sub myPopup()
Dim cbr As CommandBar
Dim cbt As CommandBarButton
On Error Resume Next
CommandBars("myPopup").Delete
On Error GoTo 0

Set cbr = Application.CommandBars.Add("myPopup", msoBarPopup, , True)

For i = 80 To 89
Set cbt = cbr.Controls.Add(1, , , , True)
With cbt
.Style = msoButtonIconAndCaption
.Caption = "code " & Chr(i - 15)
.FaceId = i
.Parameter = i - 79
.Visible = True
.OnAction = "myMacro"
End With
Next
On Error GoTo errH

cbr.ShowPopup

done:
cbr.Delete
Exit Sub
errH:
Resume done
End Sub

Sub MyMacro()
Dim cbt As CommandBarButton
Dim sParam As String

Set cbt = Application.CommandBars.ActionControl
sParam = cbt.Parameter
MsgBox "button " & sParam, , cbt.Caption

Select Case sParam
Case "1"
MsgBox "processing code " & sParam
'etc
End Select
End Sub

Regards,
Peter T

 

[Peter T]

Should include 'OnError Resume Next' just after 'done:'

On Error GoTo errH

cbr.ShowPopup

done:

OnError Resume Next ' >> add this line

cbr.Delete
Exit Sub
errH:
Resume done
End Sub

Peter T

 

[Pflugs]

That's really neat and definitely useful. Any idea on how to make that popup
menu moveable and remain on the screen when you click away?

 

[Pflugs]

Upon some futher testing, I found that the type of control I'm talking about
is msoControlSplitButtonPopup. I can create these as long as they have
predefined ID's. I can't figure out how to create them from scratch and add
my own controls to them. Anyone have any thoughts?

Thanks,
Pflugs

 

[Peter T]

I don't think there's any straightforward way to create this type of
control, or indeed various others.

Depending on what you want to achieve perhaps a cascading menu system (popup
controls rather than popup bars) or even add some more 'squarish'
commandbars.

Regards,
Peter T

 

[Pflugs]

Yes, I think those methods would work better. Thanks for the popup code.
Pflugs