Changing the contents of a cell... dynamically?

I am wanting to do something that I don't know if it is
possible. Here is the scenario...

Say cell A10 gets data (numbers) entered into it, for this
example lets say 100, after the focus moves from the cell I want
25 to be automatically subtracted from the number so that it is
actually is 75. How can I do this if it is possible? Thanks.

--

Please reply to newsgroup so we can all learn from you
knowledge.
Private emails will not be answered.

Friendly Indián

Frank
Tue Feb 03 07:11:48 CST 2004

Hi
you can use the worksheet_change event for this kind of operation. Put
the following code in your worksheet module:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("A10")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo CleanUp
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Value = .Value - 25
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub

HTH
Frank

Friendly Indián wrote:
> I am wanting to do something that I don't know if it is
> possible. Here is the scenario...
>
> Say cell A10 gets data (numbers) entered into it, for this
> example lets say 100, after the focus moves from the cell I want
> 25 to be automatically subtracted from the number so that it is
> actually is 75. How can I do this if it is possible? Thanks.

John
Tue Feb 03 07:13:20 CST 2004

one way:

Paste this Event macro in the worksheet_code module (right-click on the
worksheet tab and select "View Code").

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Address(False, False) = "A10" Then
Application.EnableEvents = False
.Value = .Value - 25
Application.EnableEvents = True
End If
End With
End Sub


In article <em2fmYl6DHA.2480@TK2MSFTNGP10.phx.gbl>,
"Friendly Indián" <spam-free@zone.com> wrote:

> I am wanting to do something that I don't know if it is
> possible. Here is the scenario...
>
> Say cell A10 gets data (numbers) entered into it, for this
> example lets say 100, after the focus moves from the cell I want
> 25 to be automatically subtracted from the number so that it is
> actually is 75. How can I do this if it is possible? Thanks.

Harald
Tue Feb 03 07:16:14 CST 2004

Rightclick the sheet tab. Choose "view code". Paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Address = "$A$10" Then
If IsNumeric(.Formula) Then
Application.EnableEvents = False
.Value = .Value - 25
Application.EnableEvents = True
End If
End If
End With
End Sub

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Friendly Indián" <spam-free@zone.com> skrev i melding
news:em2fmYl6DHA.2480@TK2MSFTNGP10.phx.gbl...
> I am wanting to do something that I don't know if it is
> possible. Here is the scenario...
>
> Say cell A10 gets data (numbers) entered into it, for this
> example lets say 100, after the focus moves from the cell I want
> 25 to be automatically subtracted from the number so that it is
> actually is 75. How can I do this if it is possible? Thanks.
>
> --
>
> Please reply to newsgroup so we can all learn from you
> knowledge.
> Private emails will not be answered.
>
> Friendly Indián
>
>

Shatin
Tue Feb 03 08:48:34 CST 2004

Hello,

Can you guys please explain to me why you need the line:

Application.EnableEvents = False

When I commented out the line, when I entered 100 into the cell, I got the
result 5500 rather than 75. What has happened?

--
"Harald Staff" <innocent@enron.invalid> wrote in message
news:ebFbnfl6DHA.260@TK2MSFTNGP11.phx.gbl...
> Rightclick the sheet tab. Choose "view code". Paste this in:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> With Target(1)
> If .Address = "$A$10" Then
> If IsNumeric(.Formula) Then
> Application.EnableEvents = False
> .Value = .Value - 25
> Application.EnableEvents = True
> End If
> End If
> End With
> End Sub
>
> --
> HTH. Best wishes Harald
> Followup to newsgroup only please
>
> "Friendly Indián" <spam-free@zone.com> skrev i melding
> news:em2fmYl6DHA.2480@TK2MSFTNGP10.phx.gbl...
> > I am wanting to do something that I don't know if it is
> > possible. Here is the scenario...
> >
> > Say cell A10 gets data (numbers) entered into it, for this
> > example lets say 100, after the focus moves from the cell I want
> > 25 to be automatically subtracted from the number so that it is
> > actually is 75. How can I do this if it is possible? Thanks.
> >
> > --
> >
> > Please reply to newsgroup so we can all learn from you
> > knowledge.
> > Private emails will not be answered.
> >
> > Friendly Indián
> >
> >
>
>

