Autochange Yes/No to Y/N in Excel

March 18, 2009 at 13:13:54
Specs: Windows XP
How can I set formatting in Excel columns so that when users type "Yes" or "No", the cell formats the entry as "Y" or "N"?

See More: Autochange Yes/No to Y/N in Excel

Report •


#1
March 18, 2009 at 15:22:57
As far as I know, TEXT can not be Custom Formatted in Excel.

MIKE


Report •

#2
March 18, 2009 at 19:22:33
Right click on the sheet tab, choose View Code, and paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
 If UCase(Target) = UCase("Yes") Then Target = "Y"
 If UCase(Target) = UCase("No") Then Target = "N"
End Sub

Every time a change is made to the worksheet, the code will check the input and if it's Yes or No, it'll change it to Y or N.

UCase is used so the code will handle Yes, YES, yes, YeS, etc. by internally setting the contents to all uppercase and then comparing it to an all uppercase Yes or No.


Report •

#3
March 18, 2009 at 19:40:27
Very nice, think I'll save this one myself. :-)

MIKE


Report •

Related Solutions

#4
March 19, 2009 at 05:48:54
That's beautiful and works wonders! Thank you DerbyDad03! And thank you, Mike, for a great site!

Report •

#5
March 19, 2009 at 05:56:45
Worksheet_Change and Selection_Change macros can be pretty useful.

There are lots of other Worksheet and Workbook based macros that can be used for all sorts of interesting things.


Report •

#6
March 19, 2009 at 06:07:14
Good to know! Is that the best way for me to set a column reserved for the 2-letter State to force uppercase in the whole column?

Report •

#7
March 19, 2009 at 06:49:40
I'm not sure what you're asking re: 2-letter stste column.

Please clarify


Report •

#8
March 19, 2009 at 06:56:15
Sorry, I should have been more specific. In the same worksheet, Column E is where users will enter the 2-characters for states, i.e., AL, AZ MI, MN, NY, OK, PA, WI, etc. Some users are entering Al, Az, Mi, Mn, Ny, Ok, Pa, Wi, etc. I'd like to force the column to return all upper-case letters. Can I do this by simply adding another line to the macro code you provided above?

Report •

#9
March 19, 2009 at 08:12:55
Yes, you can do it with one line, but the line would be slightly different than the others.

The first 2 lines are not forcing anything in the Excel sheet to be uppercase. They are reading the contents of the cell and forcing it to be uppercase in "VBA memory" so that VBA can compare apples to apples regardless of what the user enters. If we didn't do it that way, we'd have to check for every combination of Yes, YeS, etc. line by line. The code then puts a "Y" or "N" in the cell based on the input.

Your second request is slightly different. You are asking for 2 things:

1 - Force the contents of the actual cell to be uppercase
2 - Limit this to Column E.

So first we have to check and see if the Target cell is in Column E, then, if it is, we can replace the contents with the uppercase version.

Note: VBA checks some things with a "double negative" i.e. if Not something is Nothing, then it must be something. That's why the new line might look a little strange.

Here's the code to handle Yes and No in every cell in the sheet and to force the contents of any cell in Column E to be uppercase:

Private Sub Worksheet_Change(ByVal Target As Range)
 If UCase(Target) = UCase("Yes") Then Target = "Y"
 If UCase(Target) = UCase("No") Then Target = "N"
 If Not Intersect(Target, Columns("E")) Is Nothing Then Target = UCase(Target)
End Sub


Report •

#10
March 19, 2009 at 09:04:22
Much better! Since this will eventually be a template that people will use to create a new spreadsheet each month, I'm suspecting that some will just go in and highlight a bunch of cells and click the delete button. When I tried this, I received a Microsoft Visual Basic error message: Run-time error '13': Type mismatch, with the options to End, Debug or Help. Is there a way to turn off that error message so that users won't see it? Thanks so much for your patience with me!

Report •

#11
March 19, 2009 at 09:39:26
You might have other issues to deal with. If users don't have Macros enabled on their machine, they are going to get a warning when they try to open the workbook.

The warning they get will differ based on the level of Macro Security they have set.

This should deal with users who select more than one cell.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells.Count > 1 Then Exit Sub
 If UCase(Target) = UCase("Yes") Then Target = "Y"
 If UCase(Target) = UCase("No") Then Target = "N"
 If Not Intersect(Target, Columns("E")) Is Nothing Then Target = UCase(Target)
End Sub

I should note that it's next to impossible for me to anticipate how users will use a worksheet, so it's next to impossible for me to write the perfect code. It would behoove you to study the code and see what it is doing as a means to learn a little about VBA. If you are going to distribute workbooks with macros in them, then there should someone available who can maintian those macros. As a simple example, let's say you move the States to Column D. Someone will have to go into the macro and modify the code.



Report •

#12
March 19, 2009 at 09:48:54
Excellent! You're a rock star! Thanks again so much for your help!

Report •

#13
March 19, 2009 at 09:53:02
re: You're a rock star!

You say that now...I'll just wait until the next issue pops up ;-)


Report •


Ask Question