Solved: Formatting Dynamic Toolbar

[Opv]

Now that I have a functional dynamic toolbar, I am wondering if the following script could be modified to make the toolbar buttons larger?


[VBA]
With .Controls.Add(Type:=msoControlButton)
.Caption = "Sort Database"
.FaceId = 928
.OnAction = "sortRows"
End With
[/VBA]

[slamet Harto]

[VBA]

With .Controls.Add(Type:=msoControlButton)
.Caption = "Sort Database"
.FaceId = 928
.OnAction = "sortRows"
.Width = 87

End With

[/VBA]

[Bob Phillips]

I don't think so, commandbars only seem to have one size of image. It can be 16x16 or 32x32, but it wil still be the same size.

Excel 2007 allows large or small buttons, but not previous versions.

[Opv]

[vba]

With .Controls.Add(Type:=msoControlButton)
.Caption = "Sort Database"
.FaceId = 928
.OnAction = "sortRows"
.Width = 87

End With

[/vba]

Thanks. That seems to just add space between the icons rather than actually enlarging the icons.

Opv

[Opv]

I don't think so, commandbars only seem to have one size of image. It can be 16x16 or 32x32, but it wil still be the same size.

Excel 2007 allows large or small buttons, but not previous versions.

I was afraid of that. Thanks.

Opv

[Opv]

What about this: Is it possible to force my dynamically created toolbar to be stacked vertically? (I know I can do this manually, but I am curious as to whether it can be done automatically via VBA.) Here is my existing code:

[vba]
Private Sub CreateToolbar()

With Application.CommandBars.Add(Name:=myToolBar, temporary:=True)

With .Controls.Add(Type:=msoControlButton)
.Caption = "Return Home"
.FaceId = 1016
.OnAction = "gotoHome"
.Width = myIconWidth
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Add New Terms"
.FaceId = 535
.OnAction = "gotoEnd"
.Width = myIconWidth
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Sort Database"
.FaceId = 928
.OnAction = "sortRows"
.Width = myIconWidth
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Remove Duplicates"
.FaceId = 536
.OnAction = "DeleteDuplicatesViaFilterSub"
.Width = myIconWidth
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Search Database"
.FaceId = 25
.OnAction = "showUserForm"
.Width = myIconWidth
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Show All Data"
.FaceId = 1375
.OnAction = "showAllData"
.Width = myIconWidth
End With


With .Controls.Add(Type:=msoControlButton)
.Caption = "Protect Data"
.FaceId = 330
.OnAction = "protectData"
.Width = myIconWidth
.Enabled = False
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Unprotect/Edit Data"
.FaceId = 340
.OnAction = "unprotectData"
.Width = myIconWidth
.Enabled = False
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "HELP"
.FaceId = 926
.OnAction = "showHelp"
.Width = myIconWidth
End With

.Position = msoBarFloating
.Visible = True
End With

End Sub

[/vba]

[Opv]

Well, I've solved (so to speak) that question by adjusting the .Height sufficiently to force the toolbar to be displayed vertically. Now, I need to know if there is a way to control where on the screen the toolbar is positioned each time it is created?

[Bob Phillips]

Try

[vba]

.Position = msoBarLeft
[/vba]

[Opv]

Try

[vba]

.Position = msoBarLeft
[/vba]

Thanks. I changed it to .Position = msoBarRight. That indeed forces the toolbar to the right but it anchors it to the right edge of the screen and forces it to be the full height of the screen, as well as loses the caption bar.

The following, however, seems to work:

[vba]
.Position = msoBarFloating
.Top = 250
.Left = 900
[/vba]
I had to play around with the settings to get the results I was after, but it seems to work OK.

Thanks for your help,

Opv

[Opv]

Well, I've run into another glitch.

[VBA]
Sub hideToolbars()

On Error Resume Next

With Application
.DisplayFullScreen = True
.CommandBars("Full Screen").Visible = False
.CommandBars("Worksheet Menu Bar").Enabled = False
.CommandBars("Toolbox").Enabled = True
.CommandBars("ToolBox").Visible = True
End With

ActiveWindow.DisplayWorkbookTabs = True

On Error GoTo 0

End Sub
[/VBA]

The above script is not displaying the workbook tabs. Is there anything in the code that is causing this in Excel 2000?

Opv

[Bob Phillips]

Move the On Error Goto 0 to above that line of code and see what happens.

[Opv]

Move the On Error Goto 0 to above that line of code and see what happens.

Thanks. I just tried that per your suggestion but it don't seem to make any difference.

There seems to be something to do with the following line:

[VBA]
.DisplayFullScreen = True
[/VBA]

When I comment that line out and manually hide all of the desired elements, I can retain the DisplayWorkbookTabs, but there is something going on when the workbook is in full-screen mode that seems to be totally disabling the workbook tabs.

In the event that I have to do the more encumbering work-around, is there any way to manually hide the Windows title bar without using the full-screen mode?

[Bob Phillips]

That line wasn't in the code that you showed us!

[Opv]

That line wasn't in the code that you showed us!

You lost me on that one. It's in the code I posted earlier. See Post #10