Forum Discussion
Default Cell value & Protecting a worksheet that uses macros
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
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
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 SubI 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.
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