Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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. |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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 , "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. |
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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" skrev i melding ... 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 |
#5
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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" wrote in message ... 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" skrev i melding ... 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 |
#6
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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" skrev i melding ... 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 |
#7
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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. |
#8
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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. |
#9
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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. |
#10
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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" wrote in message ... 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" skrev i melding ... 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 |
#11
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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" wrote in message ... 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" wrote in message ... 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" skrev i melding ... 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 |
#12
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" skrev i melding ... 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 |
#13
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" skrev i melding ... 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 |
#14
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Changing the contents of a cell... dynamically?
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" wrote in message ... 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" skrev i melding ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Length of Cell Contents | Excel Discussion (Misc queries) | |||
Changing cell contents | Excel Discussion (Misc queries) | |||
Changing the Contents of a Cell | Excel Discussion (Misc queries) | |||
Store values of a dynamically changing cell | Excel Worksheet Functions | |||
Cell contents changing colors | Excel Programming |