Need to clear contents on protected sheet

Recovery

New Member
Joined
Aug 31, 2014
Messages
5
I have an invoice I am working on for a local business it needs to be protected so the employees do not alter the formulas but I also have a clear contents function. When I protect the sheet the clear contents gives errors I assume the protection is preventing the clear contents from running. I have some code I have been piecing together from different forums and I think I am close to getting it right I just need a little help. I have a VB button on the worksheet that the user presses to clear contents and save a copy of the invoice while generating another invoice number as well. I keep getting "compile error: Invalid or unqualified reference" Any help would be greatly appreciated.

The name of the work sheet is: TMR MASTER INVOICE

The code I am using:

Sub NextInvoice()
Range("D6").Value = Range("D6").Value + 1
.Unprotect Password:="reco1966"
Range("A9:A12,A14:C21,A27:C34,D7,D9,D11,D39").ClearContents
.Protect Password:="reco1966"

End Sub




___________________________________________________________________
Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "C:\TMR\Inv" & Range("D6").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I have tried this but I'm still getting error of cell being protected and clear.contents is not running

Sub NextInvoice()
Range("D6").Value = Range("D6").Value + 1
ThisWorkbook.Unprotect Password:="reco1966"
Range("A9:A12,A14:C21,A27:C34,D7,D9,D11,D39").ClearContents
ThisWorkbook.Protect Password:="reco1966"
End Sub
 
Upvote 0
Is the D6 that's being added to on the same worksheet as the cells being cleared. (If so, why isn't that line erroring)
If so you could use code like this. (Its more likely that the sheet is protected than the workbook protected)

Code:
With ActiveSheet
    .Range("D6").Value = .Range("D6").Value + 1
    .Unprotect Password:="reco1966"
    .Range("A9:A12,A14:C21,A27:C34,D7,D9,D11,D39").ClearContents
    .Protect Password:="reco1966"
End With
 
Upvote 0
Is the D6 that's being added to on the same worksheet as the cells being cleared. (If so, why isn't that line erroring)
If so you could use code like this. (Its more likely that the sheet is protected than the workbook protected)

Code:
With ActiveSheet
    .Range("D6").Value = .Range("D6").Value + 1
    .Unprotect Password:="reco1966"
    .Range("A9:A12,A14:C21,A27:C34,D7,D9,D11,D39").ClearContents
    .Protect Password:="reco1966"
End With

First thank you for your help,

I am using the code you supplied but now I am getting a "COMPILE ERROR: INVALID OUTSIDE PROCEDURE" highlighted in blue on the With Active Sheet line..
 
Upvote 0
Why are you clearing the contents of locked cells? You should only be clearing the cells in which the user can enter data and those cells need to be unlocked.
 
Upvote 0

Forum statistics

Threads
1,223,390
Messages
6,171,807
Members
452,426
Latest member
cmachael

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
Back
Top