MY VBA code wll not run automatically...here is the script

Question

Here is my Script

Private Sub Worksheet_Activate()

' Loop to cycle through each cell in the specified range.

For Each x In Range("A1:B30000")

  ' There is not a Proper function in Visual Basic for Applications. 

  ' So, you must use the worksheet function in the following form: 

  x.Value = Application.Proper(x.Value) 

Next

End Sub

Answer 1

Move the code into the code module of the sheet, it is executed if you switch to the sheet.

Take a coffee break, it needs some time to change 60.000 cells that way.

Andreas.

Answer 2

Hi Scott

Please, try this code

Private Sub Worksheet_Activate()

Dim x As Range

Dim myRng As Range

Set myRng = Range(Cells(1, "A"), Cells(Rows.Count, "B").End(xlUp))

Application.ScreenUpdating = False

    For Each x In myRng 

            If Not x.HasFormula Then 

                x.Value = StrConv(x.Value, vbProperCase) 

            End If 

    Next x 

Application.ScreenUpdating = True

End Sub

NOTES:

1. This code goes into the worksheet's module. To access it, double-click the relevant Sheet in the VBA Panel, and paste the above code, (as shown in the picture below by the Green arrows.), don't forget to save the file.

2. I chose to replace the static range Range("A1:B30000") with a dynamic range, as I assume you might not use all 30 thousand rows you cautiously set up in your code.

This will save time when running the macro, as it will only loop until the last row with data in columns A:B.

The macro also skips cells with formulas in it, preventing errors.

I hope my explanation is clear, and the code helps you with your project

Do let me know if you need more help

Regards

Jeovany

Answer 3

You might want to reduce the amount of actions in your code. Consider only hitting where your usedrangeas well as skipping empty cells with isemptywhich will dramatically increase your speed if such items are included in your process.

You also need to make sure this macro is firing with the on active event by placing it in the proper worksheet (NOT MODULE) as described above. To test for this, the below code will give you a popup when it runs. If you don't see it, it's not running. Once you do see it, delete it out of procedure

Private Sub Worksheet_Activate() 

    Dim aCell As Range, targetRange As Range 

    Const bigRange = "A1:B30000" 

    MsgBox "comment/delete the next 2 lines (including stop) once you see this popup (hit okay)."

    Stop 'delete this and above line once popup appears 

    'this ensures you're not checking cells outside of your worksheet's usedrange

    Set targetRange = Intersect(Me.Range(bigRange), Me.UsedRange) 

    If not targetRange Is Nothing Then 

        For Each aCell In targetRange.Cells 

            If IsEmpty(aCell) Then 

                'this check is faster than checking if string so I'd include for speed

            ElseIf Not WorksheetFunction.IsFormula(aCell) And WorksheetFunction.IsText(aCell) Then 

                aCell.Value = WorksheetFunction.Proper(aCell.Value) 

            End If 

        Next aCell 

    End If 

End Sub 

Good luck