Macros - Assign to specific spreadsheet only?

[Wapiti]

I'm getting confused.

I built two macros for a given spreadsheet and inserted them on a custom
toolbar.

When we copy the spreadsheet, giving it a new name, and then open the copy
and run the macros - excel opens a new spreadsheet with the name of the
original.

I'm guessing that the macros are assigned specifically to a particular
spreadsheet somehow? How do you suggest I work around this? I'd like to
copy the spreadsheet and have the macro work in the newly created
spreadsheet too. Even if it entails some minor coding changes, thats
fine - or is there a way to tell the macro to work whereever it is.

All the macros do is provide two different column sorts, one by part number
and the other by description.

Thanks!

 

[Don Guillett]

Why not just use a worksheet double_click event to sort by the column that
is clicked?

[CHECKSSORT].Sort Key1:=Cells(1, ActiveCell.Column), Order1:=xlAscending,
Orientation:=xlTopToBottom

 

[Wapiti]

Ok, did a little more playing/testing to find the following:

It appears to only be the macros when they are assigned to the toolbar. If
I run the macros directly, from the new document, it doesn't open the old
document.

If I run the macros as assigned to the toolbar however, it attempts to open
the old xls file. But what seems strange to me is that after it opens the
old xls file and then I close it - the macros work fine in the new file.
????

Make sense to you??

-m

 

[Wapiti]

Each macro is actually sorting on multiple columns - I was just explaining
what they do in simplistic terms to not muddy the waters, if you will.

Still confused as to why the toolbar macros feel they need to open the
original file where the macros previously lived, rather than the from within
the document where they currently reside.

Why not just use a worksheet double_click event to sort by the column that
is clicked?

[CHECKSSORT].Sort Key1:=Cells(1, ActiveCell.Column), Order1:=xlAscending,
Orientation:=xlTopToBottom

--
Don Guillett
SalesAid Software
(e-mail address removed)

I'm getting confused.

I built two macros for a given spreadsheet and inserted them on a custom
toolbar.

When we copy the spreadsheet, giving it a new name, and then open the copy
and run the macros - excel opens a new spreadsheet with the name of the
original.

I'm guessing that the macros are assigned specifically to a particular
spreadsheet somehow? How do you suggest I work around this? I'd like to
copy the spreadsheet and have the macro work in the newly created
spreadsheet too. Even if it entails some minor coding changes, thats
fine - or is there a way to tell the macro to work whereever it is.

All the macros do is provide two different column sorts, one by part number
and the other by description.

Thanks!

 

[Don Guillett]

Try putting in personal.xls and assign the toolbar to that.

 

[Bernie Deitrick]

Wapiti,

When you assign a macro to a commandbar button, Excel keeps track of that
workbook. Anytime you try to run the macro, Excel will open that workbook
if it isn't already open. The way around this is to have code in your
workbook that creates the commandbutton on the fly , using the workbook open
event, and deletes it when the workbook is closed, using the close event.

There are plenty of code examples: google the groups for

Deitrick createcommandbar

and you will find some of my examples.

HTH,
Bernie
MS Excel MVP

Each macro is actually sorting on multiple columns - I was just explaining
what they do in simplistic terms to not muddy the waters, if you will.

Still confused as to why the toolbar macros feel they need to open the
original file where the macros previously lived, rather than the from within
the document where they currently reside.

Why not just use a worksheet double_click event to sort by the column that
is clicked?

[CHECKSSORT].Sort Key1:=Cells(1, ActiveCell.Column), Order1:=xlAscending,
Orientation:=xlTopToBottom

--
Don Guillett
SalesAid Software
(e-mail address removed)

I'm getting confused.

I built two macros for a given spreadsheet and inserted them on a custom
toolbar.

When we copy the spreadsheet, giving it a new name, and then open the copy
and run the macros - excel opens a new spreadsheet with the name of the
original.

I'm guessing that the macros are assigned specifically to a particular
spreadsheet somehow? How do you suggest I work around this? I'd

like

to

 

[Wapiti]

Perfect Bernie, That sounds like what I need - I'll look up using the
workbook_open event. I'm a developer but never for Excel - finding this
quite interesting.

Thanks,

Mike

Wapiti,

When you assign a macro to a commandbar button, Excel keeps track of that
workbook. Anytime you try to run the macro, Excel will open that workbook
if it isn't already open. The way around this is to have code in your
workbook that creates the commandbutton on the fly , using the workbook open
event, and deletes it when the workbook is closed, using the close event.

There are plenty of code examples: google the groups for

Deitrick createcommandbar

and you will find some of my examples.

HTH,
Bernie
MS Excel MVP

Each macro is actually sorting on multiple columns - I was just explaining
what they do in simplistic terms to not muddy the waters, if you will.

Still confused as to why the toolbar macros feel they need to open the
original file where the macros previously lived, rather than the from within
the document where they currently reside.

Why not just use a worksheet double_click event to sort by the column that
is clicked?

[CHECKSSORT].Sort Key1:=Cells(1, ActiveCell.Column), Order1:=xlAscending,
Orientation:=xlTopToBottom

--
Don Guillett
SalesAid Software
(e-mail address removed)
I'm getting confused.

I built two macros for a given spreadsheet and inserted them on a custom
toolbar.

When we copy the spreadsheet, giving it a new name, and then open

the
copy

and run the macros - excel opens a new spreadsheet with the name of the
original.

I'm guessing that the macros are assigned specifically to a particular
spreadsheet somehow? How do you suggest I work around this? I'd

like

to
copy the spreadsheet and have the macro work in the newly created
spreadsheet too. Even if it entails some minor coding changes, thats
fine - or is there a way to tell the macro to work whereever it is.

All the macros do is provide two different column sorts, one by part
number
and the other by description.

Thanks!