If a cell is NA then lock sheet - VBA

[Jaspal]

Hello there,
My Excel sheet is protected, I have got two drop downs in cells for the end users to select.
If it is a valid combination then it creates a unique string in Cell D6 below and adds a row in the relevant section and protects the sheet again - Macro below, but if the selection is invalid it unprotects my sheet. How can I keep it locked.
Cell D6 throws NA error for an invalid combination, how can I tweak my Macro to lock the sheet if D6 is NA?

Thank you in advance.

[VBA]Sub Movingrowsdown()
'Movingrowsdown Macro
' moves to the row of relevant account and account class
'Keyboard Shortcut: Ctrl+Shift+C
Application.ScreenUpdating = False
Call UnprotectSheet(ActiveSheet.Name)
Dim r As Integer
Range("A:E").EntireColumn.Hidden = False
r = Range("d6").Value
Cells(r, 1).Select
ActiveCell.EntireRow.Insert
Range("B" & r - 1 & ":I" & r - 1).Copy
Range("B" & r).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("K" & r & ":O" & r).Locked = False
Range("W" & r).Locked = False
Application.ScreenUpdating = True
Range("A:E").EntireColumn.Hidden = True
Range("K" & r).Activate
Call ProtectSheet(ActiveSheet.Name)
End Sub[/VBA]

[joshcas]

Hey Jaspal ,

Well if it’s only to look for NA in D6 , would be somethinglike this:

[vba]
Sub Test()
If Worksheet(“Mine”).Range(“D6”).Value = “NA” Then
‘ Do this
End If
End Sub
[/vba]

Instead of the dropdowns I’ll suggest something that I designedto accomplish the protection algorithm

pass.zip

[SamT]

[vba]Sub Movingrowsdown()
'
' Movingrowsdown Macro
' moves to the row of relevant account and account class
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Application.ScreenUpdating = False

Call UnprotectSheet(ActiveSheet.Name)
Dim r As Variant
Range("A:E").EntireColumn.Hidden = False
r = Range("d6").Value
If r = "N/A" Then GoTo SkipIt
Cells(r, 1).Select
ActiveCell.EntireRow.Insert
Range("B" & r - 1 & ":I" & r - 1).Copy
Range("B" & r).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("K" & r & ":O" & r).Locked = False
Range("W" & r).Locked = False
SkipIt:
Range("A:E").EntireColumn.Hidden = True
Range("K" & r).Activate
Call ProtectSheet(ActiveSheet.Name)
Application.ScreenUpdating = True

End Sub[/vba]

[joshcas]

Here is a screenshot :

Untitled.jpg

[Jaspal]

Thank you kindly SamT. I will try the code that you provided!
Have a great weekend ahead.

[Jaspal]

Hello SamT,

I tried the above code, but the sheet unptrotects as soon as I hit the "End" or "Debug" button. Is it because as part of the Macro it unhides columns A:E, because whenever an invalid combination is selected and D6 has a "#N/A" error this macro leaves columns A:E unhidden and the sheet unprotected.

[SamT]

Test for Bad value in Range "r". if value of r is "#N/A", then skip all code until after line label "skipit:"
[vba] If r = "N/A" Then Goto SkipIt

'also try
If r.Text = "N/A" Then Goto SkipIt
[/vba]