How to combine locked cells and forced uppercase separately using range?

[Riki55]

Hi, I am new to VBA and require some help.

I am able to lock cells for a certain range (below):

Please Login or Register  to view this content.

And also make certain cells to force uppercase on them (below):

Please Login or Register  to view this content.

And I learnt that there cannot be duplicates of Worksheet_Change but i am having difficulties on how to combine them together. Kindly advise on how to combine them both.
Have attached the excel document on what i have done also.
Thank you.

[Rick Rothstein]

Untested but this should correctly combine your two Change event procedures into a single one.

Please Login or Register  to view this content.

Note: I used your code exactly as you wrote but have a couple of comments about it. First, the variable name I highlighted in red is not Dim'med in your code and no value has been assigned to it before you used it in your If..Then test. Second, as written, if a large selection of cells intersects with either of the ranges you are testing for, your code will process every cell within that large selection. This would be particularly troublesome if you attempted to select all the cells on the sheet in order to clear them... it would take a long, long, long, long time before you would be able to regain control of your computer. If you are only changing one cell at a time, you should test (at the beginning of your code) that Target.Count and if it is greater than 1, then Exit Sub.

[sintek]

Please Login or Register  to view this content.

[Riki55]

Untested but this should correctly combine your two Change event procedures into a single one.

Please Login or Register  to view this content.

Note: I used your code exactly as you wrote but have a couple of comments about it. First, the variable name I highlighted in red is not Dim'med in your code and no value has been assigned to it before you used it in your If..Then test. Second, as written, if a large selection of cells intersects with either of the ranges you are testing for, your code will process every cell within that large selection. This would be particularly troublesome if you attempted to select all the cells on the sheet in order to clear them... it would take a long, long, long, long time before you would be able to regain control of your computer. If you are only changing one cell at a time, you should test (at the beginning of your code) that Target.Count and if it is greater than 1, then Exit Sub.

Hi Rick, i tried using the code and it returned an error (below) with the portion highlighted:
Run-time error '91': Object variable or With block variable not set

Please Login or Register  to view this content.

[Riki55]

Please Login or Register  to view this content.

Hi sintek, where do i put the script as mentioned in your reply?

[Rick Rothstein]

Hi Rick, i tried using the code and it returned an error (below) with the portion highlighted:
Run-time error '91': Object variable or With block variable not set

Please Login or Register  to view this content.

Looks like I screwed that up somewhat. See if this works for you...

Please Login or Register  to view this content.

[Riki55]

Looks like I screwed that up somewhat. See if this works for you...

Please Login or Register  to view this content.

Hi Rick, thanks for your reply. It is working now but somehow the forcing uppercase for the specific range is not working?

[sintek]

Error in your code snippet ... 0 to be used ... not O

Please Login or Register  to view this content.

As Rick pointed out earlier...Were is mStr declared and where is its value assigned..

Hi sintek, where do i put the script as mentioned in your reply?

Please Login or Register  to view this content.

[Riki55]

Error in your code snippet ... 0 to be used ... not O

Please Login or Register  to view this content.

As Rick pointed out earlier...Were is mStr declared and where is its value assigned..

Please Login or Register  to view this content.

Hi sintek, you are right on the 0 and not O. I didn't notice that.
Thank you and also to Rick. It is working perfectly now!

[sintek]

For future threads...No need to quote entire posts as this just clutters the thread...If you want to reference something specifically, just quote that small snippet...

So just out of interest...Were is mStr declared and where is its value assigned..

And...thanks for rep +