Solved: Floating collection of commandbars

[U_Shrestha]

Hello,

My workbook has bunch of commandbuttons on each worksheets directly embedded in the worksheets and the buttons move (disappear) as I scroll the worksheet down. Is there a way I can group together all the buttons and then they will occupy the same space that I define in the worksheet all the time?I even tried changing the placement to "3" in the "Properties". Thanks.

[Bob Phillips]

Build a toolbar, for example

[vba]


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

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

Set oCB = Application.CommandBars.Add(Name:="myToolbar", temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "savenv"
.OnAction = "savenv"
.FaceId = 27
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "savemyprog"
.OnAction = "savemyprog"
.FaceId = 28
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "macro4"
.OnAction = "macro4"
.FaceId = 29
End With
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.Caption = "dater"
.OnAction = "dater"
.FaceId = 30
End With
.Visible = True
.Position = msoBarTop
End With

End Sub
[/vba]

To add this, go to the VB IDE (ALT-F11 from Excel), and in
the explorer pane, select your workbook. Then select the
ThisWorkbook object (it's in Microsoft Excel Objects which
might need expanding). Double-click the ThisWorkbook and
a code window will open up. Copy this code into there,
changing the caption and action to suit.

This is part of the workbook, and will only exist with the
workbook, but will be available to anyone who opens the
workbook.

[U_Shrestha]

Hello xld: What do I need to change in your code. What do I put in where it says 'Captions' and OnAction? Can you please explain. I have the macro "ShowAllData" for instance, how do I address it in there?

Private Sub CommandButton2_Click()
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub

Different groups of commandbuttons are required in each worksheet, although some buttons should be available in all worksheets.

[Bob Phillips]

That isn't a macro ShowAlldata, it is button code. You would need to move it to a macro called say ShowAll in a standard code module, and change one of the OnAction values to "ShowAll", with a caption of say "Show All data".

[U_Shrestha]

Hello xld: Here's what I did,

I put this code in 'ThisWorkbook'

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

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

Set oCB = Application.CommandBars.Add(Name:="myToolbar", temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "Show All Data"
.OnAction = "ShowAllData"
.FaceId = 27
End With

End Sub

I made a table in sheet1 and used autofilter. I inserted a new module as following:

Sub ShowAllData()
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub

When I opened the workbook (book1), I get error message saying: Compile error, expected End With, and it hightlights 'End Sub'.

Can you please help? Thanks.

[MikeO]

I didn't try your code, but at first glance you're missing an "End With" for your "With oCB". There could be more errors also.

[Bob Phillips]

[vba]


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("myToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl

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

Set oCB = Application.CommandBars.Add(Name:="myToolbar", temporary:=True)
With oCB
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "Show All Data"
.OnAction = "ShowAllData"
.FaceId = 27
End With
.Visible = True '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
End With '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

End Sub
[/vba]

[U_Shrestha]

Hello xld,

Thanks for providing me the solution. This is wonderful!