Floating Vertical Menu bar....Please help

[shashi1515]

The following code will make a floating horizontal menu bar, but how can
I make it a vertical menu bar? What changes should I make in the code?
Please advice.

Thanks in advance,
Shashi


Option Explicit

Const ToolBarName As String = "User Options"

'This code runs whenever the workbook is open
Sub Workbook_Open()

Call CreateMenubar

End Sub

'This code runs before the workbook is closed
Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox "This code ran at Excel close!"
Call RemoveMenubar

End Sub


'This code removes the floating tool bar before closing
Sub RemoveMenubar()

On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0

End Sub

'This code creats the floating tool bar
Sub CreateMenubar()

Dim iCtr As Long
Dim temp As Long

Dim MacNames As Variant
Dim CapNamess As Variant
Dim TipText As Variant

Call RemoveMenubar

MacNames = Array("aaa", "ab", _
"bbb")

CapNamess = Array("AAA Caption", "ab caption", _
"BBB Caption")

TipText = Array("AAA tip", "AB tip", _
"BBB tip")

With Application.CommandBars.Add
..Name = ToolBarName
..Left = 950
..Top = 100
..Width = 10000
..Protection = msoBarNoMove
..Visible = True
..Position = msoBarFloating



For iCtr = LBound(MacNames) To UBound(MacNames)

With .Controls.Add(Type:=msoControlButton)
..BeginGroup = True
..OnAction = "'" & ThisWorkbook.Name & "'!" &
MacNames(iCtr)
..Caption = CapNamess(iCtr)
..Style = msoButtonIconAndCaption
..FaceId = 71 + iCtr
..TooltipText = TipText(iCtr)


End With

Next iCtr
End With
End Sub

 

[Leith Ross]

Hello Shashi1515,

You need to reduce the width of the toolbar when you create it. The
width now is equal to 10000. Set it to a little larger than a single
buttons width, about 22. This should give a column of single buttons in
a vertical menu.

Sincerely.
Leith Ross

 

[shashi1515]

Leith,

I tried with 22, it didn't work. The result is same horizontal bar. I
even tried with other numbers like 2, 5, 10 etc. None of them made it
horizontal. :-(

Thanks,
Shashi

 

[Jim Cone]

Shashi,
Set the width dimension after the buttons are added.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"shashi1515" wrote in message
The following code will make a floating horizontal menu bar, but how can
I make it a vertical menu bar? What changes should I make in the code?
Please advice.
Thanks in advance,
Shashi

 

[shashi1515]

Thanks Jim....it worked........Thanks a lot.

~ Shash