resetting custom buttons for macro

[Guest]

We have created an Excel program that uses macros to create packing slips and
box labels for the various customers we service. We have a master address
list (also an Excel sheet) that the program uses to populate the packing slip
and box label. The problem that we seem to be having is that when the packing
slips are saved as a different name and closed, then the original program
reopened, we have to reset the buttons to the macros. If we don't reset the
buttons and click on them, they will open the previous file that was saved.
Is there a way to amke the buttons run only the macros assigned to it and not
have them changed without rewiting the entire program?

 

[Guest]

mtarggart,

I've had the same exact problem with my major project, unfortunately
the short answer to your question is no. because the menu with the macro
button will be kept with the excel application itself you can not do this.
However there are three solutions i know of.

1. If the menu with the macro button is stored in the workbook, AND only one
copy of the workbook, (Regardless of the name of the book) will be open at
one time, then you can add an Auto_close macro telling the workbook to
automatically delete the menu with the macros on it.

2. If you have more than one copy of the book open at one time, you can
Create and delete the menu each time the workbook is
opened/closed/activated/deactivated etc.... but this involves recoding also.

3. you can code everything into an .xla add-in and make the macros point to
that add-in.(this is similar to coding them all into the personal workbook
excel loads on startup)

ben

 

[Guest]

Not clear exactly what your probelm is here.
I take it you have 2 files. One contains your address list, and the other
your packing slip info. Do you run the macro from the cutomer detail to
create the packing slips, or do your run the macro from the packing slip file.
If the latter, that is your problem. Run the macros from the customer file,
and save the packing slips as another name, then return to the master file,
and nothing there has changed.(?)

 

[Guest]

Kassie,

Thanks for the reply. I am sorry I did not make myself more clear. But, I
will run down what it is that happens. FYI - I am running Office 2003, but
this procedure also works for Office 2000 and XP.

First of all, yes there are two files. One is just an Excel sheet named
pckingslip.xls that the program uses as a template. This is saved to my
desktop. The second is a master record of all of our customers names and
addresses. We will call it addresses.xls. This file is on the network. It
must remain closed or there will be a script error when the program is ran.

When I open the pckingslip.xls file on my desktop, I see a generic version
of what our packing slip looks like. In the menu bar, I have three custom
buttoms. One named Packing Slips, one named Braille Packing Slips and one
named Box Labels. When I click on the Packing slip button, it runs a macro
that was coded in the VB editor. This program has a few fields that the user
must manually fill in. It also has a drop down list that links to the master
spreadsheet and contains all the names on our master address spreadsheet.
There is also an OK button located at the bottom of the program.

We choose the recipient of the package, enter the title of the job, the job
number, date ordered, dated shipping, number of boxes, and a description of
the contents of the box. When I hit the OK button, the program copies the
first template sheet and creates a new sheet at the end of the sheet tabs
and fills in all of the information that the user just enterd on the program
gui. It creates a packing slip for every box the the user entered.

When you click the Packing Slips button again, all of the information that
you had previously entered is still there. You choose another recipient and
change any of the information that you need to change like number of boxes
and hit the OK button again.

When all of the packing slips have been made, the user saves the workbook as
anything but original name.

Now, if this renamed workbook is closed and pckingslip.xls is opened again
showing only the template sheet, as soon as I click on the Packing SLips
button, it opens all of the previously made packing slips. To get this not to
happen, I need to open pckingslip.xls, click on the tools menu and click
customize. I then right clikc on each button and reset it to which macro I
want it to run.

In the macro name field, it has the location of the previously saved packing
slips.

My question was, is it possible to set the buttons to only use the macro
assigned to it. I thought maybe locking the buttons so they cannot be changed
either by the user accidentally or by the program.

Ben explained a few options, but it sounds like a pain. Which is what we
have with the thing from the beginning.

I hope this clears up some of the confusion. I sounds like a lot to do, but
it really much more simple than what we had to do before. With this program I
can now do in about 20-30 minutes what use to take me 3-5 hours to do.

i think I just might look into recoding the entire thing to use something
other than excel. I'll have to think about it more when I have more time.

Thanks for you help, though. I really appriciate it.

Mike