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


--
shashi1515
------------------------------------------------------------------------
shashi1515's Profile: http://www.excelforum.com/member.php...o&userid=31109
View this thread: http://www.excelforum.com/showthread...hreadid=507744

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=507744

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


--
shashi1515
------------------------------------------------------------------------
shashi1515's Profile: http://www.excelforum.com/member.php...o&userid=31109
View this thread: http://www.excelforum.com/showthread...hreadid=507744

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

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

~ Shash

--
shashi151
-----------------------------------------------------------------------
shashi1515's Profile: http://www.excelforum.com/member.php...fo&userid=3110
View this thread: http://www.excelforum.com/showthread.php?threadid=50774