Command Button problems

  • Thread starter Thread starter Ed Lisay
  • Start date Start date
E

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
 
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
 
Back
Top