UPPERCASE - HELP 1

[is1ma1il]

I have a section of Cells that when a person types into the Cell, if the person has typed it all uppercase it leaves it alone. If the person has typed lowercase or mixed it makes them uppercase.

 

[dsi]

You could include a macro that would update the desired range any time a cell in that range is updated using the Worksheet_Change event. If this is of interest, give us an idea of the cell range and we could whip up an example.

 

[is1ma1il]

Cells B2:AF29

Thanks for this.

 

[dsi]

This works, but has some limitations. If the user has a range selected, then starts modifying the cells, it will not work. Maybe someone else can come up with something more efficient.

In the VBA Editor, double click the desired sheet from the Project Window and add this code. The user will have to Enable macros for it to work.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim sTmp As String
    Dim iPos As String
    Dim bCol As Boolean
    Dim bRow As Boolean
    Dim sCol As String
    Dim iRow As Long
    
    sTmp = Right(Target.Address, Len(Target.Address) - 1)
    iPos = InStr(1, sTmp, "$")
    sCol = Left(sTmp, iPos - 1)
    iRow = Right(sTmp, Len(sTmp) - iPos)
    
    bCol = False: bRow = False
    
    If iRow >= 2 And iRow <= 29 Then
        bRow = True
    End If
    If Len(sCol) = 2 And Left(sCol, 1) = &quot;A&quot; And Right(sCol, 1) <= &quot;F&quot; Then
        bCol = True
    ElseIf sCol >= &quot;B&quot; Then
        bCol = True
    End If
    If bCol = True And bRow = True Then
        Target = UCase(Target.Text)
    End If
End Sub

There is another option that will not have the above limitation. On the Workbook_BeforeSave event, the entire range could be scanned and updated. This may be a better option. To use this, double-click the ThisWorkbook object from the Project Window in the VBA Editor and add this code. Note, you will probably have to change the sheet name.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet
    Dim iCol As Integer
    Dim iRow As Integer
    
    'Change Sheet Name if Necessary
    Set ws = Sheets("Sheet1")
    
    For iCol = 2 To 32
        For iRow = 2 To 29
            ws.Cells(iRow, iCol) = UCase(ws.Cells(iRow, iCol).Text)
        Next iRow
    Next iCol

    Set ws = Nothing
End Sub

Hope this helps...

 

[is1ma1il]

Thanks

 

[Dreamboat]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10&quot) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

If you want all cells in columns B through D to be UC, then do Range("B&quot

You need to paste this into the Worksheet code.


From:

http://www.cpearson.com/excel/case.htm

Anne Troy
Dreamboat@TheWordExpert.com
Anne@MrExcel.com

 

[dsi]

Application.Intersect

That's what I was looking for.

Thanks, have one on me...

 

[Dreamboat]

Hey. I didn't write it. Just stole it.

LOL Anne Troy
Dreamboat@TheWordExpert.com
Anne@MrExcel.com