Data Validation

[andrewb90]

I have a simple macro that hides a few rows. When those rows are hidden, I need a way to restrict any data entry until those rows have been unhidden. I am thinking data validation, but I have no idea how I would make that work.

When rows 16:17 are hidden, I need to restrict any entry in cells D12:O15

Any ideas would be greatly appreciated.

Andrew

 

[Snakehips]

Andrew,

Perhaps something like this......

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A16:A17").EntireRow.Hidden = False Then Exit Sub
If Intersect(Target, Range("D12:O15")) Is Nothing Then Exit Sub
MsgBox "Restricted Data Entry"  edit to suit or remove
Range("A1").Select  ' edit to suit
End Sub

Hope that helps.

 

[Snakehips]

Andrew,

Another thought

If you do not have any conflicting issues with protecting the sheet in any way then perhaps something like this code which will toggle the hiding and unhiding or rows 16 & 17 and will lock / unlock and protect cells D12:O1
If no other cells are to be locked then select all cells and unlock before running the code. Make sure you start with rows not hidden and cells not locked before you run code.

Sub HuHRows()
ActiveSheet.Unprotect
Range("A16:A17").EntireRow.Hidden = Not Range("A16:A17").EntireRow.Hidden
Range("D12:O15").Locked = Not Range("D12:O15").Locked
ActiveSheet.Protect
End Sub

Also note that you cannot have both these code enabled at the same time!!