onetel_comms
02-26-2015, 06:47 PM
Hi All,
I am hoping some one can help. I am new to VB in excel and I have been tasked with creating a document for work purposes. The idea is to create a spread sheet with multiple drop down options which when selected hide certain rows.
I have successfully created the drop down boxes, with "YES", "NO" and "-" as the options however I keep running into issues when I try to program the VB side of things. The issue is that some boxes when selected do what they are suppose to do however other don't do anything. Its really strange to me as when I unhide the whole sheet and select each option individually it seems to work, but when I save the sheet and reopen it I get the same issue.
Can anyone help? I have listed the VB code below. Any help will be greatly appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Target.Parent.Range("B63")
Select Case Target.Address
Case "$B$63"
If Target.Value = "-" Then
Rows("64:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("64:307").Hidden = True
MsgBox "Please run the command before proceeding"
Else
Rows("64:78").Hidden = False
End If
End Select
Dim Rng2 As Range
Set Rng2 = Target.Parent.Range("B78")
Select Case Target.Address
Case "$B$78"
If Target.Value = "-" Then
Rows("79:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("79:307").Hidden = True
MsgBox "Please run the command before proceeding"
Else
Rows("79:127").Hidden = False
End If
End Select
Dim Rng3 As Range
Set Rng3 = Target.Parent.Range("B127")
Select Case Target.Address
Case "$B$127"
If Target.Value = "-" Then
Rows("128:307").Hidden = True
ElseIf Target.Value = "YES" Then
Rows("128:130").Hidden = False
Else
Rows("128:307").Hidden = True
MsgBox "Please DO NOT continue."
End If
End Select
Dim Rng4 As Range
Set Rng4 = Target.Parent.Range("B130")
Select Case Target.Address
Case "$B$130"
If Target.Value = "-" Then
Rows("131:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("131:143").Hidden = True
Rows("187:307").Hidden = True
Else
Rows("131:186").Hidden = False
MsgBox "Please check with support"
End If
End Select
Dim Rng5 As Range
Set Rng5 = Target.Parent.Range("B186")
Select Case Target.Address
Case "$B$186"
If Target.Value = "-" Then
Rows("187:307").Hidden = True
ElseIf Target.Value = "YES" Then
Rows("187:195").Hidden = True
Rows("207:227").Hidden = True
Rows("280:306").Hidden = True
Else
Rows("187:195").Hidden = False
Rows("207:227").Hidden = False
Rows("280:306").Hidden = False
End If
End Select
Dim Rng6 As Range
Set Rng6 = Target.Parent.Range("B195")
Select Case Target.Address
Case "$B$195"
If Target.Value = "-" Then
Rows("216:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("216:222").Hidden = True
Rows("293:306").Hidden = True
Else
Rows("216:222").Hidden = False
Rows("293:306").Hidden = False
End If
End Select
End Sub
I am hoping some one can help. I am new to VB in excel and I have been tasked with creating a document for work purposes. The idea is to create a spread sheet with multiple drop down options which when selected hide certain rows.
I have successfully created the drop down boxes, with "YES", "NO" and "-" as the options however I keep running into issues when I try to program the VB side of things. The issue is that some boxes when selected do what they are suppose to do however other don't do anything. Its really strange to me as when I unhide the whole sheet and select each option individually it seems to work, but when I save the sheet and reopen it I get the same issue.
Can anyone help? I have listed the VB code below. Any help will be greatly appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Target.Parent.Range("B63")
Select Case Target.Address
Case "$B$63"
If Target.Value = "-" Then
Rows("64:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("64:307").Hidden = True
MsgBox "Please run the command before proceeding"
Else
Rows("64:78").Hidden = False
End If
End Select
Dim Rng2 As Range
Set Rng2 = Target.Parent.Range("B78")
Select Case Target.Address
Case "$B$78"
If Target.Value = "-" Then
Rows("79:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("79:307").Hidden = True
MsgBox "Please run the command before proceeding"
Else
Rows("79:127").Hidden = False
End If
End Select
Dim Rng3 As Range
Set Rng3 = Target.Parent.Range("B127")
Select Case Target.Address
Case "$B$127"
If Target.Value = "-" Then
Rows("128:307").Hidden = True
ElseIf Target.Value = "YES" Then
Rows("128:130").Hidden = False
Else
Rows("128:307").Hidden = True
MsgBox "Please DO NOT continue."
End If
End Select
Dim Rng4 As Range
Set Rng4 = Target.Parent.Range("B130")
Select Case Target.Address
Case "$B$130"
If Target.Value = "-" Then
Rows("131:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("131:143").Hidden = True
Rows("187:307").Hidden = True
Else
Rows("131:186").Hidden = False
MsgBox "Please check with support"
End If
End Select
Dim Rng5 As Range
Set Rng5 = Target.Parent.Range("B186")
Select Case Target.Address
Case "$B$186"
If Target.Value = "-" Then
Rows("187:307").Hidden = True
ElseIf Target.Value = "YES" Then
Rows("187:195").Hidden = True
Rows("207:227").Hidden = True
Rows("280:306").Hidden = True
Else
Rows("187:195").Hidden = False
Rows("207:227").Hidden = False
Rows("280:306").Hidden = False
End If
End Select
Dim Rng6 As Range
Set Rng6 = Target.Parent.Range("B195")
Select Case Target.Address
Case "$B$195"
If Target.Value = "-" Then
Rows("216:307").Hidden = True
ElseIf Target.Value = "NO" Then
Rows("216:222").Hidden = True
Rows("293:306").Hidden = True
Else
Rows("216:222").Hidden = False
Rows("293:306").Hidden = False
End If
End Select
End Sub