Hiding/Showing Rows on Cell H2 Change
 

Hello.

What I have is a spreadsheet and when you open the workbook Cell H2 is empty
and the user selects either "System" or "Detail" from a dropdown list that's
in H2. What I want to do is to hide rows 32 & 33 if H2 is = "Detail" But if
it is "System" it doesn't hide them. However, and here's a tricky thing, if
the user selects "System" after they already selected "Detail" I need rows 32
& 33 to unhide.

Is this possible somehow and if so How would I do it?

Thanks Much In Advance.
Rob

Hiding/Showing Rows on Cell H2 Change
 

Rob

You could put this code behind the worksheet (It is some change event code
and will fire on any change on the worksheet) To implement right click the
sheet tab and select view code and paste in the resultant window. It will
unhide on any word other than detail, but that could be changed

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("H2"), Target) Is Nothing Then
If Target.Value = "Detail" Then
Rows("32:33").Hidden = True
Exit Sub
End If
Rows("32:33").Hidden = False
End If
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Rob" wrote in message
...
Hello.

What I have is a spreadsheet and when you open the workbook Cell H2 is
empty
and the user selects either "System" or "Detail" from a dropdown list
that's
in H2. What I want to do is to hide rows 32 & 33 if H2 is = "Detail" But
if
it is "System" it doesn't hide them. However, and here's a tricky thing,
if
the user selects "System" after they already selected "Detail" I need rows
32
& 33 to unhide.

Is this possible somehow and if so How would I do it?

Thanks Much In Advance.
Rob

Hiding/Showing Rows on Cell H2 Change
 

Private Sub Worksheet_Change(ByVal Target As Range)

'
'
'
'

If (Range("H2") = "Detail") Then
Rows("32:33").Select
Selection.EntireRow.Hidden = True
ElseIf (Range("H2") = "System") Then
Rows("31:34").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End If
End Sub

"Rob" wrote:

Hello.

What I have is a spreadsheet and when you open the workbook Cell H2 is empty
and the user selects either "System" or "Detail" from a dropdown list that's
in H2. What I want to do is to hide rows 32 & 33 if H2 is = "Detail" But if
it is "System" it doesn't hide them. However, and here's a tricky thing, if
the user selects "System" after they already selected "Detail" I need rows 32
& 33 to unhide.

Is this possible somehow and if so How would I do it?

Thanks Much In Advance.
Rob

Hiding/Showing Rows on Cell H2 Change
 

Should have mentioned..
Go to Tools-Macro-Visual Basic Editor
double click on the desired sheet name in the top left pane
paste the above code.

"Rob" wrote:

Hello.

What I have is a spreadsheet and when you open the workbook Cell H2 is empty
and the user selects either "System" or "Detail" from a dropdown list that's
in H2. What I want to do is to hide rows 32 & 33 if H2 is = "Detail" But if
it is "System" it doesn't hide them. However, and here's a tricky thing, if
the user selects "System" after they already selected "Detail" I need rows 32
& 33 to unhide.

Is this possible somehow and if so How would I do it?

Thanks Much In Advance.
Rob

Hiding/Showing Rows on Cell H2 Change
 

Sean

It's easier and safer IMO to right-click on sheet tab and "View Code" then
paste.

Going to VBE some users may not have the Project Explorer open by default and if
they do, the workbook/project may not be expanded so sheet objects will not be
visible or they may have several similar workbooks open and could double-click
the sheet in an incorrect workbook.


Gord Dibben MS Excel MVP

On Thu, 4 Jan 2007 14:05:00 -0800, Sean Timmons
wrote:

Should have mentioned..
Go to Tools-Macro-Visual Basic Editor
double click on the desired sheet name in the top left pane
paste the above code.

"Rob" wrote:

Hello.

What I have is a spreadsheet and when you open the workbook Cell H2 is empty
and the user selects either "System" or "Detail" from a dropdown list that's
in H2. What I want to do is to hide rows 32 & 33 if H2 is = "Detail" But if
it is "System" it doesn't hide them. However, and here's a tricky thing, if
the user selects "System" after they already selected "Detail" I need rows 32
& 33 to unhide.

Is this possible somehow and if so How would I do it?

Thanks Much In Advance.
Rob

Hiding/Showing Rows on Cell H2 Change
 

THANK YOU SOOOOO VERY MUCH. That works like a charm!!!

CHEERS!!
Rob

"Nick Hodge" wrote:

Rob

You could put this code behind the worksheet (It is some change event code
and will fire on any change on the worksheet) To implement right click the
sheet tab and select view code and paste in the resultant window. It will
unhide on any word other than detail, but that could be changed

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("H2"), Target) Is Nothing Then
If Target.Value = "Detail" Then
Rows("32:33").Hidden = True
Exit Sub
End If
Rows("32:33").Hidden = False
End If
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Rob" wrote in message
...
Hello.

