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.

--
Murphy's first law of combat: Incoming fire always has the right of way.

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


--
HTH...

Jim Thomlinson


"Magnet Peddler" wrote:

> 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.
>
> --
> Murphy's first law of combat: Incoming fire always has the right of way.

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?




--
Murphy''''''''s first law of combat: Incoming fire always has the right of
way.


"Jim Thomlinson" wrote:

> 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
>
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Magnet Peddler" wrote:
>
> > 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.
> >
> > --
> > Murphy's first law of combat: Incoming fire always has the right of way.

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.
--
HTH...

Jim Thomlinson


"Magnet Peddler" wrote:

> 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?
>
>
>
>
> --
> Murphy''''''''s first law of combat: Incoming fire always has the right of
> way.
>
>
> "Jim Thomlinson" wrote:
>
> > 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
> >
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Magnet Peddler" wrote:
> >
> > > 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.
> > >
> > > --
> > > Murphy's first law of combat: Incoming fire always has the right of way.

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
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> 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.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Magnet Peddler" wrote:
>
> > 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?
> >
> >
> >
> >
> > --
> > Murphy''''''''s first law of combat: Incoming fire always has the right of
> > way.
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > 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
> > >
> > >
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Magnet Peddler" wrote:
> > >
> > > > 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.
> > > >
> > > > --
> > > > Murphy's first law of combat: Incoming fire always has the right of way.

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?


--
Murphy's first law of combat: Incoming fire always has the right of way.


"Jim Thomlinson" wrote:

> 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
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Jim Thomlinson" wrote:
>
> > 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.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Magnet Peddler" wrote:
> >
> > > 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?
> > >
> > >
> > >
> > >
> > > --
> > > Murphy''''''''s first law of combat: Incoming fire always has the right of
> > > way.
> > >
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Magnet Peddler" wrote:
> > > >
> > > > > 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.
> > > > >
> > > > > --
> > > > > Murphy's first law of combat: Incoming fire always has the right of way.

Any takers on this one?
--
Murphy''''''''s first law of combat: Incoming fire always has the right of
way.


"Magnet Peddler" wrote:

> 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?
>
>
> --
> Murphy's first law of combat: Incoming fire always has the right of way.
>
>
> "Jim Thomlinson" wrote:
>
> > 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
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > 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.
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Magnet Peddler" wrote:
> > >
> > > > 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?
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Murphy''''''''s first law of combat: Incoming fire always has the right of
> > > > way.
> > > >
> > > >
> > > > "Jim Thomlinson" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > --
> > > > > HTH...
> > > > >
> > > > > Jim Thomlinson
> > > > >
> > > > >
> > > > > "Magnet Peddler" wrote:
> > > > >
> > > > > > 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.
> > > > > >
> > > > > > --
> > > > > > Murphy's first law of combat: Incoming fire always has the right of way.