Method or data member not found

If Overview is the sheet's name then you should say:

Please Login or Register  to view this content.

If you are already in the sheet you can leave that part and just start with Cells

try using Sheets before sheet name
Sheets("Overview")

That works perfectly fine in my excel.

Shot in the dark, is 'overview' specifically typed as a worksheet variable?

Originally Posted by YasserKhalil

try using Sheets before sheet name
Sheets("Overview")

overview.Cells(1, 1).Interior.ColorIndex = 50 is totally legit if overview is a worksheet variable, or if that is one of the worksheet's .codeName values.

You'll only want to do this if you are using the user-viewable tab name (worksheet.name) to access a sheet - which is generally something to avoid because that name may be altered.

I did some quick corrections but there's still a lot of issue with PrintBOM (as a worksheet?) and the references.

All sheets musht be either adress though a set variable of Sheet("sheetname")

I made some changes (see the modified code below)

Please Login or Register  to view this content.

It's not only that it's the whole worksheet decalration you're using.

I cannot write it out for you without testing. I do not have anything near to the ranges or worksheets you use, unless you want to attach a sample file with dummy data it will be easier

Ok, I think I can help.

@Rob K - code from Post#6.


Basically, I think you need to alter your thinking on what a Public Variable would do here. Using a Module-level declaration like 'Public Rework As Range' does not get you around the need to "SET" the variable in the button click macro.

Overview.Rework does not make sense because Rework is not a method or data member of object Overview. Once you have SET rework (with a line like Set Rework = Overview.Range("JobID")) then you can just access it in the code.

(but read my comment below this block of code)

Please Login or Register  to view this content.

BUT! There is an easier way to avoid the need for SETting a variable altogether. Since JobID appears to be a named formula identifying a range (aka named range), you can just go right to it.


IF the SCOPE of JobID is Workbook (in the name manger), then you can use that name in your VBA with brackets. [JobID].Cells(1, Int(JobNumber)).Interior.ColorIndex = 50 is a perfectly legitimate statement.

IF the SCOPE is NOT workbook (then it's a specific sheet name, I'll guess "overview" for the name) then you just need to preface the [JobID].cells(... call with the worksheet in one of three ways:

1) (best) Just use the worksheet codeName: Overview.[JobID].cells(... etc.

2)(next best) Use an worksheet variable to identify the sheet (but that variable will need to first have been declared and set...):

3)(worst) Use the worksheet name (different from codename) - this is the 'tab' name the user sees. THIS IS A BAD IDEA because you can't 100% rely on that name not changing.

worksheets("Overview").[JobID].cells(... etc.


Well, I guess there is a 4: Sheets(1).[JobID].cells(.... That's the worst-worst of all because it depends on the order of the sheets in the users view, which might be changed also.

Rule of thumb when coding and writing code
- Always check if a vraiable, range or sheen exist
- protect the worksheets so that users cannot change them but let the vba code acces it without problem
- etc. etc.

I don't think you're listening to any of us

Please Login or Register  to view this content.

[QUOTE=Rob K;4140569]I can change the colour of this cell if this line is in the main macro, but I can't get it to work elsewhere.[QUOTE]

I would guess something is out of scope, but can you post your code with the new alterations?