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

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

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" wrote:
>
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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, ...'l
>
> 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" wrote:
>>
>> 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

Maybe changing this line would help:

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



"Mr. Smith" wrote:
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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, ...'l
> >
> > 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" wrote:
> >>
> >> 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

--

Dave Peterson