Code correction pls help
 

Hi,

I have a cell (D4) in Sheet 2, which is referenced to cell (D87) in
Sheet1.

Cell D4 has a dropdown (thru Data - Validation - list) Values are :-
Select one, 1, 2, 3, 4, 5

Now i have a code in Sheet2 (for cell D4), which will unhide cells
based on values given in D87. The code is working fine.

My problem is the rows dont hide/unhide, till click on some other cell
and click back on D4 in Sheet2. Can this be fixed ?? My code is below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("Offering_Info")
If Not Intersect(.Cells, Target) Is Nothing Then
Application.ScreenUpdating = False
Rows("5:14").Hidden = True
If IsNumeric(.Value) Then
Rows("5:5"). _
Resize(1 + CLng(.Value) * 2).Hidden = False
End If
Application.ScreenUpdating = True
End If
End With
End Sub

Code correction pls help
 

Works fine for me (I presume that Offering_Info is D4?). What Excel version?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



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

I have a cell (D4) in Sheet 2, which is referenced to cell (D87) in
Sheet1.

Cell D4 has a dropdown (thru Data - Validation - list) Values are :-
Select one, 1, 2, 3, 4, 5

Now i have a code in Sheet2 (for cell D4), which will unhide cells
based on values given in D87. The code is working fine.

My problem is the rows dont hide/unhide, till click on some other cell
and click back on D4 in Sheet2. Can this be fixed ?? My code is below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("Offering_Info")
If Not Intersect(.Cells, Target) Is Nothing Then
Application.ScreenUpdating = False
Rows("5:14").Hidden = True
If IsNumeric(.Value) Then
Rows("5:5"). _
Resize(1 + CLng(.Value) * 2).Hidden = False
End If
Application.ScreenUpdating = True
End If
End With
End Sub

Code correction pls help
 

On Aug 16, 4:15 pm, "Bob Phillips" wrote:
Works fine for me (I presume that Offering_Info is D4?). What Excel version?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ram" wrote in message

oups.com...



Hi,

I have a cell (D4) in Sheet 2, which is referenced to cell (D87) in
Sheet1.

Cell D4 has a dropdown (thru Data - Validation - list) Values are :-
Select one, 1, 2, 3, 4, 5

Now i have a code in Sheet2 (for cell D4), which will unhide cells
based on values given in D87. The code is working fine.

My problem is the rows dont hide/unhide, till click on some other cell
and click back on D4 in Sheet2. Can this be fixed ?? My code is below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("Offering_Info")
If Not Intersect(.Cells, Target) Is Nothing Then
Application.ScreenUpdating = False
Rows("5:14").Hidden = True
If IsNumeric(.Value) Then
Rows("5:5"). _
Resize(1 + CLng(.Value) * 2).Hidden = False
End If
Application.ScreenUpdating = True
End If
End With
End Sub- Hide quoted text -

- Show quoted text -

Yes Bob, Offering_Info is D4. My excel version is Excel
2003(11.8142.8132) SP2.

The code works fine for me too. However as i mentioned I have to click
on a different cell lets say (D5) and the click back on (D4). Is it
working the same way for you?

Code correction pls help
 

Maybe using _selectionchange is the wrong event to tie into.

How about trying worksheet_change



Ram wrote:

Hi,

I have a cell (D4) in Sheet 2, which is referenced to cell (D87) in
Sheet1.

Cell D4 has a dropdown (thru Data - Validation - list) Values are :-
Select one, 1, 2, 3, 4, 5

Now i have a code in Sheet2 (for cell D4), which will unhide cells
based on values given in D87. The code is working fine.

My problem is the rows dont hide/unhide, till click on some other cell
and click back on D4 in Sheet2. Can this be fixed ?? My code is below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("Offering_Info")
If Not Intersect(.Cells, Target) Is Nothing Then
Application.ScreenUpdating = False
Rows("5:14").Hidden = True
If IsNumeric(.Value) Then
Rows("5:5"). _
Resize(1 + CLng(.Value) * 2).Hidden = False
End If
Application.ScreenUpdating = True
End If
End With
End Sub

--

Dave Peterson

Code correction pls help
 

As Dave says, it sounds as though it is after changing the value that you
need to switch cells, so you should be using Selection_Change event.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Ram" wrote in message
ups.com...
On Aug 16, 4:15 pm, "Bob Phillips" wrote:
Works fine for me (I presume that Offering_Info is D4?). What Excel
version?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Ram" wrote in message

oups.com...



Hi,

I have a cell (D4) in Sheet 2, which is referenced to cell (D87) in
Sheet1.

Cell D4 has a dropdown (thru Data - Validation - list) Values are :-
Select one, 1, 2, 3, 4, 5

Now i have a code in Sheet2 (for cell D4), which will unhide cells
based on values given in D87. The code is working fine.

My problem is the rows dont hide/unhide, till click on some other cell
and click back on D4 in Sheet2. Can this be fixed ?? My code is below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("Offering_Info")
If Not Intersect(.Cells, Target) Is Nothing Then
Application.ScreenUpdating = False
Rows("5:14").Hidden = True
If IsNumeric(.Value) Then
Rows("5:5"). _
Resize(1 + CLng(.Value) * 2).Hidden = False
End If
Application.ScreenUpdating = True
End If
End With
End Sub- Hide quoted text -

- Show quoted text -

Yes Bob, Offering_Info is D4. My excel version is Excel
2003(11.8142.8132) SP2.

The code works fine for me too. However as i mentioned I have to click
on a different cell lets say (D5) and the click back on (D4). Is it
working the same way for you?

Code correction pls help
 

I mean Worksheet_Change.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob Phillips" wrote in message
...
As Dave says, it sounds as though it is after changing the value that you
need to switch cells, so you should be using Selection_Change event.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Ram" wrote in message
ups.com...
On Aug 16, 4:15 pm, "Bob Phillips" wrote:
Works fine for me (I presume that Offering_Info is D4?). What Excel
version?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Ram" wrote in message

oups.com...



Hi,

I have a cell (D4) in Sheet 2, which is referenced to cell (D87) in
Sheet1.

Cell D4 has a dropdown (thru Data - Validation - list) Values are :-
Select one, 1, 2, 3, 4, 5

Now i have a code in Sheet2 (for cell D4), which will unhide cells
based on values given in D87. The code is working fine.

My problem is the rows dont hide/unhide, till click on some other cell
and click back on D4 in Sheet2. Can this be fixed ?? My code is below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Range("Offering_Info")
If Not Intersect(.Cells, Target) Is Nothing Then
Application.ScreenUpdating = False
Rows("5:14").Hidden = True
If IsNumeric(.Value) Then
Rows("5:5"). _
Resize(1 + CLng(.Value) * 2).Hidden = False
End If
Application.ScreenUpdating = True
End If
End With
End Sub- Hide quoted text -

- Show quoted text -

Yes Bob, Offering_Info is D4. My excel version is Excel
2003(11.8142.8132) SP2.

The code works fine for me too. However as i mentioned I have to click
on a different cell lets say (D5) and the click back on (D4). Is it
working the same way for you?