Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
refilling data in same cell after user input in another cell
How can I reduce the value in a cell by certain number which I enter in
another cell. eg: operator enters value 3 in cell D2 and press enter - I want the value say 55 in cell B5 to be reduced by 3 and have 3 from D2 to be cleared and 55 becomes 52 in B5. -- H. Singh |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
refilling data in same cell after user input in another cell
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D2" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Range("B5") = Range("B5") - .Value .Value = "" End With End If ws_exit: Application.EnableEvents = True End Sub "H. Singh" wrote: How can I reduce the value in a cell by certain number which I enter in another cell. eg: operator enters value 3 in cell D2 and press enter - I want the value say 55 in cell B5 to be reduced by 3 and have 3 from D2 to be cleared and 55 becomes 52 in B5. -- H. Singh |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
refilling data in same cell after user input in another cell
Thank You Topper -- But how I will implement this in an worksheet.
Can it be simple. Or Where I should start learnig how to use this visual basic things without big hasstle in short time and without taking lot of courses and spending lots of money. -- H. Singh "Toppers" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "D2" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Range("B5") = Range("B5") - .Value .Value = "" End With End If ws_exit: Application.EnableEvents = True End Sub "H. Singh" wrote: How can I reduce the value in a cell by certain number which I enter in another cell. eg: operator enters value 3 in cell D2 and press enter - I want the value say 55 in cell B5 to be reduced by 3 and have 3 from D2 to be cleared and 55 becomes 52 in B5. -- H. Singh |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
refilling data in same cell after user input in another cell
Right-click on the sheet tab and "View Code"
Copy/paste Topper's code into that sheet module. Start pounding numbers into D2. I strongly suggest you not use this method. You have no way of correcting any errors made when entering a new number into D2. i.e. you have no "paper trail" to follow to see where an error occurred. Gord Dibben MS Excel MVP On Thu, 19 Apr 2007 12:54:02 -0700, H. Singh wrote: Thank You Topper -- But how I will implement this in an worksheet. Can it be simple. Or Where I should start learnig how to use this visual basic things without big hasstle in short time and without taking lot of courses and spending lots of money. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
refilling data in same cell after user input in another cell
Hi Gord
I created Module in a new worksheet - & tried entering data in D2 & pressing enter -- nothing worked. I tried F5 etc ---. I have very complicated (for me) sheet with long conditional formulas working for maintaining my stock for machines parts. I am using Excel 2003 sp2. At present I have to update stock quantity manually. But I want to automate. If this first step works then I can introduce what machine a particular stock is required with some machine code check in another column & if true then it will subtract it othervise not. & so on it need to proceed to next row for another check I thought I can limit the formulas calculations so I tried limiting formula calculations to 1 time only & using F9 -- It is not that good too. Are these modules - macros. I need to get a nack of it & once I understand I will work more complicated. can someone get me going to start on these modules / macros / vb thing in a simple manner. -- H. Singh "Gord Dibben" wrote: Right-click on the sheet tab and "View Code" Copy/paste Topper's code into that sheet module. Start pounding numbers into D2. I strongly suggest you not use this method. You have no way of correcting any errors made when entering a new number into D2. i.e. you have no "paper trail" to follow to see where an error occurred. Gord Dibben MS Excel MVP On Thu, 19 Apr 2007 12:54:02 -0700, H. Singh wrote: Thank You Topper -- But how I will implement this in an worksheet. Can it be simple. Or Where I should start learnig how to use this visual basic things without big hasstle in short time and without taking lot of courses and spending lots of money. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
refilling data in same cell after user input in another cell
First, I think you should consider carefully Gordon's advice about using this
approach which leaves no room for error and as he says, no audit trail. As for learning VBA, the cheapest way is to buy a good book (John Walkenbach's are highly recommended - he has a web site: www.j-walk.com) and then get lots of practice. Plus use these NGs as an excellent source of expertise (none better!). Without seeing your w/book it is impossible to advise you on what to do next. if you can send a sample to me with clear explanation of requirement(s), I am willing to offer you my solution(s). E-mail: toppers at REMOVETHISjohntopley.fsnet.co.uk "H. Singh" wrote: Hi Gord I created Module in a new worksheet - & tried entering data in D2 & pressing enter -- nothing worked. I tried F5 etc ---. I have very complicated (for me) sheet with long conditional formulas working for maintaining my stock for machines parts. I am using Excel 2003 sp2. At present I have to update stock quantity manually. But I want to automate. If this first step works then I can introduce what machine a particular stock is required with some machine code check in another column & if true then it will subtract it othervise not. & so on it need to proceed to next row for another check I thought I can limit the formulas calculations so I tried limiting formula calculations to 1 time only & using F9 -- It is not that good too. Are these modules - macros. I need to get a nack of it & once I understand I will work more complicated. can someone get me going to start on these modules / macros / vb thing in a simple manner. -- H. Singh "Gord Dibben" wrote: Right-click on the sheet tab and "View Code" Copy/paste Topper's code into that sheet module. Start pounding numbers into D2. I strongly suggest you not use this method. You have no way of correcting any errors made when entering a new number into D2. i.e. you have no "paper trail" to follow to see where an error occurred. Gord Dibben MS Excel MVP On Thu, 19 Apr 2007 12:54:02 -0700, H. Singh wrote: Thank You Topper -- But how I will implement this in an worksheet. Can it be simple. Or Where I should start learnig how to use this visual basic things without big hasstle in short time and without taking lot of courses and spending lots of money. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
refilling data in same cell after user input in another cell
Hi Topper/Gord,
I am emailing the excell file t you both and here is a little explanatin of what i am trying. I want INSTOCK items to be reduced in the required cells after input from user in XX and YY e.g. #of machines = 2 of type A in cell McCodeInput and press Enter on key board. Look for A s in column MACHINE CODE ( in this case first one is Barrel Mount ) then in the concerned sheet e.g. in this case GX-A look how many parts of that stock are required per machine --- then multiply that by the (# of machines to take out = XX i.e. 2 ) in this case in cell NumMcs and subtract it from that IN STOCK quantity. This work book I want it like this final. So far I am using similar thing but doing this all this stock reducing manually because I know which machine takes how many of which parts and I simply multiply that in my head and reduce it every now and then from ALL work sheet. For printing I go to that particular machine and print it if required. Its easy but I want it easier so that I fill only XX and YY and press Enter & the job is done. I started learning Visual Basic & it does seems very easy. So I think with help / suggestion from some of you experts will help a lot. H Singh -- H. Singh "Toppers" wrote: First, I think you should consider carefully Gordon's advice about using this approach which leaves no room for error and as he says, no audit trail. As for learning VBA, the cheapest way is to buy a good book (John Walkenbach's are highly recommended - he has a web site: www.j-walk.com) and then get lots of practice. Plus use these NGs as an excellent source of expertise (none better!). Without seeing your w/book it is impossible to advise you on what to do next. if you can send a sample to me with clear explanation of requirement(s), I am willing to offer you my solution(s). E-mail: toppers at REMOVETHISjohntopley.fsnet.co.uk "H. Singh" wrote: Hi Gord I created Module in a new worksheet - & tried entering data in D2 & pressing enter -- nothing worked. I tried F5 etc ---. I have very complicated (for me) sheet with long conditional formulas working for maintaining my stock for machines parts. I am using Excel 2003 sp2. At present I have to update stock quantity manually. But I want to automate. If this first step works then I can introduce what machine a particular stock is required with some machine code check in another column & if true then it will subtract it othervise not. & so on it need to proceed to next row for another check I thought I can limit the formulas calculations so I tried limiting formula calculations to 1 time only & using F9 -- It is not that good too. Are these modules - macros. I need to get a nack of it & once I understand I will work more complicated. can someone get me going to start on these modules / macros / vb thing in a simple manner. -- H. Singh "Gord Dibben" wrote: Right-click on the sheet tab and "View Code" Copy/paste Topper's code into that sheet module. Start pounding numbers into D2. I strongly suggest you not use this method. You have no way of correcting any errors made when entering a new number into D2. i.e. you have no "paper trail" to follow to see where an error occurred. Gord Dibben MS Excel MVP On Thu, 19 Apr 2007 12:54:02 -0700, H. Singh wrote: Thank You Topper -- But how I will implement this in an worksheet. Can it be simple. Or Where I should start learnig how to use this visual basic things without big hasstle in short time and without taking lot of courses and spending lots of money. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
refilling data in same cell after user input in another cell
I just sent three emails but I got the Toppers yours private one thru but
others which I tried to send you both on your names at discussions.microsoft.com failed. Is there some way for sending this file to Gord also so that he can also see it. Thx. -- H. Singh "Toppers" wrote: First, I think you should consider carefully Gordon's advice about using this approach which leaves no room for error and as he says, no audit trail. As for learning VBA, the cheapest way is to buy a good book (John Walkenbach's are highly recommended - he has a web site: www.j-walk.com) and then get lots of practice. Plus use these NGs as an excellent source of expertise (none better!). Without seeing your w/book it is impossible to advise you on what to do next. if you can send a sample to me with clear explanation of requirement(s), I am willing to offer you my solution(s). E-mail: toppers at REMOVETHISjohntopley.fsnet.co.uk "H. Singh" wrote: Hi Gord I created Module in a new worksheet - & tried entering data in D2 & pressing enter -- nothing worked. I tried F5 etc ---. I have very complicated (for me) sheet with long conditional formulas working for maintaining my stock for machines parts. I am using Excel 2003 sp2. At present I have to update stock quantity manually. But I want to automate. If this first step works then I can introduce what machine a particular stock is required with some machine code check in another column & if true then it will subtract it othervise not. & so on it need to proceed to next row for another check I thought I can limit the formulas calculations so I tried limiting formula calculations to 1 time only & using F9 -- It is not that good too. Are these modules - macros. I need to get a nack of it & once I understand I will work more complicated. can someone get me going to start on these modules / macros / vb thing in a simple manner. -- H. Singh "Gord Dibben" wrote: Right-click on the sheet tab and "View Code" Copy/paste Topper's code into that sheet module. Start pounding numbers into D2. I strongly suggest you not use this method. You have no way of correcting any errors made when entering a new number into D2. i.e. you have no "paper trail" to follow to see where an error occurred. Gord Dibben MS Excel MVP On Thu, 19 Apr 2007 12:54:02 -0700, H. Singh wrote: Thank You Topper -- But how I will implement this in an worksheet. Can it be simple. Or Where I should start learnig how to use this visual basic things without big hasstle in short time and without taking lot of courses and spending lots of money. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force user to enter data in cell before moving to next cell | New Users to Excel | |||
User Data Input | Excel Worksheet Functions | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
user input decides cell copy range | Excel Discussion (Misc queries) | |||
Change color of cell after user has input a number | Excel Discussion (Misc queries) |