column uppercase

[hendrikbez]

Hi i want to add button on sheet1, to make all words in sheet4 column c in upper case, this is the code i have.

How do I add a sheet to it.

Private Sub Worksheet_Activate()
On Error Resume Next
Application.ScreenUpdating = False

Dim cell As Range

For Each cell In Range("$C$2:" & Range("$C$2").SpecialCells(xlLastCell).Address)
    cell.Value = UCase(cell.Value)
Next cell

On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[jasonb75]

With Worksheet_Activate code, you don't add the sheet to the code, you add the code to the sheet or run it with a buton.

Select the other sheet that you want to use the code with, then right click the sheet name (in excel) and click 'view code'. Paste your code into that module.

Go back to excel, then save the workbook (as macro enabled). To run the code for the first time, you will need to select another sheet, then re-select the sheet with the code.

 

[Elaszat]

Hi hendrikbez,

the code right now applies the upper case to column C in the currently active sheet.

You want to address the sheet like this:

For Each cell in Worksheets("Sheet4").Range("$C$2:" & Worksheets("Sheet4").Range("$C$2").SpecialCells(xlLastCell).Address)

@jasonb75 is right, though, you need the correct module for your code. If you want a button on Sheet 1, you need to put the code in the sub for the button, which is located in a general module and assign that sub to the button.

Regards,
Elaszat

 

[hendrikbez]

jasonb75 and Elaszat, thank your for your exlpantion, I have got it working.

 

[Elaszat]

Great and thanks for the feedback

 

[Rick Rothstein]

In case you might be interested, you do not need a loop to do what your code is doing.

Private Sub Worksheet_Activate()
  With Range("C1", Cells(Rows.Count, "C").End(xlUp))
    .Value = Evaluate("IF(" & .Address & "="""","""",UPPER(" & .Address & "))")
  End With
End Sub

 

[Elaszat]

Wow, that's really cool. Thanks for the interesting snippet, Rick!