Clear data from unlocked cells

wpryan · #1 02-18-2016, 02:47 AM

Hi All, I have a workbook that is used as a service report. I developed the form in Excel 2010, and used it without problem with Excel 2010 and 2013. When I upgraded to Office 2016, the code that I was using to clear the data stopped working. It's saying that I'm trying to change cells in protected sheet. Well that's the point - I want to change only the values on the protected sheet with the cells that are unlocked, so I make sure first that the worksheet is protected. As I said, the code worked fine on Excel 2010 and 2013. Here it is:

Code:

Sub ClearUnlockedCells()

Dim msg As String, ans As Variant
 
    msg = "Completing this action will delete all form data! Continue?"
    ans = msgbox(msg, vbOKCancel, "Z-Models SCP Info")

    Select Case ans
    Case vbOK

        Application.ScreenUpdating = False
        'On Error Resume Next
        Application.DisplayAlerts = False
        
    
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                .Cells.Value = vbNullString
            End With
        Next ws
    
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        
        
    Case vbCancel
    End Select

    Sheets("General Data").Range("B4").Select
    
End Sub

...any help is appreciated.

ufopilot · #2 02-18-2016, 11:35 PM

Maybe the cells that were once unprotected are somehow changed to protected?
You can also place this in your code:

Code:

ActiveSheet.Unprotect

then at the end of your code:

Code:

ActiveSheet.Protect

You may want to make sure that you are indeed clearing data from unlocked or unprotected cells.

wpryan · #3 02-20-2016, 01:28 AM

Thanks for your reply. I tried your suggestion (unlock worksheet first) and as I expected, the result was that all data on the worksheet was deleted - all text and formulas. I can run the code on a system running Office 2013 or 2010, it works just fine.

I'm curious, is it possible to open the document in compatibility mode for those versions in 2016?

wpryan · #4 02-22-2016, 03:00 AM

Hi, I decided to change the code to reference named ranges instead of "UsedRange". However I am getting error 1004, Application or Object Defined Error on the line which is in red. I can't figure this out... Modified code:

Code:

Sub ClearUnlockedCells()

Dim msg As String, ans As Variant
 
    msg = "Completing this action will delete all form data! Continue?"
    ans = msgbox(msg, vbOKCancel, "Z-Models SCP Info")

    Select Case ans
    Case vbOK

        Application.ScreenUpdating = False
        'On Error Resume Next
        Application.DisplayAlerts = False
        
        Range("Z_GD") = ""
            
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        
        
    Case vbCancel
    End Select

    Sheets("General Data").Range("B4").Select
    
End Sub

Debaser · #5 02-22-2016, 06:49 AM

Your original code doesn't work for me on 2010 unless I uncomment the On Error Resume Next line.

wpryan · #6 02-25-2016, 10:51 PM

Thanks for your reply. In this case, when I changed the code to reference named ranges, I had to unlock the worksheets prior to clearing the named ranges. Now it's working...

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copy data from certain Cells in new sheet	zain_shaikh	Excel Programming	1	02-11-2015 07:35 PM
How to populate cells in Sheet2 with Data Source query using cell data from Sheet1	bobznkazoo	Excel	2	03-27-2014 11:14 AM
data in some cells moving	Sherriann	Excel	1	08-07-2013 06:52 AM
How can I fill the below emty cells with above cell data?	Learner7	Excel	8	06-28-2011 12:10 PM
How to combine different cells data in one cell?	Learner7	Excel	1	07-28-2010 12:07 PM