Status Bar Update

[RandyD123]

I want to add a status bar update as this code process's

Sub ClearUnlockedCells()
'
' ClearUnlockedCells Macro
' This Macro Will Clear All Unlocked Cells.
'
 
'
If MsgBox("Clear All Unprotected Cells On This Sheet?", vbYesNo + vbQuestion + vbDefaultButton2, "Clear All Unprotected Cells") = vbNo Then
    MsgBox "Operation Cancelled, No Changes Have Been Made.", vbInformation
    Exit Sub
End If
    ActiveSheet.Protect
    Dim WorkRange As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = False Then Cell.Value = ""
    Next Cell
    MsgBox "All UnLocked Cells Have Been Cleared!", vbInformation
End Sub

How can I do this so it shows the progress or maybe just shows that something is happening?

Thanks for any help.

 

[JackDanIce]

Try:

Sub ClearUnlockedCells_v1()

' ClearUnlockedCells Macro
' This Macro Will Clear All Unlocked Cells.

Const msg1 As String = "Clear All Unprotected Cells On This Sheet?"
Const msg2 As String = "Operation Cancelled, No Changes Have Been Made."

Dim WorkRange   As Excel.Range
Dim Cell        As Excel.Range
Dim x           As Long
Dim xPer        As Double

If MsgBox(msg1, vbYesNo + vbQuestion + vbDefaultButton2, "Clear All Unprotected Cells") = vbNo Then
    MsgBox msg2, vbInformation
    Exit Sub
End If

Application.ScreenUpdating = False

With ActiveSheet
    .Protect
    Set WorkRange = .UsedRange
    x = 1
    For Each Cell In WorkRange
        xPer = Round(x / WorkRange.Cells.Count * 100, 2)
        Application.StatusBar = "Testing cell: " & x & " of " & WorkRange.Cells.Count & " Progress: " & xPer & "%"
        If Not Cell.Locked Then Cell.Value = vbNullString
        x = x + 1
    Next Cell
End With

With Application
    .StatusBar = False
    .ScreenUpdating = True
End With

MsgBox "All UnLocked Cells Have Been Cleared!", vbInformation

Set WorkRange = Nothing

End Sub

 

[RoryA]

I wouldn't recommend doing it for every cell - that will slow the whole thing down further. I'd do it say every 10% of cells.

 

[RandyD123]

Try:

Sub ClearUnlockedCells_v1()

' ClearUnlockedCells Macro
' This Macro Will Clear All Unlocked Cells.

Const msg1 As String = "Clear All Unprotected Cells On This Sheet?"
Const msg2 As String = "Operation Cancelled, No Changes Have Been Made."

Dim WorkRange   As Excel.Range
Dim Cell        As Excel.Range
Dim x           As Long
Dim xPer        As Double

If MsgBox(msg1, vbYesNo + vbQuestion + vbDefaultButton2, "Clear All Unprotected Cells") = vbNo Then
    MsgBox msg2, vbInformation
    Exit Sub
End If

Application.ScreenUpdating = False

With ActiveSheet
    .Protect
    Set WorkRange = .UsedRange
    x = 1
    For Each Cell In WorkRange
        xPer = Round(x / WorkRange.Cells.Count * 100, 2)
        Application.StatusBar = "Testing cell: " & x & " of " & WorkRange.Cells.Count & " Progress: " & xPer & "%"
        If Not Cell.Locked Then Cell.Value = vbNullString
        x = x + 1
    Next Cell
End With

With Application
    .StatusBar = False
    .ScreenUpdating = True
End With

MsgBox "All UnLocked Cells Have Been Cleared!", vbInformation

Set WorkRange = Nothing

End Sub

Thank You very much. It works perfectly. And it seems to be faster than my original code! I noticed a ".protect" in the code. I have the sheet open password protected and the users do not know the password, so even if I forget to protect it on close, it auto protects on open.

 

[JackDanIce]

@Rory - Good suggestion

@RandyD123, your original code contains:

