Status Bar Update

RandyD123

Board Regular
Joined
Dec 4, 2013
Messages
149
I want to add a status bar update as this code process's

Code:
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Dec 4, 2013
Messages
149
Try:
Code:
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.
 
Last edited:

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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:
Rich (BB code):
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

Board Regular
Joined
Dec 4, 2013
Messages
149
@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:
Rich (BB code):
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top