Update Custom Toolbar Buttons
 

I have a toolbar that is created every time I open Excel and removed when I
exit Excel. I was hoping that someone could help me out with code for adding
the toolbar only if it's not already there (so that if I move it around it
will be in the same spot as the last time I used it), but will always update
the two buttons I have to make sure they are pulling procedures from the most
recent .xla file.

Maybe this will help: I am still refining the procedures the buttons call
out, but I have already shared this add-in with my coworkers. In order to
make it easier on them (the not-so-tech-savvy), I refine it and save it with
the same name, then send out the revised version so all they have to do is
re-save it and it will replace their old add-in.

Thanks in advance!

Update Custom Toolbar Buttons
 

You should always create a new toolbar with code when your our add-in opens
and delete it when it closes (and make it "temporary" so it's gone even with
a crash). That way you and your users always have the latest and greatest.

That leaves the issue of maintaining its position between uses and that you
do with registry entries. When you start up get the last position from the
registry (if it exists) and when you close save the current position.

Sub SaveToolbar_Pos() ''Call from Auto_Close/Workbook_BeforeClose
On Error GoTo ExitThis ''In case toolbar was deleted
With CommandBars("MyToolbarName")
SaveSetting "MyApp", "MyToolbar", "TBPos", .Position
SaveSetting "MyApp", "MyToolbar", "TBTop", .Top
SaveSetting "MyApp", "MyToolbar", "TBLeft", .Left
SaveSetting "MyApp", "MyToolbar", "TBWidth", .Width
SaveSetting "MyApp", "MyToolbar", "TBHeight", .Height
End With
ExitThis:
End Sub

Sub SetToobar_Pos() ''Call after creating toolbar
Dim Pos As Integer
With CommandBars("MyToolbarName")
Pos = GetSetting("MyApp", "MyToolbar", "TBPos", -1) ''Last item is
"default"
If Pos < -1 Then ''If -1 there is no position in registry yet
.Position = Pos
.Top = GetSetting("MyApp", "MyToolbar", "TBTop", 0)
.Left = GetSetting("MyApp", "MyToolbar", "TBLeft", 0)
If Pos = 4 Then ''Floating toolbar - Try to preserve shape
.Width = GetSetting("MyApp", "MyToolbar", "TBWidth", 2)
''Default of 2 makes this tall an skinny
.Height = GetSetting("MyApp", "MyToolbar", "TBHeight", 5000)
End If
End If
End With
End Sub



--
Jim
"SLW612" wrote in message
...
I have a toolbar that is created every time I open Excel and removed when I
exit Excel. I was hoping that someone could help me out with code for
adding
the toolbar only if it's not already there (so that if I move it around it
will be in the same spot as the last time I used it), but will always
update
the two buttons I have to make sure they are pulling procedures from the
most
recent .xla file.

Maybe this will help: I am still refining the procedures the buttons call
out, but I have already shared this add-in with my coworkers. In order to
make it easier on them (the not-so-tech-savvy), I refine it and save it
with
the same name, then send out the revised version so all they have to do is
re-save it and it will replace their old add-in.

Thanks in advance!

Update Custom Toolbar Buttons
 

Thanks Jim, it's just what I needed!

"Jim Rech" wrote:

You should always create a new toolbar with code when your our add-in opens
and delete it when it closes (and make it "temporary" so it's gone even with
a crash). That way you and your users always have the latest and greatest.

That leaves the issue of maintaining its position between uses and that you
do with registry entries. When you start up get the last position from the
registry (if it exists) and when you close save the current position.

Sub SaveToolbar_Pos() ''Call from Auto_Close/Workbook_BeforeClose
On Error GoTo ExitThis ''In case toolbar was deleted
With CommandBars("MyToolbarName")
SaveSetting "MyApp", "MyToolbar", "TBPos", .Position
SaveSetting "MyApp", "MyToolbar", "TBTop", .Top
SaveSetting "MyApp", "MyToolbar", "TBLeft", .Left
SaveSetting "MyApp", "MyToolbar", "TBWidth", .Width
SaveSetting "MyApp", "MyToolbar", "TBHeight", .Height
End With
ExitThis:
End Sub

Sub SetToobar_Pos() ''Call after creating toolbar
Dim Pos As Integer
With CommandBars("MyToolbarName")
Pos = GetSetting("MyApp", "MyToolbar", "TBPos", -1) ''Last item is
"default"
If Pos < -1 Then ''If -1 there is no position in registry yet
.Position = Pos
.Top = GetSetting("MyApp", "MyToolbar", "TBTop", 0)
.Left = GetSetting("MyApp", "MyToolbar", "TBLeft", 0)
If Pos = 4 Then ''Floating toolbar - Try to preserve shape
.Width = GetSetting("MyApp", "MyToolbar", "TBWidth", 2)
''Default of 2 makes this tall an skinny
.Height = GetSetting("MyApp", "MyToolbar", "TBHeight", 5000)
End If
End If
End With
End Sub



--
Jim
"SLW612" wrote in message
...
I have a toolbar that is created every time I open Excel and removed when I
exit Excel. I was hoping that someone could help me out with code for
adding
the toolbar only if it's not already there (so that if I move it around it
will be in the same spot as the last time I used it), but will always
update
the two buttons I have to make sure they are pulling procedures from the
most
recent .xla file.

Maybe this will help: I am still refining the procedures the buttons call
out, but I have already shared this add-in with my coworkers. In order to
make it easier on them (the not-so-tech-savvy), I refine it and save it
with
the same name, then send out the revised version so all they have to do is
re-save it and it will replace their old add-in.

Thanks in advance!