I want c2 to have the first name of an employee preceeded by a comma
automatically.... ,MARY. The first name will change but the , always
needs to be there. Is there a way to do that?
And is there a way to force all users of the report to type in capital
letters?
1. If that is the only entry in the cell, then:
Format/Cells/Number/Custom Type: ,@
2. You can use data validation to allow only capital letters:
Data/Validation/Settings
Allow: Custom
Formula: =EXACT(E1,UPPER(E1))
and add some appropriate explanatory messages.
It might be simpler to use a VBA event-triggered macro to force all entries to
capital letters.
Right click on the sheet tab and select View Code.
Paste the code below into the window that opens:
===========================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Set AOI = [a1:z1] 'range where to force all caps
Application.EnableEvents = False
If Not Intersect(Target, AOI) Is Nothing Then
For Each c In AOI
c.Value = UCase(c.Value)
If Len(c.Value) > 0 Then
c.NumberFormat = ",@"
Else
c.NumberFormat = "General"
End If
Next c
End If
Application.EnableEvents = True
End Sub
==============================
The code adds the leading comma by adjusting the cell format, which is what you
requested. It does not actually place a comma in that cell. If you wish that,
you can easily modify the macro:
---------------------
....
If Len(c.Value) > 0 And Left(c.Value, 1) <> "," Then
c.Value = "," & c.Value
End If
....
