Protecting Spreadsheet and VBA

[Grammarjunkie]

Sorry to bother,
but does protecting the spreadsheet and locking cells affect the VBA code results? They've been working fine, but now, when I've protected the spreadsheet, I get a runtime error.

The only VBA codes I have are to hide rows when certain fields read certain things. So is the VBA disgruntled because the spreadsheet is locked and the rows can't unhide?

I'm going to try Format - Protect - Uncheck Lock for those hidden rows? I hope that helps.
Any advice or help is appreciated.

Edit: Nope. That didn't help. Haha.
Hm.

 

[dave3009]

Hi

You can unprotect the cells during macro execution.

Sheets("TheSheet").Unprotect "PasswordIfSet"

'Your code

Sheets("TheSheet").Protect "PasswordIfSet"

HTH

Dave

 

[Grammarjunkie]

Hi

You can unprotect the cells during macro execution.

Sheets("TheSheet").Unprotect "PasswordIfSet"

'Your code

Sheets("TheSheet").Protect "PasswordIfSet"

HTH

Dave

Hi, Dave,

I appreciate you stopping. But unless I'm doing something wrong (highly possible), that code you provided isn't stopping the issue. Whether my sheet is protected or unprotected, with that code in, I get a debug error. I placed the first sentence right before my code (right before Application.Screenupdating = False) and I placed the second sentence at the end right before End Sub. Was this erroneous?

Any further help is appreciated.

 

[dave3009]

Can we see your code please, might help figure out what the issue is.

 

[Grammarjunkie]

Can we see your code please, might help figure out what the issue is.

Private Sub Worksheet_Change(ByVal Target As Range)


Application.ScreenUpdating = False

If Range("J3") = "TPO" Then
    Rows("14:15").Hidden = False
Else
    Rows("14:15").Hidden = True
End If

If (Range("D6") <> "") And (Range("E18") <> "") And (Range("D5") <> Range("E17")) Then
        Rows("39:41").Hidden = False
Else
        Rows("39:41").Hidden = True
End If

If Range("F45") = "N" Then
    Rows("46:48").Hidden = True
Else
    Rows("46:48").Hidden = False
End If

If Range("E19") = "" Then
        Rows("49:52").Hidden = True
Else
        Rows("49:52").Hidden = False
End If

If Range("D13") = "Purchase" Then
    Rows("59").Hidden = False
Else
    Rows("59").Hidden = True
End If

If Range("E22") = "" Then
    Rows("53:56").Hidden = True
Else
    Rows("53:56").Hidden = False
End If

If Range("D13") = "Purchase" Then
    Rows("104:113").Hidden = False
Else
    Rows("104:113").Hidden = True
End If

If Range("J3") = "TPO" Then
    Rows("118:121").Hidden = False
Else
    Rows("118:121").Hidden = True
End If

If Range("D13") = "Purchase" Then
    Rows("127:130").Hidden = False
Else
    Rows("127:130").Hidden = True
End If

If Range("J3") = "TPO" Then
    Rows("126").Hidden = False
Else
    Rows("126").Hidden = True
End If

If Range("F95") = "Y" Then
    Rows("96:97").Hidden = False
Else
    Rows("96:97").Hidden = True
End If

If Range("F89") = "Y" Then
    Rows("90:95").Hidden = False
Else
    Rows("90:95").Hidden = True
End If

If Range("F75") = "Y" Then
    Rows("76").Hidden = False
Else
    Rows("76").Hidden = True
End If

If Range("F75") = "N" Then
    Rows("77:80").Hidden = False
Else
    Rows("77:80").Hidden = True
End If

Application.ScreenUpdating = True



End Sub

 

[Grammarjunkie]

Can we see your code please, might help figure out what the issue is.

Sorry that I didn't share that information before. Honestly, I know practically nothing when it comes to VBA codes. So I'm still wary on what is really needed or not needed to determine solutions.

 

[dave3009]

I've put the code in my WB and I can't recreate the error.

I think partially because you haven't said what runtime error it is, it'll have a code like 1004 and a descriptor like "Unable to do the something" (but better)

Are you running this as a worksheet change event from a worksheet module or are you trying to run it as a standalone macro in a standard module?

Private Sub Worksheet_Change(ByVal Target As Range)


ActiveSheet.Unprotect
Application.ScreenUpdating = False


If Range("J3") = "TPO" Then
    Rows("14:15").Hidden = False
Else
    Rows("14:15").Hidden = True
End If


If (Range("D6") <> "") And (Range("E18") <> "") And (Range("D5") <> Range("E17")) Then
        Rows("39:41").Hidden = False
Else
        Rows("39:41").Hidden = True
End If


If Range("F45") = "N" Then
    Rows("46:48").Hidden = True
Else
    Rows("46:48").Hidden = False
End If


If Range("E19") = "" Then
        Rows("49:52").Hidden = True
Else
        Rows("49:52").Hidden = False
End If


If Range("D13") = "Purchase" Then
    Rows("59").Hidden = False
Else
    Rows("59").Hidden = True
End If


If Range("E22") = "" Then
    Rows("53:56").Hidden = True
Else
    Rows("53:56").Hidden = False
End If


If Range("D13") = "Purchase" Then
    Rows("104:113").Hidden = False
Else
    Rows("104:113").Hidden = True
End If


If Range("J3") = "TPO" Then
    Rows("118:121").Hidden = False
Else
    Rows("118:121").Hidden = True
End If


If Range("D13") = "Purchase" Then
    Rows("127:130").Hidden = False
Else
    Rows("127:130").Hidden = True
End If


If Range("J3") = "TPO" Then
    Rows("126").Hidden = False
Else
    Rows("126").Hidden = True
End If


If Range("F95") = "Y" Then
    Rows("96:97").Hidden = False
Else
    Rows("96:97").Hidden = True
End If


If Range("F89") = "Y" Then
    Rows("90:95").Hidden = False
Else
    Rows("90:95").Hidden = True
End If


If Range("F75") = "Y" Then
    Rows("76").Hidden = False
Else
    Rows("76").Hidden = True
End If


If Range("F75") = "N" Then
    Rows("77:80").Hidden = False
Else
    Rows("77:80").Hidden = True
End If


Application.ScreenUpdating = True
ActiveSheet.Protect




End Sub

The above works for me btw