Forum Discussion

Orii120's avatar
Orii120
Copper Contributor
Apr 08, 2019

Default Cell value & Protecting a worksheet that uses macros

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.

 

excel
Macros and VBA

5 Replies

  • JWR1138's avatar
    JWR1138
    Iron Contributor
    Apr 09, 2019

    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

     

    • Orii120's avatar
      Orii120
      Copper Contributor
      Apr 18, 2019

      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

      • JWR1138's avatar
        JWR1138
        Iron Contributor
        Apr 18, 2019

        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

         

Resources