Help with Custom Tool Bar

  • Thread starter Stephen sjw_ost
  • Start date
S

Stephen sjw_ost

I need some help please. I am using the following to build a custom tool bar.
This code is entered in the ThisWorkbook in the VBA editor;

Sub SaveLoop()
Do
On Error Resume Next
ActiveWorkbook.Save
Loop Until ActiveWorkbook.Saved = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("pscnt").Delete
End Sub

Private Sub Workbook_Open()
'
Application.CommandBars.Add(Name:="pscnt").Visible = True
Application.CommandBars("pscnt").Controls.Add
Type:=msoControlSplitDropdown, _
ID:=128, Before:=1
Application.CommandBars("pscnt").Controls.Add
Type:=msoControlSplitDropdown, _
ID:=129, Before:=2
Application.CommandBars("pscnt").Controls.Add Type:=msoControlButton,
ID:= _
21, Before:=3
Application.CommandBars("pscnt").Controls.Add Type:=msoControlButton,
ID:= _
19, Before:=4
Application.CommandBars("pscnt").Controls.Add Type:=msoControlButton,
ID:= _
22, Before:=5
Application.CommandBars("pscnt").Controls.Add Type:=msoControlButton,
ID:= _
108, Before:=6
Application.CommandBars("pscnt").Controls.Add Type:=msoControlComboBox,
ID _
:=1733, Before:=7
Application.CommandBars("pscnt").Controls.Add Type:=msoControlButton,
ID:= _
2950, Before:=1
End Sub

I want to be able to assign the SaveLoop code to the custom button I have
included in the Workbook_Open part which builds the tool bar.

How can I assign the SaveLoop code or include it in the build process so
that when my users click the new "Save button", my SaveLoop code is invoked?
Also, How can I attach an image that is available in the custom tool bar
creation section?

The purpose of the SaveLoop is to ensure the file saves while in a shared
workbook state with multiple users saving at the same time. I found that it
helps to prevent runtime save errors like "This file is currently locked by
xxxx for saving". Instead of giving the error, the file just loops until it
is free to save. FYI, this has worked great and is why I want to incorporate
it to my custom tool bar.

Any help or direction is greatly appreciated.
 
S

Stephen sjw_ost

I just found this info submitted by Josh O and answered by Ron de Bruin.
I will give this a try.
****************************
Delete the button first in the code before you add it with code.
Something like this


Sub MenuBar_Item()
Call MenuBar_Item_Delete

With Application.CommandBars("Standard")
With .Controls.Add(Type:=msoControlButton, temporary:=True, before:=1)
.Style = msoButtonCaption
.Caption = "&Hi"
.TooltipText = "Hi again"
.OnAction = ThisWorkbook.Name & "!TestMacro1"
.Tag = "MyTag1"
End With

End With
End Sub

Sub MenuBar_Item_Delete()
On Error Resume Next
Application.CommandBars.FindControl(Tag:="MyTag1").Delete
On Error GoTo 0
End Sub
*************************
If anyone has any other suggestions, I would appreciate it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top