Hello I have hidden 19 worksheets . Using a new worksheet, I Created a
form and on cell B28 I have a dropdown list naming the19 differend kinds of
agreements. What I want is when the agreement name is selected on cell B 28
for that worksheet to unhide.
This is my code(so far)
Private Sub Worksheet_Change(ByVal Target As Range)
With Target

If Me.Range("B28").Value = "" Then
Worksheets("StandardAgreement").Visible = xlSheetVeryHidden
Else
Worksheets("StandardAgreement").Visible = xlSheetVisible
End If
End With

The above code works ...the next agreement on the list is 3 year Agreement
..so I added the following to the code above


If Me.Range("B28").Value = "" Then
Worksheets("3YearAgreement").Visible = xlSheetVeryHidden
Else
Worksheets("3YearAgreement").Visible = xlSheetVisible

End If
End If

End With
This does not work, How can I add all 19 agreements to the code so that the
whatever agreement is selected in cell B28 the worksheet unhides
thanks for helping me!

Hello


Private Sub Worksheet_Change(ByVal Target As Range)

With Target

If .Address = "$B$28" Then

Select Case .Value

'ADJUST - fill in the 19 possibilities

'Case "": Sheets("StandardAgreement").Visible =
xlSheetVeryHidden
'Case "another option": Sheets("3YearAgreement").Visible =
xlSheetVisible
'Case "yet another option": Sheets("...").Visible =
xlSheetVisible
'...

End Select

End If

End With

End Sub



--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Marilyn" wrote:

Hello I have hidden 19 worksheets . Using a new worksheet, I Created a
form and on cell B28 I have a dropdown list naming the19 differend kinds of
agreements. What I want is when the agreement name is selected on cell B 28
for that worksheet to unhide.
This is my code(so far)
Private Sub Worksheet_Change(ByVal Target As Range)
With Target

If Me.Range("B28").Value = "" Then
Worksheets("StandardAgreement").Visible = xlSheetVeryHidden
Else
Worksheets("StandardAgreement").Visible = xlSheetVisible
End If
End With

The above code works ...the next agreement on the list is 3 year Agreement
.so I added the following to the code above


If Me.Range("B28").Value = "" Then
Worksheets("3YearAgreement").Visible = xlSheetVeryHidden
Else
Worksheets("3YearAgreement").Visible = xlSheetVisible

End If
End If

End With
This does not work, How can I add all 19 agreements to the code so that the
whatever agreement is selected in cell B28 the worksheet unhides
thanks for helping me!

Wigi Thank you
I'm a novice at this, I need simplier instructions Let me rephrasea.
When a person selects the agreement type from cell B28 (in the form
worksheet)then that agreement worksheet should unhide. At any given time
there should not be more that 2 worksheets unhiden.
With your code I can unhide the agreement type worksheet, but if a selected
a different agreement type the previous agreement type I had selected does
not hide
Again thanks
"Wigi" wrote:

Hello


Private Sub Worksheet_Change(ByVal Target As Range)

With Target

If .Address = "$B$28" Then

Select Case .Value

'ADJUST - fill in the 19 possibilities

'Case "": Sheets("StandardAgreement").Visible =
xlSheetVeryHidden
'Case "another option": Sheets("3YearAgreement").Visible =
xlSheetVisible
'Case "yet another option": Sheets("...").Visible =
xlSheetVisible
'...

End Select

End If

End With

End Sub



--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Marilyn" wrote:

Hello I have hidden 19 worksheets . Using a new worksheet, I Created a
form and on cell B28 I have a dropdown list naming the19 differend kinds of
agreements. What I want is when the agreement name is selected on cell B 28
for that worksheet to unhide.
This is my code(so far)
Private Sub Worksheet_Change(ByVal Target As Range)
With Target

If Me.Range("B28").Value = "" Then
Worksheets("StandardAgreement").Visible = xlSheetVeryHidden
Else
Worksheets("StandardAgreement").Visible = xlSheetVisible
End If
End With

The above code works ...the next agreement on the list is 3 year Agreement
.so I added the following to the code above


If Me.Range("B28").Value = "" Then
Worksheets("3YearAgreement").Visible = xlSheetVeryHidden
Else
Worksheets("3YearAgreement").Visible = xlSheetVisible

End If
End If

End With
This does not work, How can I add all 19 agreements to the code so that the
whatever agreement is selected in cell B28 the worksheet unhides
thanks for helping me!

Hello
This is what my code looks like. the code will unhide any of the sheets but
it only hides the first case "Standard Agreement" Help Please and thank
you

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$B$28" Then
Select Case .Value

'ADJUST - fill in the 19 possibilities

Case "": Sheets("StandardAgreement").Visible = xlSheetVeryHidden
Case "": Sheets("3YearAgreement").Visible = xlSheetVeryHidden
Case "": Sheets("1YearAgreement").Visible = xlSheetVeryHidden
Case "": Sheets("5YearAgreement").Visible = xlSheetVeryHidden

Case "StandardAgreement": Sheets("StandardAgreement").Visible =
xlSheetVisible
Case "3YearAgreement": Sheets("3YearAgreement").Visible =
xlSheetVisible
Case "1YearAgreement": Sheets("1YearAgreement").Visible =
xlSheetVisible
Case "5YearAgreement": Sheets("5YearAgreement").Visible =
xlSheetVisible


'...

End Select

End If

End With

End Sub fworks SO this is


"Marilyn" wrote:

Hello I have hidden 19 worksheets . Using a new worksheet, I Created a
form and on cell B28 I have a dropdown list naming the19 differend kinds of
agreements. What I want is when the agreement name is selected on cell B 28
for that worksheet to unhide.
This is my code(so far)
Private Sub Worksheet_Change(ByVal Target As Range)
With Target

If Me.Range("B28").Value = "" Then
Worksheets("StandardAgreement").Visible = xlSheetVeryHidden
Else
Worksheets("StandardAgreement").Visible = xlSheetVisible
End If
End With

The above code works ...the next agreement on the list is 3 year Agreement
.so I added the following to the code above


If Me.Range("B28").Value = "" Then
Worksheets("3YearAgreement").Visible = xlSheetVeryHidden
Else
Worksheets("3YearAgreement").Visible = xlSheetVisible

End If
End If

End With
This does not work, How can I add all 19 agreements to the code so that the
whatever agreement is selected in cell B28 the worksheet unhides
thanks for helping me!