Command Button problems

[Ed Lisay]

Richard,

Sounds like you want the code to run based on the
Worksheet that is currently active.
I do this all the time. Try this:
1. Take the code out of the Worksheet and put it in a
Module.
T
2. hen add this at the beginning:
mySheetName = ActiveCell.Worksheet.Name

3. Then find all the places in the existing code where
the name of a worksheet is hardcoded in and replace then
with:
mySheetName

4. Then from each command button click event, just call
the code.

Please let me know if you have any questions,
Ed

 

[Richard]

Hello Ed,
Thanks for your reply,
Steve also gave me a reply and between the replies, I can
sort of see what needs to be done but I haven't got the
understanding to put the advice into practice. Sorry if I
ask some dumb questions.

Yes, I want to sit in the All sheet and run a macro
created in the Update sheet.

When you say take the code out of the worksheet and put it
in a module. I've got the same code for the update button
sitting in both worksheets. But it's called Sub
CommandButton1_Click(). Do you mean make a new macro
called say Test() and copy the code into it. Which
spreadsheet do I create the new macro in, the All s/s or
the Update s/s. So do I then have a macro called Test()
and a CommandButton called CommandButton1_Click() and
which sheets do they live in. Can you see how I'm confused.

Then if above is correct i.e new Macro called Test() do I
put the line MySheetName = ActiveCell.Worksheet.Name at
the beginning. Is then MySheetName called Update or All
and what is the ActiveCell and also the Worksheet.Name.
What is the syntax for this.

In my existing code, I have no hardcoded references to to
the worksheet only a With activesheet.Querytables
statement at the commencement of the code.

My simple mind says to create a new macro in All s/s.
Leave the CommandButton1_Click() in the Update s/s and in
the new macro in All, call up the CommandButton1_Click()
macro in Update.
Does this make any sense to you because I think I'm going
round in circles. It's my first encounter with VB and
struggling a bit. Use'd to just creating and recording
macro's and fine tuning them in V7.

Could you clarify what you are advising so I can digest it
and understand it.

Appreciate any help you can give.

Regards,
Richard