Public variable and Runtime error 91: Object Variable or With block variable not set

**Frank Nunez** · 10-29-2020, 03:11 PM

Hi all, I have a userform with a textbox that I load from a standard module that I use to edit cell comments more easily than Excel's comment boxes. Everything works (ie. changing cells and the textbox updating to the new cell's comment, automatic storing of the textbox data to the previous cell's comment. What doesn't work is when a cell has no comment, and I add one to the textbox and then click on a different cell, I get thrown a Object variable or with block not set on the line bolded below.

Also, in a standard module elsewhere, I set the public variable PrevCellRange to the active cell upon load of the userform as follows:

Please Login or Register  to view this content.

Then in my worksheet module, I have the following main code, with public variables set atop the module:

Please Login or Register  to view this content.

Thanks all for your help again, this is causing me to pull my hair out. Frank

**6StringJazzer** · 10-29-2020, 04:10 PM

It's hard to diagnose this without having all your code. When you have public variables, you have to design very carefully to prevent situations that are begging to be bugs.

It would be helpful to have your entire file. See yellow banner at the top of the page

I don't see what your specific problem is but I recommend that you work directly with cells rather than the addresses of cells. This may not solve your problem but it may make it easier to diagnose. For example (untested)

Please Login or Register  to view this content.

**Frank Nunez** · 10-29-2020, 05:25 PM

Please Login or Register  to view this content.

This is the userForm init code from the "standard module" I mentioned above.

**6StringJazzer** · 10-29-2020, 06:23 PM

I think the problem you are having is that you are entering Worksheet_SelectionChange and NewCell has not been set. Then when you do this

Please Login or Register  to view this content.

followed by this

Please Login or Register  to view this content.

PrevCellRange is the null string and causes an error.

**Frank Nunez** · 10-29-2020, 06:40 PM

Sample.xlsx is attached.

**hrlngrv** · 10-29-2020, 07:02 PM

Your Worksheet_SelectionChange event handler assigns NewCell to PrevCellRange before assigning NewCell the ActiveCell's address. Are you CERTAIN NewCell is set when you assign it to PrevCellRange?

Wouldn't hurt to add the following statement.

Please Login or Register  to view this content.

**6StringJazzer** · 10-29-2020, 09:08 PM

Originally Posted by Frank Nunez

Sample.xlsx is attached.

You have attached a non-macro-enabled file--no code. You should attach a file with a .xlsm extension.

**Frank Nunez** · 10-29-2020, 10:11 PM

Sorry was working in a terrible rush, here's the macro enabled workbook! Thanks again for offering to take a look. To the point about prevCellRange not being set, my question is why isn't the line in Module 1 setting prevCellRange upon load of the userform? This is why I mentioned the Public variable problem in the subject, or am I missing something else?

Please Login or Register  to view this content.

Frank

**romperstomper** · 10-30-2020, 04:27 AM

It's a scope issue. Your variables are declared as public members of a class (the worksheet) so can only be accessed that way. (If you used Option Explicit you would have been alerted to non-declared variables.) Move these two lines to Module 1:

Please Login or Register  to view this content.

**Frank Nunez** · 10-30-2020, 10:41 AM

Hi Rorya,
I thought of the same thing, and tried moving those to public variables to Module 1, but I get the same error at the same line in the worksheet module code.
Did you by any chance try the .xlsm file on your end and have success?
Frank

**6StringJazzer** · 10-30-2020, 10:57 AM

Originally Posted by Frank Nunez

To the point about prevCellRange not being set, my question is why isn't the line in Module 1 setting prevCellRange upon load of the userform?

My point was that NewCell was not set. In the workbook you attached, you reversed the two lines of code compared to what you originally posted, and different than what I suggested above.

This is what I tried to explain above but here it is in more detail:

The only place where NewCell is set is in Sub Worksheet_SelectionChange
The first time you enter Sub Worksheet_SelectionChange NewCell has not been set, so it is Nothing
Then you set PrevCell to NewCell, so now PrevCell is Nothing
Any attempt to reference PrevCell will cause an error.

Further, you are referring to PrevCellRange.Comment. However, this will raise an error if the cell does not have a comment. I am a bit short on time at the moment but will look for a fix.

Originally Posted by rorya

It's a scope issue. Your variables are declared as public members of a class (the worksheet) so can only be accessed that way. (If you used Option Explicit you would have been alerted to non-declared variables.) Move these two lines to Module 1:

Please Login or Register  to view this content.

Moving the declaration would be one solution, however, a Public variable declared in a worksheet module has scope in all modules but it must be qualified to have visibility. In this case the [corrected] line of code in showFormCommentEditor

Please Login or Register  to view this content.

can be changed to

Please Login or Register  to view this content.

to resolve this.

However, that still does not solve the other problem I described above.

**romperstomper** · 10-30-2020, 11:13 AM

Originally Posted by 6StringJazzer

Further, you are referring to PrevCellRange.Comment. However, this will raise an error if the cell does not have a comment.

That is the key issue really, once the variable declarations have been moved.

**Frank Nunez** · 10-30-2020, 12:43 PM

Hi Rorya and SixStringJazzer, thanks for both of your insights!

It seems that the error comes up even if PrevCellRange is not empty.

In showFormCommentEditor I revised the code as follows:

Please Login or Register  to view this content.

The public variable scope issue is resolved also now as Module1 succesfully passes PrevCellRange to the Sheet1 module upon launch of the userform. Thank You.

In the Sheet1 Module (selection change), I realized NewCell was unnecessary so I removed it. Instead, I update PrevCellRange to the activecell further down in the code, after my other Selection Change code runs. MsgBox(PrevCellRange) demonstrates that as I change cells by selection, PrevCellRange is populated with the last cell successfully.

Everything works fine up until the same situation arises as before unfortunately - As soon as I populate the myUserFormTextBox with any data while A6 is selected (a cell with no existing comment), then change selection to another cell, the error 91 appears.

In this screenshot, you can see that while in A6, I entered the data into the textbox that you see, then after I click on A5, msgbox(PrevCellRange) shows that indeed it is not empty, but still error 19 appears.

error.jpg

Here is the updated code as text, without the unnecessary NewCell variable. The .xlsm is attached below.

Please Login or Register  to view this content.

**Frank Nunez** · 10-31-2020, 12:05 AM

RESOLVED:

I changed the evaluation of the cell's comment status (empty/not empty) from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

THANK YOU TO ALL WHO BROUGHT THEIR MINDS AND EXPERTISE TO THIS ERROR RESOLUTION! Frank

**6StringJazzer** · 10-31-2020, 12:04 PM

Glad you found a solution. I have an alternative but if you are happy then you might not even need to look at it.

**Frank Nunez** · 10-31-2020, 04:02 PM

Very cool 6StringJazzer, I wish I thought of a similar solution earlier using the error handling as you did, thanks man, and again for hanging in there with me through this problem-
Best- Frank

Public variable and Runtime error 91: Object Variable or With block variable not set

LinkBack

Thread Tools

Rate This Thread

Display

Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Re: Public variable and Runtime error 91: Object Variable or With block variable not set

Thread Information

Users Browsing this Thread

Similar Threads

Runtime error 91: Object variable or With block variable not set

Runtime Error 91 Object variable or with block variable not set

[SOLVED] VBA Runtime Error 91: Object variable or With block variable not set.

[SOLVED] Runtime Error 91: Object variable or With block variable not set

Runtime Error 91 - object variable or with block variable not set

runtime error 91 object variable or With block variable not set

Runtime Error '91' Object variable or With Block variable not set.

Bookmarks

Bookmarks

Posting Permissions