Code to run Value of cell rather than the Formula

[Magnet Peddler]

I am currently running this code in my worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Rows("12:61").Hidden = True
Rows(12 & ":" & Target.Value + 11).Hidden = False
endit:
Application.EnableEvents = True
End Sub

If i simply enter a number in B5, the code works wonderfully. The problem is
that the value entered in B5 is not simply a number, but a formula which
pulls data from another worksheet. How can i make this work with the value
of b5 rather than the formula in B5?

I am a n00b to this, and would appreciate any help in the right direction.

Thanks in advance.

 

[Jim Thomlinson]

That should work fine so long as the result of the formula in b5 is an
integer value. Give this a try...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Rows("12:61").Hidden = True
Rows(12 & ":" & clng(Target.Value) + 11).Hidden = False
endit:
Application.EnableEvents = True
End Sub

 

[Magnet Peddler]

Thanks for the lightning fast response. OK, so it appears the code was
working after all.

I apparently asked the wrong question then! The code works fine either way
IF i click on the b5 cell then click the check mark. If NOT, however, it
will not create the change event.

So, what i need to ask is how do I make a change event that activates w/o
having to validate the data in b5?

 

[Jim Thomlinson]

Are all of the precident cells of B5 on the same sheet as B5? If so then it
is not too bad. If not then we need to get creative.

 

[Jim Thomlinson]

Sorry duty calls so I will not be able to help you further... Here is the
code if the precidents ar all on the same sheet as B5...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngUpdated As Range

On Error Resume Next
Set rngUpdated = Range("B5").Precedents
If Not rngUpdated Is Nothing Then
Set rngUpdated = Union(Range("B5"), rngUpdated)
Else
Set rngUpdated = Range("B5")
End If
If Intersect(Target, rngUpdated) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Rows("12:61").Hidden = True
Rows(12 & ":" & CLng(Target.Value) + 11).Hidden = False
endit:
Application.EnableEvents = True
End Sub

 

[Magnet Peddler]

And I have run out of time in this precious day too. Thank you so much for
your help thus far.

The way I have this setup is the information on Sheet2 pulls data from
Sheet1 so that you don't have to re-enter the data. Then Sheet2 is emailed,
using Ron Debruin's handy dandy email code, to the appropriate persons.

The catch is that on Sheet1 you have to enter a number from 8 - 50. So, for
example, if you enter 8 in the quantity cell, 8 rows appear and you enter the
appropriate info in them. From there, I would like the 8 in Sheet1 to
transfer to Sheet2, then show the 8 rows needed on Sheet2 rather than all 50.
Am I going in the right direction here, or do I need to try this another way?

 

[Magnet Peddler]

Any takers on this one?