Richard,

Sounds like the code is in the sheet "Update". When you have an Option
Button it has an associated on-click event code in the sheet module. You
can't get around that.

What you can do is have the click event code run a macro in a standard
module.

Private Sub CommandButton1_Click()
Run mymacro
End Sub

Or copy the code from behind the "Update" sheet and copy it to the module
for the "All" sheet.

--
sb
"Richard" wrote in message
...
Hello,
I have a workbook called Allotment which contains two
spreadsheets called All and Update.

Update has two CommandButtons called OptionA and OptionB.
In Update both buttons execute the code without a problem.
I wish to be able to place both these buttons on sheet All
and run then from there.

This is what I have done.

Opened control Toolbox in sheet Update and clicked on the
OptionA button and selected copy. Then over to the All
sheet and clicked paste. Gone back to Update and copied
code over to the CommandButton in the All sheet.

When I click the command button in the All sheet, the
button is executing the code applicable to the Update
sheet within the All sheet. The first lines of code for
the Conmmand button delete cells in the Update sheet,
which is how it should, but when run from the All sheet it
is deleting basically all of my sheet All, just leaving
some remnants of macro buttons etc.

What am I doing wrong. How can I drive the Update sheet
alone from the All sheet where I have copied the buttons
and code to. In other words the commands are executing on
the wrong sheet.

Any help, please!
Thanks.

Hello Steve,
Thanks for your reply,

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

Sorry to be a nuisance but I'm totally confused about
this. Don't fully understand Excel97 as previously used v7
for w95 and lot's of things are different. May need
elementary instructions.

I thought I already copied over the code. On both sheets I
have the OptionA button and when I view the code from
either sheet it is the same, called Sub CommandButton_Click
().

What do you mean by run a macro in a standard module? I
thought my code was in a standard module. Is a click event
code a special type of macro? Also in my Update
CommandButton1_Click() sheet, the first word is Private
and this is commented out but in the All sheet it is
uncommented. Is this correct?

Do you mean to do this. Create a macro (not a Command
button) in the All sheet then copy the code from Update
and insert it in the macro created. I don't understand
this, as the code in Update is called Private Sub
CommandButton1_Click and you suggested to run Mymacro from
within a macro with the same name. I'm confused using the
new VB section where I see all the modules and sheets and
code cascading. Having trouble seeing what is linked to
what.

I really need help with this. Could you possibly be more
explicit and set out what I need to do in steps. It may
sink in if you could assist me this way. I'm finding this
very frustrating what with trying to do it and learning on
the fly. All the current macro's and command buttons work
ok but I need to flick between the sheets to do specific
tasks. Just want to do all tasks from the All sheet.

Thanks and regards,
Richard

-----Original Message-----
Richard,

Sounds like the code is in the sheet "Update". When you
have an Option
Button it has an associated on-click event code in the
sheet module. You
can't get around that.

What you can do is have the click event code run a macro
in a standard
module.

Private Sub CommandButton1_Click()
Run mymacro
End Sub

Or copy the code from behind the "Update" sheet and copy
it to the module
for the "All" sheet.

--
sb
"Richard" wrote in message
...
Hello,
I have a workbook called Allotment which contains two
spreadsheets called All and Update.

Update has two CommandButtons called OptionA and
OptionB.
In Update both buttons execute the code without a
problem.
I wish to be able to place both these buttons on sheet
All
and run then from there.

This is what I have done.

Opened control Toolbox in sheet Update and clicked on
the
OptionA button and selected copy. Then over to the All
sheet and clicked paste. Gone back to Update and copied
code over to the CommandButton in the All sheet.

When I click the command button in the All sheet, the
button is executing the code applicable to the Update
sheet within the All sheet. The first lines of code for
the Conmmand button delete cells in the Update sheet,
which is how it should, but when run from the All sheet
it
is deleting basically all of my sheet All, just leaving
some remnants of macro buttons etc.

What am I doing wrong. How can I drive the Update sheet
alone from the All sheet where I have copied the buttons
and code to. In other words the commands are executing
on
the wrong sheet.

Any help, please!
Thanks.


.

Hello Steve,
Appreciate your assistance.

Your reply has helped with some of the grey areas I had,
but I'm still perplexed and have not solved my problem.

I followed your instruction about controls. this is what I
did.

In the "All" spreadsheet.
Into Design Mode.
Opened Control Toolbox.
Selected the ControlTool (Rectangle) and positioned on the
sheet.
Double clicked which took me to a new CommandButton3 and
an empty Sub.
Copied the Code from the CommandButton on
spreadsheet "UpDate" into this Sub.
Went back to the "All" spreadsheet and clicked the button.
The CommandButton3 code ran and crashed at
Selection.QueryTable.Delete. Application-defined or Object-
defined error.
The data on my "All" sheet is then deleted.
Once again the macro is running in the wrong sheet.

