Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Unhide roes in a different worksheet - My code is not working
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 42
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 42
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
coloured roes static while sorting data ybeckett Excel Discussion (Misc queries) 3 December 4th 09 04:45 PM
My code is still not working -- Excel 2000 Worksheet protection Bill Case Excel Programming 2 September 19th 06 03:56 PM
how do i unhide a worksheet in excel 2003? unhide tab don't work mikekeat Excel Discussion (Misc queries) 2 March 6th 06 03:36 AM
Code not working after worksheet change. Erik Excel Programming 0 September 17th 04 04:01 AM


All times are GMT +1. The time now is 05:47 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement