Remove blank sheet from custom .xla Add in....

[Mr. Smith]

Hi.
I have some macros which I distribute as a .xla file. It works allright, but
when I run macros from the .xla Add in, it keeps opening a blank sheet from
the orignial .xls file from which the .xla is based on.

How can I hide/delete this sheet once and for all? Excel won't let me have a
..xls file without atleast one sheet...... Can I delete the sheet directly
from the .xla file?

Kind regards

Mr. Smith

 

[Dave Peterson]

You sure that the thing that invokes the macro isn't pointing to the original
..xls workbook and excel knows it has to open that workbook to run the macro.

If that's possible, ...

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Mr. Smith]

Thanks Dave
Bellow is the code on the "This Workbook" item in the VBAprocject in the
..xla file. The core issue is to include a permanent menu item to the useres
who want it. The menu item invokes a macro which formats output from a
"low-level" retrieve routine from our economy system. The macro checks that
the current content of the Excel worksheet is from the economy system and
fit for the formating macro. There's not a problem (yet) that the menu item
is added/removed through Add ins...

Option Explicit
Dim cControl As CommandBarButton
Private Sub Workbook_AddinInstall()
On Error Resume Next 'Just in case
'Delete any existing menu item that may have been left.
Application.CommandBars("Worksheet Menu
Bar").Controls("AutoFormat").Delete
'Add the new menu item and Set a CommandBarButton Variable to it
Set cControl = Application.CommandBars("Worksheet Menu
Bar").Controls.Add
'Work with the Variable
With cControl
.Caption = "AutoFormat"
.Style = msoButtonCaption
.OnAction = "open_info"
'Macro stored in a Standard Module
End With
On Error GoTo 0
End Sub
Private Sub Workbook_AddinUninstall()

On Error Resume Next 'In case it has already gone.
Application.CommandBars("Worksheet Menu
Bar").Controls("AutoFormat").Delete
On Error GoTo 0
End Sub

I guess I'll have to check the code in the AutoFormat macro, if I at some
stage reffere to the orignial sheet....

I'll look into your recomended sites. Thanks.

Mr.
Smith

 

[Dave Peterson]

Maybe changing this line would help:

..OnAction = "open_info"
to
..OnAction = "'" & thisworkbook.name & "'!" & "open_info"