ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Unhide roes in a different worksheet - My code is not working (https://www.excelbanter.com/excel-programming/392885-unhide-roes-different-worksheet-my-code-not-working.html)

Ram[_5_]
Unhide roes in a different worksheet - My code is not working
 
Hi,

Basing on values 1to 5 given in a dropdown, I want different rows to
unhide and hide accordingly. I have this code it does not work at
all!!!!

Private Sub Page()
Dim conf
conf = Sheet1.Range("Confirmation").Value

If conf = 1 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
End If

If conf = 2 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
End If
If conf = 3 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
End If
If conf = 4 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
Sheet6.Rows("134:136").EntireRow.Hidden = False
End If
If conf = 5 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
Sheet6.Rows("134:136").EntireRow.Hidden = False
Sheet6.Rows("178:180").EntireRow.Hidden = False
End If

If conf = "Select one" Then
Sheet6.Rows("4:6").EntireRow.Hidden = True
Sheet6.Rows("48:50").EntireRow.Hidden = True
Sheet6.Rows("91:93").EntireRow.Hidden = True
Sheet6.Rows("134:136").EntireRow.Hidden = True
Sheet6.Rows("178:180").EntireRow.Hidden = True
End If

End Sub

Some one please help me make modifications to this code or give me
another work around code.
Thanks a lot
Regards,
SRC

Gary Keramidas
Unhide roes in a different worksheet - My code is not working
 
give this a try

Private Sub Page()
Dim ws As Worksheet
Dim ws6 As Worksheet
Dim conf As Variant
Set ws = Worksheets("Sheet1")
Set ws6 = Worksheets("Sheet6")
conf = ws.Range("Confirmation").Value

Select Case conf

Case 1
ws6.Rows("4:6").EntireRow.Hidden = False

Case 2
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False

Case 3
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False

Case 4
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False
ws6.Rows("134:136").EntireRow.Hidden = False

Case 5
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False
ws6.Rows("134:136").EntireRow.Hidden = False
ws6.Rows("178:180").EntireRow.Hidden = False

Case Else
If LCase(conf) = "select one" Then
ws6.Rows("4:6").EntireRow.Hidden = True
ws6.Rows("48:50").EntireRow.Hidden = True
ws6.Rows("91:93").EntireRow.Hidden = True
ws6.Rows("134:136").EntireRow.Hidden = True
ws6.Rows("178:180").EntireRow.Hidden = True
End If
End Select
End Sub

--


Gary


"Ram" wrote in message
oups.com...
Hi,

Basing on values 1to 5 given in a dropdown, I want different rows to
unhide and hide accordingly. I have this code it does not work at
all!!!!

Private Sub Page()
Dim conf
conf = Sheet1.Range("Confirmation").Value

If conf = 1 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
End If

If conf = 2 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
End If
If conf = 3 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
End If
If conf = 4 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
Sheet6.Rows("134:136").EntireRow.Hidden = False
End If
If conf = 5 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
Sheet6.Rows("134:136").EntireRow.Hidden = False
Sheet6.Rows("178:180").EntireRow.Hidden = False
End If

If conf = "Select one" Then
Sheet6.Rows("4:6").EntireRow.Hidden = True
Sheet6.Rows("48:50").EntireRow.Hidden = True
Sheet6.Rows("91:93").EntireRow.Hidden = True
Sheet6.Rows("134:136").EntireRow.Hidden = True
Sheet6.Rows("178:180").EntireRow.Hidden = True
End If

End Sub

Some one please help me make modifications to this code or give me
another work around code.
Thanks a lot
Regards,
SRC

Ram[_5_]
Unhide roes in a different worksheet - My code is not working
 
On Jul 8, 11:35 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
give this a try

Private Sub Page()
Dim ws As Worksheet
Dim ws6 As Worksheet
Dim conf As Variant
Set ws = Worksheets("Sheet1")
Set ws6 = Worksheets("Sheet6")
conf = ws.Range("Confirmation").Value

Select Case conf

Case 1
ws6.Rows("4:6").EntireRow.Hidden = False

Case 2
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False

Case 3
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False

Case 4
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False
ws6.Rows("134:136").EntireRow.Hidden = False

Case 5
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False
ws6.Rows("134:136").EntireRow.Hidden = False
ws6.Rows("178:180").EntireRow.Hidden = False

Case Else
If LCase(conf) = "select one" Then
ws6.Rows("4:6").EntireRow.Hidden = True
ws6.Rows("48:50").EntireRow.Hidden = True
ws6.Rows("91:93").EntireRow.Hidden = True
ws6.Rows("134:136").EntireRow.Hidden = True
ws6.Rows("178:180").EntireRow.Hidden = True
End If
End Select
End Sub

--

Gary

"Ram" wrote in message

oups.com...



Hi,

Basing on values 1to 5 given in a dropdown, I want different rows to
unhide and hide accordingly. I have this code it does not work at
all!!!!

Private Sub Page()
Dim conf
conf = Sheet1.Range("Confirmation").Value

If conf = 1 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
End If

If conf = 2 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
End If
If conf = 3 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
End If
If conf = 4 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
Sheet6.Rows("134:136").EntireRow.Hidden = False
End If
If conf = 5 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
Sheet6.Rows("134:136").EntireRow.Hidden = False
Sheet6.Rows("178:180").EntireRow.Hidden = False
End If

If conf = "Select one" Then
Sheet6.Rows("4:6").EntireRow.Hidden = True
Sheet6.Rows("48:50").EntireRow.Hidden = True
Sheet6.Rows("91:93").EntireRow.Hidden = True
Sheet6.Rows("134:136").EntireRow.Hidden = True
Sheet6.Rows("178:180").EntireRow.Hidden = True
End If

End Sub

Some one please help me make modifications to this code or give me
another work around code.
Thanks a lot
Regards,
SRC- Hide quoted text -

- Show quoted text -

Hi Gary,

Thanks a bunch, this works just the way i wanted.

Regards,
SRC

NickHK
Unhide roes in a different worksheet - My code is not working
 
If you can change you design slightly to give a constant spacing between
these sections (instead of the 43/44 that you have at the moment), you could
do it in a loop:

Private Sub CommandButton2_Click()
Const SPACING As Long = 44

ShowHide Sheet1.Range("Confirmation").Value, SPACING

End Sub

Private Sub ShowHide(SectionCount As Variant, RowSpacing As Long)
Dim i As Long
Dim ShowRows As Boolean
Dim Counter As Long

If IsNumeric(SectionCount) Then
Counter = SectionCount
ShowRows = False
Else
Counter = 5
ShowRows = True
End If

For i = 1 To Counter
Sheet6.Rows(4 + RowSpacing * (i - 1) & ":" & 6 + RowSpacing * (i -
1)).EntireRow.Hidden = ShowRows
'Debug.Print Sheet6.Rows(4 + RowSpacing * (i - 1) & ":" & 6 + RowSpacing
* (i - 1)).Address
Next

End Sub

NickHK

"Ram" wrote in message
oups.com...
Hi,

Basing on values 1to 5 given in a dropdown, I want different rows to
unhide and hide accordingly. I have this code it does not work at
all!!!!

Private Sub Page()
Dim conf
conf = Sheet1.Range("Confirmation").Value

If conf = 1 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
End If

If conf = 2 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
End If
If conf = 3 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
End If
If conf = 4 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
Sheet6.Rows("134:136").EntireRow.Hidden = False
End If
If conf = 5 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
Sheet6.Rows("134:136").EntireRow.Hidden = False
Sheet6.Rows("178:180").EntireRow.Hidden = False
End If

If conf = "Select one" Then
Sheet6.Rows("4:6").EntireRow.Hidden = True
Sheet6.Rows("48:50").EntireRow.Hidden = True
Sheet6.Rows("91:93").EntireRow.Hidden = True
Sheet6.Rows("134:136").EntireRow.Hidden = True
Sheet6.Rows("178:180").EntireRow.Hidden = True
End If

End Sub

Some one please help me make modifications to this code or give me
another work around code.
Thanks a lot
Regards,
SRC


All times are GMT +1. The time now is 03:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com