Excel: refering to worksheets 2

[gusset]

i have a strange problem. my code does things with various worksheets in this way:

worksheets("shtOne&quot.DoStuff
worksheets("shtTwo&quot.DoStuff

i find that whatever stuff should be done to shtTwo is being done to shtOne unless i activate shtTwo first. activating it every time i want to do stuff with it is going to be resource-intensive.

is this a bug or am i going about things in the wrong way, please?

thanks

gusset

 

[SkipVought]

gusset,

If your code has a specific worksheet as the object of a method, then that method is applied to that worksheet alone.

Please post the SPECIFIC CODE that has shtOne as the object that is occurring on shtTwo.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[gusset]

there's too much code to paste; i'll give an explanation and a code sample.

on opening the workbook, which has two worksheets ("notes" and "statement&quot, a userform collects data and then starts code which does stuff (inserting data, formulae and formatting) with both sheets.

the closing of the userform runs code in a module (a module i created myself, not in the default module called Sheet1(statement) under Microsoft Excel Objects in the project menu, but under Modules.

this module does stuff such as

With Worksheets("Notes&quot
'this is a new group. insert row, give it heading
strAct = Cells(iRow, 12)
Rows(iRow).Insert: iRow = iRow + 1
intGroupIndex = iRow
intFeRateGroupIndex = iRow
With .Cells(iRow - 1, 1)
.Value = strCtAct
.Font.Bold = True

etc etc

then the code runs some more code, in another module i created myself, like this:

With Worksheets("Statement&quot.PageSetup
.LeftMargin = Application.InchesToPoints(0.4)
.RightMargin = Application.InchesToPoints(0.4)
End With
With Worksheets("Statement&quot
.Columns("A:A&quot.ColumnWidth = 30
.Columns("B:B&quot.ColumnWidth = 9.75
etc etc

the formatting that should be applied to the sheet called Statement is in fact applied to Notes.

it's crazy! i had this problem last year and worked around it, assuming it was a bug.

i tried to replicate the fault in a new workbook just now, and it doesn't manifest itself. i'm quite confused and suspect that my understanding of something or other is to blame. but if so, others must have had the same misunderstanding before, because as you see, i am referring to the sheets in the correct manner.

ps i don't understand the difference between the code modules in Microsoft Excel Objects and the modules in Modules in the project view. if you could explain that, i'd be grateful.

thanks for taking the time to answer my question.

gusset

 

[gusset]

ah, i worked out the problem. i wasn't referencing the sheet properly. i had forgotten to put dots in my code to indicate the proper object within a With statement

thanks for your help

if anyone knows the answer to my ps, please let me know

thanks

gusset

 

[SkipVought]

Hi,

Glad you discovered the answer.

A sheet object or workbook object can have code in them.

However you cannot refer to another sheet object from a sheet object. Hence, the module. A module can also be exported -- cant do with a sheet or workbook object



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi gusset,

Glad you solved your problem; it was hard to see anything wrong in your posted code.

As for the difference between code in Sheets and code in Modules: loosely, code in Sheets is related specifically to the particular sheet whilst code in Modules is more general. For example, code related to an Event on a sheet would go in that sheet. A Function for use in cells on any sheet would go in a Module.

Skip is not quite correct in one fact, however. It IS possible to reference code in one sheet from code in another sheet, although it would be quite unusual to do it.


Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Bowers74]

Hi gusset,

I just thought that you might also be interested in taking a look at my FAQ:

faq707-4090 - Refer to Worksheets more effecively in a Procedure

It might help you in the future. I was reminded of it when I read the title of you post. Enjoy!

Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[gusset]

great faq! i got the same tip from wrox's Excel 2000 VBA - they must have read your faq too!

gusset