What I have is a spreadsheet and when you open the workbook Cell H2 is
empty
and the user selects either "System" or "Detail" from a dropdown list
that's
in H2. What I want to do is to hide rows 32 & 33 if H2 is = "Detail" But
if
it is "System" it doesn't hide them. However, and here's a tricky thing,
if
the user selects "System" after they already selected "Detail" I need rows
32
& 33 to unhide.

Is this possible somehow and if so How would I do it?

Thanks Much In Advance.
Rob

Hiding/Showing Rows on Cell H2 Change
 

THANK YOU ALSO!!! Yours works just as well and what's better is that between
the two I learned a awesome trick!!

GBless!!
Rob

"Sean Timmons" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

'
'
'
'

If (Range("H2") = "Detail") Then
Rows("32:33").Select
Selection.EntireRow.Hidden = True
ElseIf (Range("H2") = "System") Then
Rows("31:34").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End If
End Sub

"Rob" wrote:

Hello.

What I have is a spreadsheet and when you open the workbook Cell H2 is empty
and the user selects either "System" or "Detail" from a dropdown list that's
in H2. What I want to do is to hide rows 32 & 33 if H2 is = "Detail" But if
it is "System" it doesn't hide them. However, and here's a tricky thing, if
the user selects "System" after they already selected "Detail" I need rows 32
& 33 to unhide.

Is this possible somehow and if so How would I do it?

Thanks Much In Advance.
Rob

Hiding/Showing Rows on Cell H2 Change
 

I was able to use the code in two places to trim my "document" down and it
looks nice. My problem is that if I go to the sheet to add or edit, the
focus goes back to cell "A1" each time I enter or press F2. I've tried
substituting "LastCell" in for "A1" but to no avail. I've Rem-ed the line
out and it goes to the hide spot instead of A1, but still not back to where I
was.
How can I get the focus to go back to the cell I was in or the one below?
Thanks
--
Scafidel
Lafayette, Louisiana


"Sean Timmons" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

'
'
'
'

If (Range("H2") = "Detail") Then
Rows("32:33").Select
Selection.EntireRow.Hidden = True
ElseIf (Range("H2") = "System") Then
Rows("31:34").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End If
End Sub

"Rob" wrote:

Hello.

What I have is a spreadsheet and when you open the workbook Cell H2 is empty
and the user selects either "System" or "Detail" from a dropdown list that's
in H2. What I want to do is to hide rows 32 & 33 if H2 is = "Detail" But if
it is "System" it doesn't hide them. However, and here's a tricky thing, if
the user selects "System" after they already selected "Detail" I need rows 32
& 33 to unhide.

Is this possible somehow and if so How would I do it?

Thanks Much In Advance.
Rob

Hiding/Showing Rows on Cell H2 Change
 

Quit selecting things.

Going with your original request of rows 32 and 32................

Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("H2") = "Detail" Then
Rows("32:33").Hidden = True
ElseIf Me.Range("H2") = "System" Then
Rows("32:33").Hidden = False
End If
End Sub


Gord Dibben MS Excel MVP

On Sat, 13 Dec 2008 13:00:02 -0800, Scafidel
wrote:

I was able to use the code in two places to trim my "document" down and it
looks nice. My problem is that if I go to the sheet to add or edit, the
focus goes back to cell "A1" each time I enter or press F2. I've tried
substituting "LastCell" in for "A1" but to no avail. I've Rem-ed the line
out and it goes to the hide spot instead of A1, but still not back to where I
was.
How can I get the focus to go back to the cell I was in or the one below?
Thanks
--
Scafidel
Lafayette, Louisiana


"Sean Timmons" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

'
'
'
'

If (Range("H2") = "Detail") Then
Rows("32:33").Select
Selection.EntireRow.Hidden = True
ElseIf (Range("H2") = "System") Then
Rows("31:34").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End If

Hiding/Showing Rows on Cell H2 Change
 

That did it! Much easier.
Thanks, Gord
--
Scafidel
Lafayette, Louisiana


"Gord Dibben" wrote:

Quit selecting things.

Going with your original request of rows 32 and 32................

Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("H2") = "Detail" Then
Rows("32:33").Hidden = True
ElseIf Me.Range("H2") = "System" Then
Rows("32:33").Hidden = False
End If
End Sub


Gord Dibben MS Excel MVP

On Sat, 13 Dec 2008 13:00:02 -0800, Scafidel
wrote:

I was able to use the code in two places to trim my "document" down and it
looks nice. My problem is that if I go to the sheet to add or edit, the
focus goes back to cell "A1" each time I enter or press F2. I've tried
substituting "LastCell" in for "A1" but to no avail. I've Rem-ed the line
out and it goes to the hide spot instead of A1, but still not back to where I
was.
How can I get the focus to go back to the cell I was in or the one below?
Thanks
--
Scafidel
Lafayette, Louisiana


"Sean Timmons" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

'
'
'
'

If (Range("H2") = "Detail") Then
Rows("32:33").Select
Selection.EntireRow.Hidden = True
ElseIf (Range("H2") = "System") Then
Rows("31:34").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End If