PDA

View Full Version : If a cell is NA then lock sheet - VBA



Jaspal
06-21-2013, 07:58 AM
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.

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

joshcas
06-21-2013, 11:43 AM
Hey Jaspal ,

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


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


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

10165

SamT
06-21-2013, 11:43 AM
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

joshcas
06-21-2013, 11:47 AM
Here is a screenshot :

10166

Jaspal
06-21-2013, 03:03 PM
Thank you kindly SamT. I will try the code that you provided!
Have a great weekend ahead.

Jaspal
06-25-2013, 02:16 AM
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
06-25-2013, 07:27 AM
Test for Bad value in Range "r". if value of r is "#N/A", then skip all code until after line label "skipit:"
If r = "N/A" Then Goto SkipIt

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