Also, as I do these tests, I'm creating multiple sheets
Like sheet11, sheet 112, sheet113 etc. Why would this
happen.

I think I have followed the instructions to the letter,
but obviously I'm stuffing something up.

Can you see what I'm doing wrong.

Regards,
Richard

-----Original Message-----
Richard,

There is the sheet module:
Usually contains event macros like your button click.
Regular module:
Contains standard code (no event macros)
ThisWorkbook module:
Contains event macros for the entire workbook.
Most often used for open and close events but can be
used for
many more.

In the sheet modules and ThisWorkbook module there are 2
select boxes
at the top. The left is for "General" or "Worksheet"
(or "Workbook").
The right is the type of macro. Explore these and become
familiar with
them. When you add a button or other control tool to a
worksheet, your
selections will add these to the list.

When working with the controls you:
Go into Design Mode
Open the Control Toolbox
Select and drag the control to the worksheet
Double click the control which takes you to the sheet
module
Write code into the sub that has been started.
(you could change the type of sub by using the
selections mentioned
above)

The code for the controls can be anything. Either
complete code or
reference to a standard module macro.

Make sure you are writing code to the correct control by
using the above.
You can copy parts of code from any module to another.
So if you have
code behind one worksheet that you want to use in another
just copy what
you need and paste.

Just remember that controls on a sheet need code in that
sheet.

Post back if you need more.

--
sb
"Richard" wrote in message
...
Hello Steve,
Thanks for your reply,

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

Sorry to be a nuisance but I'm totally confused about
this. Don't fully understand Excel97 as previously used
v7
for w95 and lot's of things are different. May need
elementary instructions.

I thought I already copied over the code. On both
sheets I
have the OptionA button and when I view the code from
either sheet it is the same, called Sub
CommandButton_Click
().

What do you mean by run a macro in a standard module? I
thought my code was in a standard module. Is a click
event
code a special type of macro? Also in my Update
CommandButton1_Click() sheet, the first word is Private
and this is commented out but in the All sheet it is
uncommented. Is this correct?

Do you mean to do this. Create a macro (not a Command
button) in the All sheet then copy the code from Update
and insert it in the macro created. I don't understand
this, as the code in Update is called Private Sub
CommandButton1_Click and you suggested to run Mymacro
from
within a macro with the same name. I'm confused using
the
new VB section where I see all the modules and sheets
and
code cascading. Having trouble seeing what is linked to
what.

I really need help with this. Could you possibly be more
explicit and set out what I need to do in steps. It may
sink in if you could assist me this way. I'm finding
this
very frustrating what with trying to do it and learning
on
the fly. All the current macro's and command buttons
work
ok but I need to flick between the sheets to do specific
tasks. Just want to do all tasks from the All sheet.

Thanks and regards,
Richard

-----Original Message-----
Richard,

Sounds like the code is in the sheet "Update". When
you
have an Option
Button it has an associated on-click event code in the
sheet module. You
can't get around that.

What you can do is have the click event code run a
macro
in a standard
module.

Private Sub CommandButton1_Click()
Run mymacro
End Sub

Or copy the code from behind the "Update" sheet and
copy
it to the module
for the "All" sheet.

--
sb
"Richard" wrote in message
...
Hello,
I have a workbook called Allotment which contains two
spreadsheets called All and Update.

Update has two CommandButtons called OptionA and
OptionB.
In Update both buttons execute the code without a
problem.
I wish to be able to place both these buttons on
sheet
All
and run then from there.

This is what I have done.

Opened control Toolbox in sheet Update and clicked on
the
OptionA button and selected copy. Then over to the
All
sheet and clicked paste. Gone back to Update and
copied
code over to the CommandButton in the All sheet.

When I click the command button in the All sheet, the
button is executing the code applicable to the Update
sheet within the All sheet. The first lines of code
for
the Conmmand button delete cells in the Update sheet,
which is how it should, but when run from the All
sheet
it
is deleting basically all of my sheet All, just
leaving
some remnants of macro buttons etc.

What am I doing wrong. How can I drive the Update
sheet
alone from the All sheet where I have copied the
buttons
and code to. In other words the commands are
executing
on
the wrong sheet.

Any help, please!
Thanks.


.



.

Richard,

You'll need to post your code. (Just reply and copy/paste your code into
the reply)

The CommandButton3 code ran and crashed at
Selection.QueryTable.Delete. Application-defined or Object-
defined error.
Usually means that Excel doesn't recognized an object or name.

Suggest you have Option Explicit at the top of all your modules and than
compile your code. Excel will point to the problem areas (maybe) and give
some sort of explanation (not always the clearest).

Post back...
--
sb
"Richard" wrote in message
...
Hello Steve,
Appreciate your assistance.

Your reply has helped with some of the grey areas I had,
but I'm still perplexed and have not solved my problem.

