Data validation (%)

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
106
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,694
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

Board Regular
Joined
Apr 17, 2018
Messages
106
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,984
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.
 

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
106
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
 

Forum statistics

Threads
1,082,131
Messages
5,363,337
Members
400,726
Latest member
Shahzad Taimoor

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top