Custom Toolbars 4 Multiple Users

[thomas.szwed]

Hello there,

I have a couple of requirements with regards to multiple users opening my Excel solution. I would like to be able to -

- customise the toolbars they see when they open the file
- retain their default toolbars when they close the file

My excel solution is on a shared drive and used by multiple users on multiple PCs so their individual settings need to be retained when close my workbook. (i.e. i dont want them seeing my custom toolbars/buttons when they are just opening their own spreadsheets)

Does anybody have any code to enable these functions?

Many Thanks in advance

Tom

[Bob Phillips]

Dynamically assign the commandbars, like this



Option Explicit

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

[Zack Barresse]

I would suggest setting your Temporary value of the command bar to False. Then when you go to open your workbook (or whatever event hook you use to open the file and create your toolbar) check for its existence, if it doesn't exist, create it, if it does exist, do not. Or, store your settings somewhere in the file or maybe even look at SaveSetting().

[thomas.szwed]

Thanks for your replies. XLD - i am still a little unsure how to fit your suggested code into my project. I have created the my own toolbar called "Controls". It has various buttons on there called

- NewStarterActions
- StageNumberKey
- ArchiveRecords

How do these fit into your suggested code?

The 'workbook_open' and 'workbook_beforeclose' statements - do they need to be defined or will they literally work when i open or close my workbook?

Many Thanks

[Bob Phillips]

Replace the toolbar name, the captions and onactions with your values.

It will build when the workbook opens and destruct on close if you put the code in ThisWorkbook.

To input this code, right click on the Excel icon on the worksheet (or next to the File menu if you maximise your workbooks), select View Code from the menu, and paste the code

[Zack Barresse]

Why then, if you want to retain toolbar settings, would you destroy the toolbar on close? Why not just make changes as they come and only create once (unless a full-rebuild)?

[thomas.szwed]

Hello,

From doing abit of research.....it seems that each users version of my custom toolbar is different......i think this is because it is loading the one saved to the user's computer not the toolbar attached to the workbook itself......what is the correct code to load a toolbar that is saved and attached to a workbook....Thanks

[Bob Phillips]

My code loads the toolb ar on workbook open, so if all your users havee that workbook, they get the same toolbr.

[thomas.szwed]

Even if we are all accessing it through a shared drive on different computers?

Secondly so the code would just fit into my 'This workbook' as below, can u confirm...obviosuly with my own settings etc



Private Sub Workbook_Activate()

Option Explicit


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




And then for the workbook close, what would i put in for that??

THanks

[Bob Phillips]

I would use the Workbook Open event not ACtivate



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

[thomas.szwed]

Do you have a key for the Face IDs button numbers? Im not sure which is which.......having to use trial and error....Thanks

[Bob Phillips]

I use this code




Private Sub ShowFaceIDs()
Dim NewToolbar As CommandBar
Dim NewButton As CommandBarButton
Dim i As Long, IDStart As Long, IDStop As Long

' Delete existing FaceIds toolbar if it exists
On Error Resume Next
Application.CommandBars("FaceIds").Delete
On Error GoTo 0

' Add an empty toolbar
Set NewToolbar = Application.CommandBars.Add _
(Name:="FaceIds", temporary:=True)
NewToolbar.Visible = True

' Change the following values to see different FaceIDs
IDStart = 501
IDStop = IDStart + 499

For i = IDStart To IDStop
Set NewButton = NewToolbar.Controls.Add _
(Type:=msoControlButton, ID:=2950)
NewButton.FaceId = i
NewButton.Caption = "FaceID = " & i
Next i
NewToolbar.Width = 600
End Sub


and agjust IDStart

[Jan Karel Pieterse]

I'd suggest to read this chapter about tolbars and menu's in Excel, it might clear up some of the problems you see:

www.jkp-ads.com/articles/distributemacro04.htm (http://www.jkp-ads.com/articles/distributemacro04.htm)

[thomas.szwed]

Really helpful thnaks

[Zack Barresse]

Maybe I'm confused. Why would you create a toolbar, that you do not need to adjust, do not need to delete, and do not want to change, and not set its temporary property to false and not check for existence before deleting? If a programmatically added toolbar is moved, when deleted and reset (recreated) the position will be reset to the default 'next in line' position, quite the PITA if you are trying to keep its position constant through application open/close.

Edit: Or nobody wants to hear it.. ;) Either way, good luck.