VBA Uppercase

[Livin404]

Good afternoon,

The Private Sub below work properly with the exception of Ucase (.text). Some of the cells have lower case, so I figured if I put it in here it will go back and correct the ones where we have lowercase. Looking for consistency.
Here is the code. I think the Ucase is the only thing that is not correct.

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("ACTIVITY").Cells
 With Range("A2:F2", Range("A" & Rows.Count).End(xlUp))
 .Font.Bold = False
 .Font.Size = 12
 .Font.Name = "Times New Roman"
  UCase (.Text)
 End With
  Columns.AutoFit
  End With
End Sub

Thank you,

 

[Joe4]

There are some confusing (and perhaps) unnecessary parts of this code.
So can we get a little clarification, so we can help you clean this up?

Is this code in the "ACTIVITY" worksheet module?
Are you wanting this to happen as data is entered, or on existing data?
What range exactly do you want this applied to?

 

[rlv01]

Maybe something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, CellRange As Range

    Set CellRange = Me.Range("A2:F2", Me.Range("A" & Rows.Count).End(xlUp))

    With CellRange
        .Font.Bold = False
        .Font.Size = 12
        .Font.Name = "Times New Roman"
    End With
    
    Application.EnableEvents = False
    For Each R In CellRange
        R.Value = UCase(R.Text)
    Next R
    Application.EnableEvents = True
    
    Columns.AutoFit
End Sub

 

[Livin404]

There are some confusing (and perhaps) unnecessary parts of this code.
So can we get a little clarification, so we can help you clean this up?

Is this code in the "ACTIVITY" worksheet module?
Are you wanting this to happen as data is entered, or on existing data?
What range exactly do you want this applied to?

It is which is why I specifically mentioned "ACTIVITY". Cells A2 to F1.

Thank you,

 

[Livin404]

Maybe something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, CellRange As Range

    Set CellRange = Me.Range("A2:F2", Me.Range("A" & Rows.Count).End(xlUp))

    With CellRange
        .Font.Bold = False
        .Font.Size = 12
        .Font.Name = "Times New Roman"
    End With
  
    Application.EnableEvents = False
    For Each R In CellRange
        R.Value = UCase(R.Text)
    Next R
    Application.EnableEvents = True
  
    Columns.AutoFit
End Sub

That does work, but it takes an incredibly long time. There has to be something causing it to take so long. It seems like the whole process has slowed. It's correct, but not worth the wait. I'm sure it has to do with the range I selected. Thank you,

 

[Joe4]

That does work, but it takes an incredibly long time. There has to be something causing it to take so long. It seems like the whole process has slowed. It's correct, but not worth the wait. I'm sure it has to do with the range I selected. Thank you,

That is because you have this running every time ANY cell is updated on the whole sheet, and checking those cells every time it runs, and it maky be calling itself, getting caught in a loop.
That is why I asked this question (which you have not answered):

Are you wanting this to happen as data is entered, or on existing data?

Please answer this question.
If this is to only run on data entry, we can restrict it, and make it run much faster.

It is which is why I specifically mentioned "ACTIVITY". Cells A2 to F1.

Worksheet_Change event procedures are designed to run against the sheet modules they are placed in.
Because of this, it is not necessary to reference the sheet name in the code, unless you are trying to apply it to some other sheet.
That is why I asked - it is not necessary to have it there, and can be confusing.

 

[Joe4]

If you just wanted it to apply on new data entries in cells A2:F2, then the code should just look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   See if any data updates to range A2:F2
    Set rng = Intersect(Target, Range("A2:F2"))
    If rng Is Nothing Then Exit Sub
    
'   Update cells
    Application.EnableEvents = False
    For Each cell In rng
        With cell
            .Font.Bold = False
            .Font.Size = 12
            .Font.Name = "Times New Roman"
            .Value = UCase(.Value)
        End With
    Next cell

    Application.EnableEvents = True
    
End Sub

 

[Livin404]

That is because you have this running every time ANY cell is updated on the whole sheet, and checking those cells every time it runs, and it maky be calling itself, getting caught in a loop.
That is why I asked this question (which you have not answered):

Please answer this question.
If this is to only run on data entry, we can restrict it, and make it run much faster.


Worksheet_Change event procedures are designed to run against the sheet modules they are placed in.
Because of this, it is not necessary to reference the sheet name in the code, unless you are trying to apply it to some other sheet.
That is why I asked - it is not necessary to have it there, and can be confusing.

Oh sorry I was running out the door with the last post. From now on it's all CAPS going forward. Anything with lowercase has been changed. In this case there is only one datasheet which is "Activity".

I did try to run what you had posted but I'm afraid nothing went to Caps. I'm sure it's because I didn't answer your question properly.

Thank you very much indeed.

 

[rlv01]

That does work, but it takes an incredibly long time. There has to be something causing it to take so long. It seems like the whole process has slowed. It's correct, but not worth the wait. I'm sure it has to do with the range I selected. Thank you,

Well, at least we've moved from 'not working' to 'working too slowly'. If there are no formulas in your A to F range, then this will work a bit faster.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, CellRange As Range
    Dim CA As Variant
    Dim RRow As Long, RCol As Long

    Set CellRange = Me.Range("A2:F2", Me.Range("A" & Rows.Count).End(xlUp))

    If Not Application.Intersect(CellRange, Target) Is Nothing Then
        With CellRange
            .Font.Bold = False
            .Font.Size = 12
            .Font.Name = "Times New Roman"
        End With

        CA = CellRange.Value
        For RRow = LBound(CA, 1) To UBound(CA, 1)
            For RCol = LBound(CA, 2) To UBound(CA, 2)
                CA(RRow, RCol) = UCase(CStr(CA(RRow, RCol)))
            Next RCol
        Next RRow

        Application.EnableEvents = False
        CellRange.Value = CA                              'put array values back into range
        Application.EnableEvents = True
        Columns.AutoFit
    End If
End Sub

And if, as @Joe4 suggested, you can limit it to just the cells changed, it could be made faster still.

 

[Livin404]

Well, at least we've moved from 'not working' to 'working too slowly'. If there are no formulas in your A to F range, then this will work a bit faster.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, CellRange As Range
    Dim CA As Variant
    Dim RRow As Long, RCol As Long

    Set CellRange = Me.Range("A2:F2", Me.Range("A" & Rows.Count).End(xlUp))

    If Not Application.Intersect(CellRange, Target) Is Nothing Then
        With CellRange
            .Font.Bold = False
            .Font.Size = 12
            .Font.Name = "Times New Roman"
        End With

        CA = CellRange.Value
        For RRow = LBound(CA, 1) To UBound(CA, 1)
            For RCol = LBound(CA, 2) To UBound(CA, 2)
                CA(RRow, RCol) = UCase(CStr(CA(RRow, RCol)))
            Next RCol
        Next RRow

        Application.EnableEvents = False
        CellRange.Value = CA                              'put array values back into range
        Application.EnableEvents = True
        Columns.AutoFit
    End If
End Sub

And if, as @Joe4 suggested, you can limit it to just the cells changed, it could be made faster still.

I couldn't have asked for a better result. Thank you and thank you both for your patience.