User-defined range is called fine in a Workbook sub, but not a module sub?

I'd like to preface that yes, I am new, but I am also not looking to dive super deep into this language. I'm just trying to get this one book setup for now... This is also my first post & day here, so hello!

I have a defined range in a "Form" sheet, and I even did the "option explicit" thing and set it as

Please Login or Register  to view this content.

Set to:

Please Login or Register  to view this content.

"notebx_label" and "Note" are cells I named.
I've called it in the "ThisWorkbook" section under a general "Workbook_Open()" public sub to set the text of the cells to white (to hide them without hiding the entire column) as such:

Please Login or Register  to view this content.

and it works fine!

But in a module on its own that I also have set to a Public sub, I called it the same way:

Please Login or Register  to view this content.

and it gives me an error for "1004 - method 'range' of object '_global' failed"

How is it different? It's called in the workbook and it works just fine! why doesn't it work in the module? I have everything set to public etc. in an attempt to get everything called properly.

Not sure how to insert the whole code, but I did pictures:

ExcelVBA1.png
ExcelVBA1.5_.png
ExcelVBA2.png

on your sub "AddNote"

call the cell name:

Please Login or Register  to view this content.

the cell is already a "range" so adding range.range is confusing excel.

Thank you for explaining why ! But now I'm getting a different error;

"Object variable or With block variable not set"

Make sure the name of the cell inside the brackets is exactly what is named.
Excel is looking for the object in the brackets, but it can't find it.

Yea, I pasted it exactly:

Please Login or Register  to view this content.

is it something to do with how I have it created?

The cell name "notebx_label" is I2 (i2), and "Note" is a merged cell of I3 thru I8.

Attachment 778478

Any chance you can upload your document here? Someone here said a picture is worth a thousand words, and a workbook is worth a thousand pictures.

VBA doesn't like merged cells; that could be a reason

the syntax for calling a named cell is :


Sheet#.[CELL NAME]

Sheet# is the absolute reference of a sheet and not the user-defined name.

I could diagnose the problem in a couple of minutes with the workbook.

Ok, I think I attached it.

I see the problem.

1st issue:

change Sheets("Form") ->worksheets("Form") or to ->Sheet4

2nd issue:

note_rng is a variable and not a cell reference (it's a variable referencing cells)

to change the font color to black in your other module, I would use this code (below) instead of the one we wrote earlier:

Please Login or Register  to view this content.

Originally Posted by Faru298

With this line I'm getting "Application-defined or object-defined error"

Please Login or Register  to view this content.

I have it exactly the same as you typed it, inside the module.

EDIT: Sorry, I posted this before I saw the one you added about this error, thank you!

I was scratching my head for a solid 20 minutes trying to figure out the error . I'm glad we got your issue resolved thx for the rep!

Thank you so much! All errors are clear and everything is working as I wanted! Amazing :D

A week ago I had NO idea Excel could be setup to do things like this, as a programming nerd this is so cool.

many thanks!