Only Allow 2 digit state code & force upper case

Hello,

Is there vba to force column to 2 digit state code? If the user types lower case also force to 2 digit uppercase also.

Tx would be TX
tx would be TX
Texas would be TX
texas would be TX and so on

Thank you

The first thing that comes to mind iw why not use a data validation list for this?

Sample workbook attached. DV list in Sheet 2 B1 - little bit of VBA forces upper case:

Please Login or Register  to view this content.

Try adding these lines to the bottom of your code AFTER End If and BEFORE End Sub:

Please Login or Register  to view this content.

Sorry - it has me stumped. Hopefully one of the VBA experts can spot what's wrong (I am just a learner myself).

I did try several methods - none worked. I have a feeling that it might be because of the DV list - I shall be interested to know myself!

Tried that - it doesn't work in this workbook. Nothing changes after text is entered into a cell in column F.

Big chance that is because the macro freezed before and enableEvents is still in false state and then the worksheet_change code is not run..

That lead to many frustrations for me testing codes

Try my sheet..

To fix in own sheet.. open immedeate window and copy the enable=true line in there and press eenter

Or type "?application.enableevents" and enter. To verify the state is true

This works for me in your sample workbook, having adapted the code Ali provide and Roel fixed.

Replace your existing code with this

Please Login or Register  to view this content.

And, before you try to test it, enter this into the Immediate Window and press Enter

Please Login or Register  to view this content.

Just to be clear, Roel's sample workbook works as it should (and as you wish).

I'm guessing that event handling is disabled. Use the suggested fix in my last post.

And, again, it is working for me.

Maybe try closing Excel down and restarting it.

I can see no reason why the various codes don't work for you.

The Undo in column A works; entry in column B generates a new code: row number + 100, and the Upper Case is working.

Can't fix it from this end 'cos it ain't broke here. Something is off in your environment.

You're welcome. Thanks for the rep.

This should have worked:

And, before you try to test it, enter this into the Immediate Window and press Enter

Please Login or Register  to view this content.

The first block of code appears to preclude tabbing from the last cell in the last row. Not sure why but it is clearly being seen as a change in column A and your code Undoes that.

Can anyone explain how event handling had become disabled? Is this something that is workbook specific? I ask because I haven't had any issues with upper case code working until this morning in the OP's workbook. Now, in your workbook, Trevor, it isn't working for me.

How do I switch it back on permanently? And more to the point, how did it get switched off in the first place?

And, lastly, where/what is the 'immediate window'?

EDIT: Closing down Excel and restarting has fixed it - phew!

@Ali: Typically, event handling becomes disabled if the event handler does not terminate as expected. For example, if the event handling has been switched off and the code crashes or is forced to stop before it is reenabled. So, if you step through code to find an error/problem, and you have passed the enable=False, and you reset the code once you've identified and fixed the fault, voila, event handling disabled until you manually reenable it or you restart Excel.

Last question next: the Immediate Window can be made visible using View | Immediate Window or pressing Ctrl-G. You close it by clicking the x in the top right corner. It normally appears below the code pane.

When it is visible, you can use it to check the values of variables, see the output of Debug.Print statements and enter commands directly, including simple loops that can be completed on one line with the commands separated by colons.

For example:

Please Login or Register  to view this content.

So, to switch event handling back on permanently, you can type:

Please Login or Register  to view this content.

in the Immediate Window and press Enter.

Last resort, close and reload Excel.

See: https://docs.microsoft.com/en-us/off...mediate-window

Hope that covers all your questions.

Thanks, Trevor - this is very illuminating. Thanks for taking the trouble.

You're welcome. Thanks for the rep.

Should have said, now you know how to fix it, try it.

Comment out the EnableEvents=True in a simple change event handler and make a change. Make another change. Nothing happens. Go to the Immediate Window and reenable it.

Thanks for the extra information. It's something that a rookie coder like me would fall into without even realising it, so I for one really appreciate these thorough explanations.

@Trevor - I'll give that a try tomorrow.

Oh, Ali, how can you bear to wait?

@Roel: you did in fact make all the same/correct suggestions. But, for whatever reason, it didn't appear to work for the OP. And, as Ali asked some specific questions, I just wanted to clarify what (I/you thought) happened and how to try to fix it without necessarily restarting Excel.