I am trying to make a formula in a cell that will read the value the
user puts in the cell and multiply it by 40, giving me that as the cell
value instead of just the input.
I tried putting this in the cell
=d15*40 (in cell d15)
This gives me a circular argument error.
I suspect I might need to do it in vba, but where does it go in the vba.
I am still confused by the Change event on the worksheet and what all
goes there and how to line up the sequence of events. Oh my!
Thanks for any info you can give me
Joanne

One way is to right click the sheet tab, view code and paste this in. It
works on cell A1 but If you want to apply this to a range of cells and don't
know how to modify it then post back.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
Target = Target * 40
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Mike

"Joanne" wrote:

> I am trying to make a formula in a cell that will read the value the
> user puts in the cell and multiply it by 40, giving me that as the cell
> value instead of just the input.
> I tried putting this in the cell
> =d15*40 (in cell d15)
> This gives me a circular argument error.
> I suspect I might need to do it in vba, but where does it go in the vba.
> I am still confused by the Change event on the worksheet and what all
> goes there and how to line up the sequence of events. Oh my!
> Thanks for any info you can give me
> Joanne
>

THank you Mike
Yes I need it to run on a range of col d cells. Can I name the range and
use it in the code like this?
If Target.Address = RangeName Then

So this goes in the worksheet_change event. How does the excel ws know
in what order to run all the little snippets of coding that are in the
worksheet_change event? I guess I am a bit hung up on getting the code
in proper sequence so that it will run - it seems to this newbie that
that is not necessary in the change event? Am I correct?

Thanks for your help
Joanne

Mike H wrote:

> One way is to right click the sheet tab, view code and paste this in. It
> works on cell A1 but If you want to apply this to a range of cells and don't
> know how to modify it then post back.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
> If Target.Address = "$A$1" Then
> If IsNumeric(Target) Then
> On Error Resume Next
> Application.EnableEvents = False
> Target = Target * 40
> Application.EnableEvents = True
> On Error GoTo 0
> End If
> End If
> End Sub
>
> Mike
>
> "Joanne" wrote:
>
> > I am trying to make a formula in a cell that will read the value the
> > user puts in the cell and multiply it by 40, giving me that as the cell
> > value instead of just the input.
> > I tried putting this in the cell
> > =d15*40 (in cell d15)
> > This gives me a circular argument error.
> > I suspect I might need to do it in vba, but where does it go in the vba.
> > I am still confused by the Change event on the worksheet and what all
> > goes there and how to line up the sequence of events. Oh my!
> > Thanks for any info you can give me
> > Joanne
> >

Joanne,

No you do it like this

If Not Intersect(Target, Range("D1100")) Is Nothing Then

You can extend or shorten the range as you require but this works on D1 - D100

I'm not quite sure what you mean by the second part of your question but
this may help.

Events happen whenever change is made in a worksheet/book and there are
several worksheet ones:-
Activate
Before double click
Change
etc

With regard to the worksheet change event it is fired every time the
worksheet changes and if there is any code in the event module then it is
executed. In our case when any cell is changed the event fires but the code
only does anything if it's the cells specified within the code that have
changed.

Have a look here where events are explained far better then I do.
http://www.cpearson.com/excel/Events.aspx

Mike

"Joanne" wrote:

> THank you Mike
> Yes I need it to run on a range of col d cells. Can I name the range and
> use it in the code like this?
> If Target.Address = RangeName Then
>
> So this goes in the worksheet_change event. How does the excel ws know
> in what order to run all the little snippets of coding that are in the
> worksheet_change event? I guess I am a bit hung up on getting the code
> in proper sequence so that it will run - it seems to this newbie that
> that is not necessary in the change event? Am I correct?
>
> Thanks for your help
> Joanne
>
> Mike H wrote:
>
> > One way is to right click the sheet tab, view code and paste this in. It
> > works on cell A1 but If you want to apply this to a range of cells and don't
> > know how to modify it then post back.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
> > If Target.Address = "$A$1" Then
> > If IsNumeric(Target) Then
> > On Error Resume Next
> > Application.EnableEvents = False
> > Target = Target * 40
> > Application.EnableEvents = True
> > On Error GoTo 0
> > End If
> > End If
> > End Sub
> >
> > Mike
> >
> > "Joanne" wrote:
> >
> > > I am trying to make a formula in a cell that will read the value the
> > > user puts in the cell and multiply it by 40, giving me that as the cell
> > > value instead of just the input.
> > > I tried putting this in the cell
> > > =d15*40 (in cell d15)
> > > This gives me a circular argument error.
> > > I suspect I might need to do it in vba, but where does it go in the vba.
> > > I am still confused by the Change event on the worksheet and what all
> > > goes there and how to line up the sequence of events. Oh my!
> > > Thanks for any info you can give me
> > > Joanne
> > >
>
>

Thanks again Mike - you solved my immediate problem and given me some
understanding of the change event. I will follow the link and learn more
so I can better help myself.
Joanne
Mike H wrote:

> Joanne,
>
> No you do it like this
>
> If Not Intersect(Target, Range("D1100")) Is Nothing Then
>
> You can extend or shorten the range as you require but this works on D1 - D100
>
> I'm not quite sure what you mean by the second part of your question but
> this may help.
>
> Events happen whenever change is made in a worksheet/book and there are
> several worksheet ones:-
> Activate
> Before double click
> Change
> etc
>
> With regard to the worksheet change event it is fired every time the
> worksheet changes and if there is any code in the event module then it is
> executed. In our case when any cell is changed the event fires but the code
> only does anything if it's the cells specified within the code that have
> changed.
>
> Have a look here where events are explained far better then I do.
> http://www.cpearson.com/excel/Events.aspx
>
> Mike
>
> "Joanne" wrote:
>
> > THank you Mike
> > Yes I need it to run on a range of col d cells. Can I name the range and
> > use it in the code like this?
> > If Target.Address = RangeName Then
> >
> > So this goes in the worksheet_change event. How does the excel ws know
> > in what order to run all the little snippets of coding that are in the
> > worksheet_change event? I guess I am a bit hung up on getting the code
> > in proper sequence so that it will run - it seems to this newbie that
> > that is not necessary in the change event? Am I correct?
> >
> > Thanks for your help
> > Joanne
> >
> > Mike H wrote:
> >
> > > One way is to right click the sheet tab, view code and paste this in. It
> > > works on cell A1 but If you want to apply this to a range of cells and don't
> > > know how to modify it then post back.
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
> > > If Target.Address = "$A$1" Then
> > > If IsNumeric(Target) Then
> > > On Error Resume Next
> > > Application.EnableEvents = False
> > > Target = Target * 40
> > > Application.EnableEvents = True
> > > On Error GoTo 0
> > > End If
> > > End If
> > > End Sub
> > >
> > > Mike
> > >
> > > "Joanne" wrote:
> > >
> > > > I am trying to make a formula in a cell that will read the value the
> > > > user puts in the cell and multiply it by 40, giving me that as the cell
> > > > value instead of just the input.
> > > > I tried putting this in the cell
> > > > =d15*40 (in cell d15)
> > > > This gives me a circular argument error.
> > > > I suspect I might need to do it in vba, but where does it go in the vba.
> > > > I am still confused by the Change event on the worksheet and what all
> > > > goes there and how to line up the sequence of events. Oh my!
> > > > Thanks for any info you can give me
> > > > Joanne
> > > >
> >
> >