Data validation (%)

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
120
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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.
 
Upvote 0
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...
 
Upvote 0
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:

Code:
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.
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top