Frank
Tue Feb 03 08:59:17 CST 2004

Hi
quite easy. Without this line each time you change the cell A10 the
event gets triggered again. So the line .Value=.value-25 will invoke
this procedure again. With disabling application events the macro just
performs its tasks (and re-enables the events after the processing)

Frank

Shatin wrote:
> Hello,
>
> Can you guys please explain to me why you need the line:
>
> Application.EnableEvents = False
>
> When I commented out the line, when I entered 100 into the cell, I
> got the result 5500 rather than 75. What has happened?
>
>> Rightclick the sheet tab. Choose "view code". Paste this in:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> With Target(1)
>> If .Address = "$A$10" Then
>> If IsNumeric(.Formula) Then
>> Application.EnableEvents = False
>> .Value = .Value - 25
>> Application.EnableEvents = True
>> End If
>> End If
>> End With
>> End Sub
>>
>> --
>> HTH. Best wishes Harald
>> Followup to newsgroup only please
>>
>> "Friendly Indián" <spam-free@zone.com> skrev i melding
>> news:em2fmYl6DHA.2480@TK2MSFTNGP10.phx.gbl...
>>> I am wanting to do something that I don't know if it is
>>> possible. Here is the scenario...
>>>
>>> Say cell A10 gets data (numbers) entered into it, for this
>>> example lets say 100, after the focus moves from the cell I want
>>> 25 to be automatically subtracted from the number so that it is
>>> actually is 75. How can I do this if it is possible? Thanks.
>>>
>>> --
>>>
>>> Please reply to newsgroup so we can all learn from you
>>> knowledge.
>>> Private emails will not be answered.
>>>
>>> Friendly Indián

shatin
Tue Feb 03 13:27:20 CST 2004

Sorry, Frank. I still don't understand this. You wrote:

> quite easy. Without this line each time you change the cell A10 the
> event gets triggered again.

Not quite sure what you mean by "each time" becaue I only entered the
value into A10 just once but the value has jumped to 5500 already.

Jake
Tue Feb 03 14:45:32 CST 2004

Hi Shatin,

Yes, you only entered the value once, but you are changing the Value
property via VBA code (which triggers the subroutine just as if you had
changed the value yourself). If you don't use EnableEvents=False, then the
event routine will keep getting triggered until Excel figures out it's in an
endless loop and stops.

Example:

1) You enter 10 --> this triggers the subroutine
2) the subroutine changes the value to 75 --> this triggers the subroutine
again
3) the subroutine changes the value to 50 --> this triggers the subroutine
again
...

So you see, without disabling built-in application events, Excel will get
stuck in an endless loop of updating the value over and over.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Shatin wrote:
> Sorry, Frank. I still don't understand this. You wrote:
>
>> quite easy. Without this line each time you change the cell A10 the
>> event gets triggered again.
>
> Not quite sure what you mean by "each time" becaue I only entered the
> value into A10 just once but the value has jumped to 5500 already.

Frank
Tue Feb 03 16:08:47 CST 2004

Hi
yes of course you only entered a value once. But the macro changed the
valued (reduced by 25). This is a new entry (for Excel). Due to this
the macro is invoked again. This repeats again and again (until AFAIK
an overflow is reached). So you see a large (negative) number.
So just leave that line and everything is o.k. :-)
Frank

Shatin wrote:
> Sorry, Frank. I still don't understand this. You wrote:
>
>> quite easy. Without this line each time you change the cell A10 the
>> event gets triggered again.
>
> Not quite sure what you mean by "each time" becaue I only entered the
> value into A10 just once but the value has jumped to 5500 already.

Friendly
Tue Feb 03 17:44:04 CST 2004

This works great, now what about this scenario. I have a range
of cells, a10-a50 and I want to do this with all the cells, i.e.
a10 - 25, a11 - 30, a12 - 20, etc. how can I do this without a
1000 lines of code. can the cell ref. in code be changed
dynamically, and if so how? thanks.


