I'm not sure what you're asking re: 2-letter stste column. Please clarify

0

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?

0

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

0

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!

0

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.

0

Excellent! You're a rock star! Thanks again so much for your help!

0

re: You're a rock star! You say that now...I'll just wait until the next issue pops up ;-)

0