Delete contents of unlocked cells in another worksheet

K

KFEagle

I found some help regarding this as a post to someone elses question.
The solution deletes the formatting in the cells as well. I need to keep
the formatting.
I'm also having trouble running the macro from another worksheet.

The macro is as follows...
this is the macro.....
Sub clear()

Sheets("Payroll - Collections - Pledges").Select
ActiveSheet.Unprotect Password:=Password
Range("C1:AR142").Select <-RIGHT HERE IS WHERE IT HANGS!
Count = 0
On Error Resume Next
For Each RNG In Selection
If RNG.Locked = False Then
Count = Count + 1
If Count = 1 Then Set Unlocked = RNG
If Count <> 1 Then Set Unlocked = Union(Unlocked, RNG)
End If
Next RNG
Unlocked.clear <-NEED FORMATTING TO STAY!
ActiveSheet.Protect Password:=Password
End Sub

Thanks!
 
D

Dave Peterson

Unlocked.clearcontents


I found some help regarding this as a post to someone elses question.
The solution deletes the formatting in the cells as well. I need to keep
the formatting.
I'm also having trouble running the macro from another worksheet.

The macro is as follows...
this is the macro.....
Sub clear()

Sheets("Payroll - Collections - Pledges").Select
ActiveSheet.Unprotect Password:=Password
Range("C1:AR142").Select <-RIGHT HERE IS WHERE IT HANGS!
Count = 0
On Error Resume Next
For Each RNG In Selection
If RNG.Locked = False Then
Count = Count + 1
If Count = 1 Then Set Unlocked = RNG
If Count <> 1 Then Set Unlocked = Union(Unlocked, RNG)
End If
Next RNG
Unlocked.clear <-NEED FORMATTING TO STAY!
ActiveSheet.Protect Password:=Password
End Sub

Thanks!
Click to expand...
 
K

KFEagle

Thanks Dave, I figured that part out later inthe day. I'm still having
problems with the part about running the macro from another worksheet though.
Any thoughts?
 
D

Dave Peterson

Make sure the code is in a General module. (Do you have it behind a worksheet?)

But you could get rid of some .selects this way:

Option Explicit
Sub clear()

Dim Rng As Range
Dim myCount As Long
Dim Unlocked As Range

With Sheets("Payroll - Collections - Pledges")
.Unprotect Password:="Password"
myCount = 0
Set Unlocked = nothing
For Each Rng In .Range("C1:AR142").Cells
If Rng.Locked = False Then
myCount = myCount + 1
If myCount = 1 Then
Set Unlocked = Rng
Else
Set Unlocked = Union(Unlocked, Rng)
End If
End If
Next Rng
If Unlocked Is Nothing Then
'do nothing
Else
Unlocked.ClearContents
End If
.Protect Password:="Password"
End With
End Sub
 
Top