"Harald Staff" <innocent@enron.invalid> wrote in message
news:ebFbnfl6DHA.260@TK2MSFTNGP11.phx.gbl...
> Rightclick the sheet tab. Choose "view code". Paste this in:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> With Target(1)
> If .Address = "$A$10" Then
> If IsNumeric(.Formula) Then
> Application.EnableEvents = False
> .Value = .Value - 25
> Application.EnableEvents = True
> End If
> End If
> End With
> End Sub
>
> --
> HTH. Best wishes Harald
> Followup to newsgroup only please
>
> "Friendly Indián" <spam-free@zone.com> skrev i melding
> news:em2fmYl6DHA.2480@TK2MSFTNGP10.phx.gbl...
> > I am wanting to do something that I don't know if it is
> > possible. Here is the scenario...
> >
> > Say cell A10 gets data (numbers) entered into it, for this
> > example lets say 100, after the focus moves from the cell I
want
> > 25 to be automatically subtracted from the number so that it
is
> > actually is 75. How can I do this if it is possible?
Thanks.
> >
> > --
> >
> > Please reply to newsgroup so we can all learn from you
> > knowledge.
> > Private emails will not be answered.
> >
> > Friendly Indián
> >
> >
>
>

Harald
Tue Feb 03 19:19:22 CST 2004

25, 30, 20, etc ??? Please explain, this is like the rapid french course "Horse is Cheval.
And so on."

And what's this for ? Why should someone have to enter 100 to get 75 in a cell ?

Best wishes Harald
Followup to newsgroup only please.

"Friendly Indián" <spam-free@zone.com> wrote in message
news:u6wUCAr6DHA.2480@TK2MSFTNGP12.phx.gbl...
> This works great, now what about this scenario. I have a range
> of cells, a10-a50 and I want to do this with all the cells, i.e.
> a10 - 25, a11 - 30, a12 - 20, etc. how can I do this without a
> 1000 lines of code. can the cell ref. in code be changed
> dynamically, and if so how? thanks.
>
>
> "Harald Staff" <innocent@enron.invalid> wrote in message
> news:ebFbnfl6DHA.260@TK2MSFTNGP11.phx.gbl...
> > Rightclick the sheet tab. Choose "view code". Paste this in:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > With Target(1)
> > If .Address = "$A$10" Then
> > If IsNumeric(.Formula) Then
> > Application.EnableEvents = False
> > .Value = .Value - 25
> > Application.EnableEvents = True
> > End If
> > End If
> > End With
> > End Sub
> >
> > --
> > HTH. Best wishes Harald
> > Followup to newsgroup only please
> >
> > "Friendly Indián" <spam-free@zone.com> skrev i melding
> > news:em2fmYl6DHA.2480@TK2MSFTNGP10.phx.gbl...
> > > I am wanting to do something that I don't know if it is
> > > possible. Here is the scenario...
> > >
> > > Say cell A10 gets data (numbers) entered into it, for this
> > > example lets say 100, after the focus moves from the cell I
> want
> > > 25 to be automatically subtracted from the number so that it
> is
> > > actually is 75. How can I do this if it is possible?
> Thanks.
> > >
> > > --
> > >
> > > Please reply to newsgroup so we can all learn from you
> > > knowledge.
> > > Private emails will not be answered.
> > >
> > > Friendly Indián
> > >
> > >
> >
> >
>
>

shatin
Tue Feb 03 21:11:46 CST 2004

Thanks Jake and Frank. Now I understand why you need to disable EnableEvents.

Frank
Wed Feb 04 02:18:52 CST 2004

Hi
interesting. One question first: why don't enter the correct numbers
:-). Easy solutions for this (without writing code for each line) could
be:
- is there a 'system' behind these changes (from your example data doe
not like there is, but maybe there is...)
- If you are able tu put the subtraction value in a cell adjacent to
this column, the macro can use this information (you can hide this
column if you like)

So - as Harald wrote - give us some more details what you want to
achieve

Frank

