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
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