Excel custom toolbar

[Guest]

Can I attach a custom toolbar to a workbook that I want to email to others?
I have macros assigned to the custom toolbar but others are not able to view
the toolbar when I send it to them.

 

[Anne Troy]

Build the custom toolbar using VBA. Here's an example in Word. You just need
to put yours in the workbook_Open event.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=14
**** Hope it helps! ****

~Dreamboat
Excel VBA Certification Coming Soon!
www.VBAExpress.com/training/
********************************

 

[Guest]

This is helping but I keep getting an error on this line
"CustomizationContext = ActiveDocument.AttachedTemplate"
I have changed ActiveDocument to ActiveWorkbook, ActiveSheet, and
ActiveWorksheet but I still recieve the error. Do you have a fix for this?

Thanks!

 

[Dave Peterson]

If you've attached a toolbar to a workbook, you'll want to read Jan Karel
Pieterse's notes:

http://google.com/[email protected]


But I like to build the toolbar on the fly when the workbook opens
(and destroy it when the workbook closes):

If that interests you, here's a post that may help:
http://groups.google.com/[email protected]

 

[Guest]

Dave,
I tried your suggestion(s) but I still cannot get the custom toolbar to open
and close with the specific workbook. Can you look at this and provide any
feedback? I would most certianly appreciate it. Here is my code for the
macro:

Private Sub Workbook_BeforeClose()
On Error Resume Next
Application.CommandBars("Assumptions and Other Options").Delete
End Sub
Sub HideResidential()
'
' HideResidential Macro
' Macro recorded 9/29/2004 by BMULLINS
'

'
Range("G1:S1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("H:K,M:Q").Select
Range("M1").Activate
Selection.EntireColumn.Hidden = True
Range("G1:S1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("G3").Select
End Sub
Sub ViewAssumptions()
'
' ViewAssumptions Macro
' Macro recorded 9/29/2004 by BMULLINS
'

'
Sheets("Assumptions").Visible = True
Sheets("Assumptions").Select
Range("A1").Select
End Sub
Sub HideAssumptions()
'
' HideAssumptions Macro
' Macro recorded 9/29/2004 by BMULLINS
'

'
ActiveWindow.SelectedSheets.Visible = False
Range("A1").Select
End Sub
Sub PrintAssumptions()
'
' PrintAssumptions Macro
' Macro recorded 9/29/2004 by BMULLINS
'

'
Sheets("Assumptions").Visible = True
Sheets("Assumptions").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Assumptions").Visible = False
End Sub

Thanks,
Billy

 

[Dave Peterson]

First, there was a small mistake in the _beforeclose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Assumptions and Other Options").Delete
End Sub

But you should have been yelled at when the workbook closed. If you weren't,
then maybe you didn't put the code in the correct spot. Make sure it's behind
the ThisWorkbook module.

All that other stuff would be in a general module.

After that change, it worked for me. (the toolbar appeared when I opened the
workbook and was deleted when I closed the workbook.)

Now the easy thing to check--are you sure you attached it correctly? Are you
sure you used the correct name (in code, too).

If you do View|toolbars, do you see your toolbar--maybe it's just not
visible????

if it's not visible, maybe adding this code behind the ThisWorkbook module will
help:


Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("assumptions and other options").Visible = True
If Err.Number <> 0 Then
MsgBox "error showing toolbar"
Err.Clear
End If
On Error GoTo 0
End Sub

=======
You may want to take one more look at creating the toolbar on the fly. It's
really pretty simple and a lot less prone to errors (well, I think).

 

[Guest]

Dave,
I am still having problems and my guess it is due to me not knowing how to
add a ThisWorkbook module. Everything to this point has been under General.

The toolbar is visible with I go to View|Toolbars|Assumptions and Other
Options.

The toolbar works great during my normal macro, however the toolbar just
does not appear and disappear on open and close.

I can manually open and close the toolbar as well as the people that I send
the file to but there are some people that will not completely understand how
to do that and that is the reason that I would like for the toolbar to
automatically open and close.

I still believe that the error is due to me not being able to creat a
ThisWorkbook module. If you would not mind explaining how that works, I am
sure I could get it to work on my end as well. Thanks for your time and
patience.

Billy

 

[Dave Peterson]

One way to get to the ThisWorkbook module (you don't add it).

Inside Excel with your workbook active.
rightclick on the excel icon to the left of the worksheet menubar
(file|edit|view|...)

Select View code
and you're there.

===
But since you've got the code in a General module, you can modify the procedure
names (and use the older auto macro names).

Private Sub Workbook_Open()
will become
Private Sub Auto_Open()


Private Sub Workbook_BeforeClose()
will become
Private Sub Auto_Close()

I think these two changes will be easier <bg>.

Save your workbook, close it, and reopen it to see if it worked.