Friendly Indián wrote:
> This works great, now what about this scenario. I have a range
> of cells, a10-a50 and I want to do this with all the cells, i.e.
> a10 - 25, a11 - 30, a12 - 20, etc. how can I do this without a
> 1000 lines of code. can the cell ref. in code be changed
> dynamically, and if so how? thanks.
>
>
> "Harald Staff" <innocent@enron.invalid> wrote in message
> news:ebFbnfl6DHA.260@TK2MSFTNGP11.phx.gbl...
>> Rightclick the sheet tab. Choose "view code". Paste this in:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> With Target(1)
>> If .Address = "$A$10" Then
>> If IsNumeric(.Formula) Then
>> Application.EnableEvents = False
>> .Value = .Value - 25
>> Application.EnableEvents = True
>> End If
>> End If
>> End With
>> End Sub
>>
>> --
>> HTH. Best wishes Harald
>> Followup to newsgroup only please
>>
>> "Friendly Indián" <spam-free@zone.com> skrev i melding
>> news:em2fmYl6DHA.2480@TK2MSFTNGP10.phx.gbl...
>>> I am wanting to do something that I don't know if it is
>>> possible. Here is the scenario...
>>>
>>> Say cell A10 gets data (numbers) entered into it, for this
>>> example lets say 100, after the focus moves from the cell I want
>>> 25 to be automatically subtracted from the number so that it is
>>> actually is 75. How can I do this if it is possible? Thanks.
>>>
>>> --
>>>
>>> Please reply to newsgroup so we can all learn from you
>>> knowledge.
>>> Private emails will not be answered.
>>>
>>> Friendly Indián

Friendly
Wed Feb 04 07:01:43 CST 2004

This is the reason. At my job I weigh containers (3 different
types), these containers contain packages. Now each contanier
has a different weight (tare), the weight I am interested in is
the actual package weight (the tares are 35, 210 and 466 lbs),
so when I enter a weight in, I want the tare weight to be
subtracted automatically from the cell. I have different
columns for each of the different containers, I then use the
data in a chart. So is there an easy (or pretty easy ) way to
do this? I can include a copy of my datasheet if that would
help to visualize. Thanks a lot!


"Harald Staff" <innocent@enron.invalid> wrote in message
news:OI2xVxr6DHA.1596@TK2MSFTNGP10.phx.gbl...
> 25, 30, 20, etc ??? Please explain, this is like the rapid
french course "Horse is Cheval.
> And so on."
>
> And what's this for ? Why should someone have to enter 100 to
get 75 in a cell ?
>
> Best wishes Harald
> Followup to newsgroup only please.
>
> "Friendly Indián" <spam-free@zone.com> wrote in message
> news:u6wUCAr6DHA.2480@TK2MSFTNGP12.phx.gbl...
> > This works great, now what about this scenario. I have a
range
> > of cells, a10-a50 and I want to do this with all the cells,
i.e.
> > a10 - 25, a11 - 30, a12 - 20, etc. how can I do this
without a
> > 1000 lines of code. can the cell ref. in code be changed
> > dynamically, and if so how? thanks.
> >
> >
> > "Harald Staff" <innocent@enron.invalid> wrote in message
> > news:ebFbnfl6DHA.260@TK2MSFTNGP11.phx.gbl...
> > > Rightclick the sheet tab. Choose "view code". Paste this
in:
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > With Target(1)
> > > If .Address = "$A$10" Then
> > > If IsNumeric(.Formula) Then
> > > Application.EnableEvents = False
> > > .Value = .Value - 25
> > > Application.EnableEvents = True
> > > End If
> > > End If
> > > End With
> > > End Sub
> > >
> > > --
> > > HTH. Best wishes Harald
> > > Followup to newsgroup only please
> > >
> > > "Friendly Indián" <spam-free@zone.com> skrev i melding
> > > news:em2fmYl6DHA.2480@TK2MSFTNGP10.phx.gbl...
> > > > I am wanting to do something that I don't know if it is
> > > > possible. Here is the scenario...
> > > >
> > > > Say cell A10 gets data (numbers) entered into it, for
this
> > > > example lets say 100, after the focus moves from the
cell I
> > want
> > > > 25 to be automatically subtracted from the number so
that it
> > is
> > > > actually is 75. How can I do this if it is possible?
> > Thanks.
> > > >
> > > > --
> > > >
> > > > Please reply to newsgroup so we can all learn from you
> > > > knowledge.
> > > > Private emails will not be answered.
> > > >
> > > > Friendly Indián
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Frank
Wed Feb 04 07:13:50 CST 2004

