RatherBeRidi
06-29-2012, 07:46 AM
I don't think this should be very difficult, but I have spent a lot of time trying different code. I can't work out how to get the value in one cell, then unhide and activate a worksheet.
Specifically - if the response in cell D95 is No, then get the value in cell C3 and unhide and activate the worksheet with the same name. The code below will unprotect and unlock cell D96 if D95 is Yes, but when No is selected in D95 the sheet is not activated and I do not receive any error message. It seems that the second If statement is ignored and it ends?
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("D95").Value = "No" Then
If Not Intersect(Target, Me.Range("C2")) Is Nothing Then
Worksheets(Target.Offset(0, 1).Value).Activate
End If
Else
ActiveSheet.Unprotect
Me.Range("D96").Locked = False
ActiveSheet.Protect
End If
End Sub
Specifically - if the response in cell D95 is No, then get the value in cell C3 and unhide and activate the worksheet with the same name. The code below will unprotect and unlock cell D96 if D95 is Yes, but when No is selected in D95 the sheet is not activated and I do not receive any error message. It seems that the second If statement is ignored and it ends?
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("D95").Value = "No" Then
If Not Intersect(Target, Me.Range("C2")) Is Nothing Then
Worksheets(Target.Offset(0, 1).Value).Activate
End If
Else
ActiveSheet.Unprotect
Me.Range("D96").Locked = False
ActiveSheet.Protect
End If
End Sub