Default Cell value & Protecting a worksheet that uses macros

[Orii120]

Hi All,

I'm back again working on this meal planner. Thanks to the assistance of some users here I have been able to get it up and running for the most part. However, I am stuck on two things. 

I would like a range of cells to have a default value so that when data is deleted from those cells the sheet will reset the default to 1 instead of becoming blank.

I would like all of the cells with the value of one in column C to reset to a default value when erased (minus the ones that say grams).

I would also like to be able to protect this workbook from editing in all cells on sheet 1 bar columns A, B and K2, L2, N2 and M2 as these cells will be used to input data from the user. The problem is that I have a Macro running to clear columns A and B so using protected mode seems to be out of the question. 

Any Ideas?

Thanks.

[#2]

Hi, For the default value you could do this (Needs to be on the code for the appropriate sheet):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangeCells As Range
Set ChangeCells = Range("C2:C10")
If Not Application.Intersect(ChangeCells, Range(Target.Address)) Is Nothing Then
If Target.Value = "" Then
Target.Value = 1
End If
End If
End Sub

Just change the change cells to the appropriate range.

For the protected workbook part you can unlock via the vba and then lock at the end of your macro, For workbook protection:

ActiveWorkbook.Unprotect Password:="Password"

ActiveWorkbook.Protect Password:="Password"

For worksheet protection:

ActiveSheet.Unprotect Password:="Password"

ActiveSheet.Protect Password:="Password"

Or better yet, you can lock it down only for the user interface giving VBA full Access:

 ActiveSheet.Protect Password:="Password", UserInterFaceOnly:=True
ActiveWorkbook.Protect Password:="Password", UserInterFaceOnly:=True

[#3]

@JWR1138 

Hi there, thanks for your reply. I just have a couple of questions.

I've implemented your code for the default value however it seems to only work when selecting and deleting one cell at a time. Is there any way to have this work when selecting multiple cells in the range?

Also with the user interface only protection I keep getting a compile error: invalid outside procedure message which then points to the password as being an issue. Any ideas on this one?

Thanks

[#4]

Hi, Sorry, I didn't think about deleting multiple cells at once, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangeCells As Range
Dim cel As Range
Set ChangeCells = Range("C2:C10")
If Not Application.Intersect(ChangeCells, Range(Target.Address)) Is Nothing Then
For Each cel In Target
If Not Application.Intersect(cel, ChangeCells) Is Nothing Then
If cel.Value = "" Then
cel.Value = 1
End If
End If
Next cel
End If
End Sub

[#5]

I may have you given some bad advice on this: 

ActiveWorkbook.Protect Password:="Password", UserInterFaceOnly:=True

as that doesn't seem to actually work, I thought it did, sorry. 

Protecting the sheets that way works for me using: 

ActiveSheet.Protect Password:="Password", UserInterFaceOnly:=True

though, how are you running that line? It needs to be stuck into a subroutine or run through the intermediate window (I'd just do it through the intermediate window as I presume you'd just run it once prior to distribution). Let me know how you are trying to utilize this and we can go from there. 

[#6]

@JWR1138 

Hi again, First off the default cell code above worked so thanks for that.

What I'm trying to do with the protection is making it so that only certain cells are editable (highlighted below) and the rest of the sheet is uneditable (to protect formulae and the functionality of the sheet). The thing is I have a clear the form button which will clear everything in columns B & C. This uses a Macro to do so, so I can't protect using the built-in functionality. It sounds like locking down the UI might be the best way to go as long as the user can still use the drop down box in column B and edit the data in column C. C