specific values to caps???

[LlebKcir]

To keep the look clean and simple on a few spreadsheets the desire is to have y, yes, n, no, na, n/a inputs from the user displayed as capital letters. looking around i found some vba code that will kind of do what i am after, but the way i typed it up, it cycles through all of the options in the code and does not let you clear the cell. once data is in the cell, it is there for good until you disable the macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A1 As Range
    Set A1 = Range("B:G")
    If Not Intersect(Target, A1) Is Nothing Then
        Application.EnableEvents = False
            Target.Value = UCase("y")
            Target.Value = UCase("yes")
            Target.Value = UCase("n")
            Target.Value = UCase("no")
            Target.Value = UCase("n/a")
            Target.Value = UCase("na")
        Application.EnableEvents = True
    End If
End Sub

it is actually kind of funny to watch. please help me help you so i can learn.

Thank you.

 

[Peter_SSs]

Try this instead

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("B:G"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      Select Case LCase(c.Value)
        Case "y", "yes", "n", "no", "na", "n/a"
          c.Value = UCase(c.Value)
      End Select
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[LlebKcir]

Try this instead

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("B:G"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      Select Case LCase(c.Value)
        Case "y", "yes", "n", "no", "na", "n/a"
          c.Value = UCase(c.Value)
      End Select
    Next c
    Application.EnableEvents = True
  End If
End Sub

Peter, thank you that works great. I never thought of using the case logic inside of that loop.

 

[Peter_SSs]

Peter, thank you that works great.

Cheers. You're welcome.