I followed your instruction about controls. this is what I
did.

In the "All" spreadsheet.
Into Design Mode.
Opened Control Toolbox.
Selected the ControlTool (Rectangle) and positioned on the
sheet.
Double clicked which took me to a new CommandButton3 and
an empty Sub.
Copied the Code from the CommandButton on
spreadsheet "UpDate" into this Sub.
Went back to the "All" spreadsheet and clicked the button.
The CommandButton3 code ran and crashed at
Selection.QueryTable.Delete. Application-defined or Object-
defined error.
The data on my "All" sheet is then deleted.
Once again the macro is running in the wrong sheet.

Also, as I do these tests, I'm creating multiple sheets
Like sheet11, sheet 112, sheet113 etc. Why would this
happen.

I think I have followed the instructions to the letter,
but obviously I'm stuffing something up.

Can you see what I'm doing wrong.

Regards,
Richard

-----Original Message-----
Richard,

There is the sheet module:
Usually contains event macros like your button click.
Regular module:
Contains standard code (no event macros)
ThisWorkbook module:
Contains event macros for the entire workbook.
Most often used for open and close events but can be
used for
many more.

In the sheet modules and ThisWorkbook module there are 2
select boxes
at the top. The left is for "General" or "Worksheet"
(or "Workbook").
The right is the type of macro. Explore these and become
familiar with
them. When you add a button or other control tool to a
worksheet, your
selections will add these to the list.

When working with the controls you:
Go into Design Mode
Open the Control Toolbox
Select and drag the control to the worksheet
Double click the control which takes you to the sheet
module
Write code into the sub that has been started.
(you could change the type of sub by using the
selections mentioned
above)

The code for the controls can be anything. Either
complete code or
reference to a standard module macro.

Make sure you are writing code to the correct control by
using the above.
You can copy parts of code from any module to another.
So if you have
code behind one worksheet that you want to use in another
just copy what
you need and paste.

Just remember that controls on a sheet need code in that
sheet.

Post back if you need more.

--
sb
"Richard" wrote in message
...
Hello Steve,
Thanks for your reply,

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

Sorry to be a nuisance but I'm totally confused about
this. Don't fully understand Excel97 as previously used
v7
for w95 and lot's of things are different. May need
elementary instructions.

I thought I already copied over the code. On both
sheets I
have the OptionA button and when I view the code from
either sheet it is the same, called Sub
CommandButton_Click
().

What do you mean by run a macro in a standard module? I
thought my code was in a standard module. Is a click
event
code a special type of macro? Also in my Update
CommandButton1_Click() sheet, the first word is Private
and this is commented out but in the All sheet it is
uncommented. Is this correct?

Do you mean to do this. Create a macro (not a Command
button) in the All sheet then copy the code from Update
and insert it in the macro created. I don't understand
this, as the code in Update is called Private Sub
CommandButton1_Click and you suggested to run Mymacro
from
within a macro with the same name. I'm confused using
the
new VB section where I see all the modules and sheets
and
code cascading. Having trouble seeing what is linked to
what.

I really need help with this. Could you possibly be more
explicit and set out what I need to do in steps. It may
sink in if you could assist me this way. I'm finding
this
very frustrating what with trying to do it and learning
on
the fly. All the current macro's and command buttons
work
ok but I need to flick between the sheets to do specific
tasks. Just want to do all tasks from the All sheet.

Thanks and regards,
Richard

-----Original Message-----
Richard,

Sounds like the code is in the sheet "Update". When
you
have an Option
Button it has an associated on-click event code in the
sheet module. You
can't get around that.

What you can do is have the click event code run a
macro
in a standard
module.

Private Sub CommandButton1_Click()
Run mymacro
End Sub

Or copy the code from behind the "Update" sheet and
copy
it to the module
for the "All" sheet.

--
sb
"Richard" wrote in message
...
Hello,
I have a workbook called Allotment which contains two
spreadsheets called All and Update.

Update has two CommandButtons called OptionA and
OptionB.
In Update both buttons execute the code without a
problem.
I wish to be able to place both these buttons on
sheet
All
and run then from there.

This is what I have done.

Opened control Toolbox in sheet Update and clicked on
the
OptionA button and selected copy. Then over to the
All
sheet and clicked paste. Gone back to Update and
copied
code over to the CommandButton in the All sheet.

When I click the command button in the All sheet, the
button is executing the code applicable to the Update
sheet within the All sheet. The first lines of code
for
the Conmmand button delete cells in the Update sheet,
which is how it should, but when run from the All
sheet
it
is deleting basically all of my sheet All, just
leaving
some remnants of macro buttons etc.

What am I doing wrong. How can I drive the Update
sheet
alone from the All sheet where I have copied the
buttons
and code to. In other words the commands are
executing
on
the wrong sheet.

Any help, please!
Thanks.


.



.