Moving between Worksheets

[Jim Berglund]

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

 

[mudraker]

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

 

[Jim Berglund]

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...

 

[Bob Phillips]

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)

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...

 

[Tom Ogilvy]

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

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)

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

 

[Jim Berglund]

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