If MsgBox("Clear All Unprotected Cells On This Sheet?", vbYesNo + vbQuestion + vbDefaultButton2, "Clear All Unprotected Cells") = vbNo Then
MsgBox "Operation Cancelled, No Changes Have Been Made.", vbInformation
Exit Sub
End If
ActiveSheet.Protect

The .protect in mine is operating on the With ActiveSheet part so it's just what you had but adjusted to account for the With part.

Glad it's working faster for you, I suspect that is because ScreenUpdating was disabled before it clears each cell and then re-enabled after.

Taking into account Rory's suggestion, modified code (new lines in blue) is:

Sub ClearUnlockedCells_v2()

' ClearUnlockedCells Macro
' This Macro Will Clear All Unlocked Cells.

Const msg1 As String = "Clear All Unprotected Cells On This Sheet?"
Const msg2 As String = "Operation Cancelled, No Changes Have Been Made."

Dim WorkRange   As Excel.Range
Dim Cell        As Excel.Range
Dim x           As Long
Dim modVal      As Long
Dim xPer        As Double

If MsgBox(msg1, vbYesNo + vbQuestion + vbDefaultButton2, "Clear All Unprotected Cells") = vbNo Then
    MsgBox msg2, vbInformation
    Exit Sub
End If

Application.ScreenUpdating = False

With ActiveSheet
    .Protect
    Set WorkRange = .UsedRange
    x = 1
    modVal = 0.1 * Application.RoundUp(WorkRange.Cells.Count, 0)
    
    For Each Cell In WorkRange
        xPer = Round(x / WorkRange.Cells.Count * 100, 2)
        If x Mod modVal = 0 Then Application.StatusBar = "Testing cell: " & x & " of " & WorkRange.Cells.Count & " Progress: " & xPer & "%"
        If Not Cell.Locked Then Cell.Value = vbNullString
        x = x + 1
    Next Cell
End With

With Application
    .StatusBar = False
    .ScreenUpdating = True
End With

MsgBox "All UnLocked Cells Have Been Cleared!", vbInformation

Set WorkRange = Nothing

End Sub

 

[RandyD123]

@Rory - Good suggestion

@RandyD123, your original code contains:

The .protect in mine is operating on the With ActiveSheet part so it's just what you had but adjusted to account for the With part.

Glad it's working faster for you, I suspect that is because ScreenUpdating was disabled before it clears each cell and then re-enabled after.

Taking into account Rory's suggestion, modified code (new lines in blue) is:

Sub ClearUnlockedCells_v2()

' ClearUnlockedCells Macro
' This Macro Will Clear All Unlocked Cells.

Const msg1 As String = "Clear All Unprotected Cells On This Sheet?"
Const msg2 As String = "Operation Cancelled, No Changes Have Been Made."

Dim WorkRange   As Excel.Range
Dim Cell        As Excel.Range
Dim x           As Long
Dim modVal      As Long
Dim xPer        As Double

If MsgBox(msg1, vbYesNo + vbQuestion + vbDefaultButton2, "Clear All Unprotected Cells") = vbNo Then
    MsgBox msg2, vbInformation
    Exit Sub
End If

Application.ScreenUpdating = False

With ActiveSheet
    .Protect
    Set WorkRange = .UsedRange
    x = 1
    modVal = 0.1 * Application.RoundUp(WorkRange.Cells.Count, 0)
    
    For Each Cell In WorkRange
        xPer = Round(x / WorkRange.Cells.Count * 100, 2)
        If x Mod modVal = 0 Then Application.StatusBar = "Testing cell: " & x & " of " & WorkRange.Cells.Count & " Progress: " & xPer & "%"
        If Not Cell.Locked Then Cell.Value = vbNullString
        x = x + 1
    Next Cell
End With

With Application
    .StatusBar = False
    .ScreenUpdating = True
End With

MsgBox "All UnLocked Cells Have Been Cleared!", vbInformation

Set WorkRange = Nothing

End Sub

Thank You very much. Perfect!

 

[JackDanIce]

You're welcome