Hi
I would use this kind of problem differently (without using the
worksheet_change event). Why don't you use two adjacent cells (one
including the tare weight and one cell automatically calculating the
package weight.). e.g.
A B C
1000 cont_type 975
...
In this case you enter the weight in column A and select the container
type in column B (using a listbox/data validation). The value in column
C is then calculated automatically.

If you like, you can send me your spreadsheet and i#ll setup both
versions (using worksheet change and using two cells)

Frank

Friendly Indián wrote:
> This is the reason. At my job I weigh containers (3 different
> types), these containers contain packages. Now each contanier
> has a different weight (tare), the weight I am interested in is
> the actual package weight (the tares are 35, 210 and 466 lbs),
> so when I enter a weight in, I want the tare weight to be
> subtracted automatically from the cell. I have different
> columns for each of the different containers, I then use the
> data in a chart. So is there an easy (or pretty easy ) way to
> do this? I can include a copy of my datasheet if that would
> help to visualize. Thanks a lot!
>
>
> "Harald Staff" <innocent@enron.invalid> wrote in message
> news:OI2xVxr6DHA.1596@TK2MSFTNGP10.phx.gbl...
>> 25, 30, 20, etc ??? Please explain, this is like the rapid
> french course "Horse is Cheval.
>> And so on."
>>
>> And what's this for ? Why should someone have to enter 100 to get 75
>> in a cell ?
>>
>> Best wishes Harald
>> Followup to newsgroup only please.
>>
>> "Friendly Indián" <spam-free@zone.com> wrote in message
>> news:u6wUCAr6DHA.2480@TK2MSFTNGP12.phx.gbl...
>>> This works great, now what about this scenario. I have a range
>>> of cells, a10-a50 and I want to do this with all the cells, i.e.
>>> a10 - 25, a11 - 30, a12 - 20, etc. how can I do this without a
>>> 1000 lines of code. can the cell ref. in code be changed
>>> dynamically, and if so how? thanks.
>>>
>>>
>>> "Harald Staff" <innocent@enron.invalid> wrote in message
>>> news:ebFbnfl6DHA.260@TK2MSFTNGP11.phx.gbl...
>>>> Rightclick the sheet tab. Choose "view code". Paste this in:
>>>>
>>>> Private Sub Worksheet_Change(ByVal Target As Range)
>>>> With Target(1)
>>>> If .Address = "$A$10" Then
>>>> If IsNumeric(.Formula) Then
>>>> Application.EnableEvents = False
>>>> .Value = .Value - 25
>>>> Application.EnableEvents = True
>>>> End If
>>>> End If
>>>> End With
>>>> End Sub
>>>>
>>>> --
>>>> HTH. Best wishes Harald
>>>> Followup to newsgroup only please
>>>>
>>>> "Friendly Indián" <spam-free@zone.com> skrev i melding
>>>> news:em2fmYl6DHA.2480@TK2MSFTNGP10.phx.gbl...
>>>>> I am wanting to do something that I don't know if it is
>>>>> possible. Here is the scenario...
>>>>>
>>>>> Say cell A10 gets data (numbers) entered into it, for this
>>>>> example lets say 100, after the focus moves from the cell I want
>>>>> 25 to be automatically subtracted from the number so that it is
>>>>> actually is 75. How can I do this if it is possible? Thanks.
>>>>>
>>>>> --
>>>>>
>>>>> Please reply to newsgroup so we can all learn from you
>>>>> knowledge.
>>>>> Private emails will not be answered.
>>>>>
>>>>> Friendly Indián