Data validation (%)

[JamesonMH]

Hi there,

My question:
How do I restrict the user from manually typing the "%" symbol in a cell??

Background:
I'm trying to finish a basic user form (with drop-down lists) where:

- if option 1 is chosen in cell A1, then cell A2 is formatted in Accounting format.
- if option 2 is chosen in cell A1, then cell A2 is formatted in Percentage format.

I set the above up easily with a Custom format in conditional formatting so that when option 2 is chosen, it shows as % format (even though the cells default is Accounting).

BUT, if the user chooses option 1 and manually enters a "%" it changes the default format from Accounting to an actual %, which I don't want (i.e. option 1 should only allow Accounting format).

I made a weak workaround by putting data validation so regardless of which option's chosen, it must be > 1. This limits me though since there are instances where option 1 is chosen and it's legitimately $0.50 or something < 1.

Bottom line: how do I restrict the user from manually typing the "%" symbol??

Sorry for long msg if you need a sample just let me know!

Cheers,
James

 

[SpillerBD]

Data Validation does not include Format Validation.
Option to control or restrict changes to Formatting is through the Cell Protection which would require Protecting the Sheet.
An alternative would be using a change event on the cell to reapply your desired format to the cell when a change to the cell is made.

 

[JamesonMH]

Data Validation does not include Format Validation.
Option to control or restrict changes to Formatting is through the Cell Protection which would require Protecting the Sheet.
An alternative would be using a change event on the cell to reapply your desired format to the cell when a change to the cell is made.

Thanks for your reply. What is the alternative you provided (change event)? I'm not familiar...

 

[Eric W]

An interesting interaction between features. If you enter a number with a %, it changes the base format of the cell to percent. CF changes the display format to Accounting, but once the base format is percent, Excel automatically divides everything entered in that cell by 100.

As SpillerBD said, you can use a change event to force the base format back to General. If you want to try that, right click on the sheet tab on the bottom, select View Code, and paste this into the window that opens:

Private Sub Worksheet_Change(ByVal target As Range)
    
    If target.Address(0, 0) = "A2" And [A1].Value = "Option 1" Then target.NumberFormat = "General"
    
End Sub

Press Alt-Q to close the editor. However, in experimenting with that, I still found some odd interactions that can appear. You may have to experiment quite a bit to get it to do what you want. Good luck.

 

[JamesonMH]

An interesting interaction between features. If you enter a number with a %, it changes the base format of the cell to percent. CF changes the display format to Accounting, but once the base format is percent, Excel automatically divides everything entered in that cell by 100.

As SpillerBD said, you can use a change event to force the base format back to General. If you want to try that, right click on the sheet tab on the bottom, select View Code, and paste this into the window that opens:

Private Sub Worksheet_Change(ByVal target As Range)
    
    If target.Address(0, 0) = "A2" And [A1].Value = "Option 1" Then target.NumberFormat = "General"
    
End Sub

Press Alt-Q to close the editor. However, in experimenting with that, I still found some odd interactions that can appear. You may have to experiment quite a bit to get it to do what you want. Good luck.

This code is much better than my non-VBA solution - thanks Eric!

And yea I found that observation too, almost like a CF glitch. But this will get me through. Thanks again.

James