Moving between Worksheets
 

I have 2 worksheets, A, & B. I have ControlButtons 1,2,3&4 that work as
follows:

CB1 is located on A. It loads external data files into B
CB2 is located on B. It collates and massages the data in B and transfers a
copy of it into A
CB3 is located on A. It further analyzes the data
CB4 is located on A and creates a report

Everything above works perfectly.

I'd like to combine the button functions for 1, 2 & 3, but all the trials
I've run have generated 1004 errors. A previous question to the forum gave
me an answer that seems to work, but is really a lot of effort to implement,
and will be a bigger problem as I move forward with this application.

Can I have your comments on the following approaches:
1. Could I create a form that has all the buttons on it, that isn't
associated with the worksheets? (ie, in a module). If so, can it be
semi-transparent?
2. Can I eliminate the buttons and add the functions to to the menu?
3. Can I use an external module to call the function from CB2 and CB3 from
CB1?

Which is most likely to be robust and easy to implement?

Thanks,

Jim Berglund

Moving between Worksheets
 

Do you do anything to any of the sheets after CB1 has been run an
before you run CB2?

If the answer is no then you can have CB1's code call CB2's code and s
for CB3 and CB4


On what module sheets are your codes located?

Can you paste your code here to assist in answer your questions

--
Message posted from http://www.ExcelForum.com

Moving between Worksheets
 

Thanks for your comments. Here are the answers to your questions...

1. Each button is run independently of the others (but the order of
operation is always from 1 to 4). I don't do anything to the sheets until
the imported data is loaded and massaged, after which the user can add
simple filters (using drop-down lists) to reduce the size of the report.
2. I'll try this suggestion
3. The code is currently associated with the two worksheets. Nothing is in a
Module
4. The code is many pages long. Most of it is data handling, copying &
pasting. The trouble occurs when I try to select things from sheet 1 from
sheet 2. Although I can add a lot of defining terms to fix this, its not
very elegant, and I'm trying to make it as straightforward as possible,
since it's 'Version 1', and will be modified further, over time.

I'll let you know if I can make it work...


"mudraker " wrote in message
...
Do you do anything to any of the sheets after CB1 has been run and
before you run CB2?

If the answer is no then you can have CB1's code call CB2's code and so
for CB3 and CB4


On what module sheets are your codes located?

Can you paste your code here to assist in answer your questions?


---
Message posted from http://www.ExcelForum.com/

Moving between Worksheets
 

Jim,

Whilst it is difficult to comment without seeing anything, you should be
able to overcome the 1004s.

Maybe your best approach is to take the CBs out of the forms, and create a
toolbar with the buttons on.

What code causes the 1004?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim Berglund" wrote in message
news:tngUc.128357$gE.12049@pd7tw3no...
Thanks for your comments. Here are the answers to your questions...

1. Each button is run independently of the others (but the order of
operation is always from 1 to 4). I don't do anything to the sheets until
the imported data is loaded and massaged, after which the user can add
simple filters (using drop-down lists) to reduce the size of the report.
2. I'll try this suggestion
3. The code is currently associated with the two worksheets. Nothing is in
a
Module
4. The code is many pages long. Most of it is data handling, copying &
pasting. The trouble occurs when I try to select things from sheet 1 from
sheet 2. Although I can add a lot of defining terms to fix this, its not
very elegant, and I'm trying to make it as straightforward as possible,
since it's 'Version 1', and will be modified further, over time.

I'll let you know if I can make it work...


"mudraker " wrote in message
...
Do you do anything to any of the sheets after CB1 has been run and
before you run CB2?

If the answer is no then you can have CB1's code call CB2's code and so
for CB3 and CB4


On what module sheets are your codes located?

Can you paste your code here to assist in answer your questions?


---
Message posted from http://www.ExcelForum.com/

Moving between Worksheets
 

from the previous thread:

---------------------------
Without looking at all your code, it appears that your procedure may be
behind a
worksheet.

If that's true, then an unqualified range will refer to the sheet holding
the
code--not the activesheet.

This is different than the behavior you've seen in a general module.

That's why you'll want to fully qualify each range reference. And if find
it
much easier to use the with/end with structure than doing all that typing:

-------------------------------

Jim doesn't want to go through and qualify his references.

So moving the code to a general module and calling it from there with
appropriate code added to insure the correct sheet is activated for each
section of code, should allow all to be done from one button - either on a
sheet (where the event would call the code in the general module) or using a
commandbar button.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Jim,

Whilst it is difficult to comment without seeing anything, you should be
able to overcome the 1004s.

Maybe your best approach is to take the CBs out of the forms, and create a
toolbar with the buttons on.

What code causes the 1004?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim Berglund" wrote in message
news:tngUc.128357$gE.12049@pd7tw3no...
Thanks for your comments. Here are the answers to your questions...

1. Each button is run independently of the others (but the order of
operation is always from 1 to 4). I don't do anything to the sheets
until
the imported data is loaded and massaged, after which the user can add
simple filters (using drop-down lists) to reduce the size of the report.
2. I'll try this suggestion
3. The code is currently associated with the two worksheets. Nothing is
in
a
Module
4. The code is many pages long. Most of it is data handling, copying &
pasting. The trouble occurs when I try to select things from sheet 1
from
sheet 2. Although I can add a lot of defining terms to fix this, its not
very elegant, and I'm trying to make it as straightforward as possible,
since it's 'Version 1', and will be modified further, over time.

I'll let you know if I can make it work...


"mudraker " wrote in message
...
Do you do anything to any of the sheets after CB1 has been run and
before you run CB2?

If the answer is no then you can have CB1's code call CB2's code and
so
for CB3 and CB4


On what module sheets are your codes located?

Can you paste your code here to assist in answer your questions?


---
Message posted from http://www.ExcelForum.com/

Moving between Worksheets
 

I took your recommendation, and put the problem code into a separate module,
calling it from the Main worksheet. It works perfectly! Thanks a lot
Jim
"mudraker " wrote in message
...
Do you do anything to any of the sheets after CB1 has been run and
before you run CB2?

If the answer is no then you can have CB1's code call CB2's code and so
for CB3 and CB4


On what module sheets are your codes located?

Can you paste your code here to assist in answer your questions?


---
Message posted from http://www.ExcelForum.com/