| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear value in cell when the "enter" key is pressed
I have a work area of 4 columns wide and 60 rows long
I need the following macro 1. I enter numbers in column A1 2. Accumulate them in Column B1 3. Clear the value in Column A1 when "enter" key is pressed 4. Enter a new number in Column A1 5. Acumulate that in Column B1 6. Clear the value in Column A1 when the "enter" key is pressed 7 repeat as data entry requires. This same routine would be applied to A2-B2 through row A60-B60 Any help appreciated.. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear value in cell when the "enter" key is pressed
Hi,
Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A60")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value Target.Value = "" Application.EnableEvents = True Else 'optional line to delete any text entered in range 'Target.Value = "" End If End If End Sub Mike "ksfarmer" wrote: I have a work area of 4 columns wide and 60 rows long I need the following macro 1. I enter numbers in column A1 2. Accumulate them in Column B1 3. Clear the value in Column A1 when "enter" key is pressed 4. Enter a new number in Column A1 5. Acumulate that in Column B1 6. Clear the value in Column A1 when the "enter" key is pressed 7 repeat as data entry requires. This same routine would be applied to A2-B2 through row A60-B60 Any help appreciated.. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear value in cell when the "enter" key is pressed
Hello Mike,
Thank your for the code! I am not sure what the right click on the "sheet tab" is referring to. Sorry to be so dumb! Frank "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A60")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value Target.Value = "" Application.EnableEvents = True Else 'optional line to delete any text entered in range 'Target.Value = "" End If End If End Sub Mike "ksfarmer" wrote: I have a work area of 4 columns wide and 60 rows long I need the following macro 1. I enter numbers in column A1 2. Accumulate them in Column B1 3. Clear the value in Column A1 when "enter" key is pressed 4. Enter a new number in Column A1 5. Acumulate that in Column B1 6. Clear the value in Column A1 when the "enter" key is pressed 7 repeat as data entry requires. This same routine would be applied to A2-B2 through row A60-B60 Any help appreciated.. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear value in cell when the "enter" key is pressed
Hi,
At the bottom of your worksheet is a tab with the sheet name on it. Right click this tab and you get a popup menu. Click 'View code' on this menu and paste the code I gave you into the white space on the right. Close VB editor (That's what your now in) by clicking the red cross and start entering your values in column A Mike "ksfarmer" wrote: Hello Mike, Thank your for the code! I am not sure what the right click on the "sheet tab" is referring to. Sorry to be so dumb! Frank "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A60")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value Target.Value = "" Application.EnableEvents = True Else 'optional line to delete any text entered in range 'Target.Value = "" End If End If End Sub Mike "ksfarmer" wrote: I have a work area of 4 columns wide and 60 rows long I need the following macro 1. I enter numbers in column A1 2. Accumulate them in Column B1 3. Clear the value in Column A1 when "enter" key is pressed 4. Enter a new number in Column A1 5. Acumulate that in Column B1 6. Clear the value in Column A1 when the "enter" key is pressed 7 repeat as data entry requires. This same routine would be applied to A2-B2 through row A60-B60 Any help appreciated.. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear value in cell when the "enter" key is pressed
Mike,
I got there and pasted the code in then closed the code window. It does not seem to change the operation of the sheet. I could email you a copy of the sheet I have developed so you could see what it is doing. Frank "Mike H" wrote: Hi, At the bottom of your worksheet is a tab with the sheet name on it. Right click this tab and you get a popup menu. Click 'View code' on this menu and paste the code I gave you into the white space on the right. Close VB editor (That's what your now in) by clicking the red cross and start entering your values in column A Mike "ksfarmer" wrote: Hello Mike, Thank your for the code! I am not sure what the right click on the "sheet tab" is referring to. Sorry to be so dumb! Frank "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A60")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value Target.Value = "" Application.EnableEvents = True Else 'optional line to delete any text entered in range 'Target.Value = "" End If End If End Sub Mike "ksfarmer" wrote: I have a work area of 4 columns wide and 60 rows long I need the following macro 1. I enter numbers in column A1 2. Accumulate them in Column B1 3. Clear the value in Column A1 when "enter" key is pressed 4. Enter a new number in Column A1 5. Acumulate that in Column B1 6. Clear the value in Column A1 when the "enter" key is pressed 7 repeat as data entry requires. This same routine would be applied to A2-B2 through row A60-B60 Any help appreciated.. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear value in cell when the "enter" key is pressed
upload the file here and post the link, it's free
http://www.savefile.com/ Mike "ksfarmer" wrote: Mike, I got there and pasted the code in then closed the code window. It does not seem to change the operation of the sheet. I could email you a copy of the sheet I have developed so you could see what it is doing. Frank "Mike H" wrote: Hi, At the bottom of your worksheet is a tab with the sheet name on it. Right click this tab and you get a popup menu. Click 'View code' on this menu and paste the code I gave you into the white space on the right. Close VB editor (That's what your now in) by clicking the red cross and start entering your values in column A Mike "ksfarmer" wrote: Hello Mike, Thank your for the code! I am not sure what the right click on the "sheet tab" is referring to. Sorry to be so dumb! Frank "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A60")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value Target.Value = "" Application.EnableEvents = True Else 'optional line to delete any text entered in range 'Target.Value = "" End If End If End Sub Mike "ksfarmer" wrote: I have a work area of 4 columns wide and 60 rows long I need the following macro 1. I enter numbers in column A1 2. Accumulate them in Column B1 3. Clear the value in Column A1 when "enter" key is pressed 4. Enter a new number in Column A1 5. Acumulate that in Column B1 6. Clear the value in Column A1 when the "enter" key is pressed 7 repeat as data entry requires. This same routine would be applied to A2-B2 through row A60-B60 Any help appreciated.. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear value in cell when the "enter" key is pressed
Mike,
Link to file http://savefile.com/projects/808744698 Hope I did it right. The Green column is the quantity entry column Frank "Mike H" wrote: upload the file here and post the link, it's free http://www.savefile.com/ Mike "ksfarmer" wrote: Mike, I got there and pasted the code in then closed the code window. It does not seem to change the operation of the sheet. I could email you a copy of the sheet I have developed so you could see what it is doing. Frank "Mike H" wrote: Hi, At the bottom of your worksheet is a tab with the sheet name on it. Right click this tab and you get a popup menu. Click 'View code' on this menu and paste the code I gave you into the white space on the right. Close VB editor (That's what your now in) by clicking the red cross and start entering your values in column A Mike "ksfarmer" wrote: Hello Mike, Thank your for the code! I am not sure what the right click on the "sheet tab" is referring to. Sorry to be so dumb! Frank "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A60")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value Target.Value = "" Application.EnableEvents = True Else 'optional line to delete any text entered in range 'Target.Value = "" End If End If End Sub Mike "ksfarmer" wrote: I have a work area of 4 columns wide and 60 rows long I need the following macro 1. I enter numbers in column A1 2. Accumulate them in Column B1 3. Clear the value in Column A1 when "enter" key is pressed 4. Enter a new number in Column A1 5. Acumulate that in Column B1 6. Clear the value in Column A1 when the "enter" key is pressed 7 repeat as data entry requires. This same routine would be applied to A2-B2 through row A60-B60 Any help appreciated.. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear value in cell when the "enter" key is pressed
Hi,
the problem was you had pasted the code with each line beginning with so every line was a syntax error. I've ammended the code such that if you enter data in the green column (C) it accumulates in column d. I've also removed all the formula from column d because they were circular references. http://www.savefile.com/files/2019846 Mike "ksfarmer" wrote: Mike, Link to file http://savefile.com/projects/808744698 Hope I did it right. The Green column is the quantity entry column Frank "Mike H" wrote: upload the file here and post the link, it's free http://www.savefile.com/ Mike "ksfarmer" wrote: Mike, I got there and pasted the code in then closed the code window. It does not seem to change the operation of the sheet. I could email you a copy of the sheet I have developed so you could see what it is doing. Frank "Mike H" wrote: Hi, At the bottom of your worksheet is a tab with the sheet name on it. Right click this tab and you get a popup menu. Click 'View code' on this menu and paste the code I gave you into the white space on the right. Close VB editor (That's what your now in) by clicking the red cross and start entering your values in column A Mike "ksfarmer" wrote: Hello Mike, Thank your for the code! I am not sure what the right click on the "sheet tab" is referring to. Sorry to be so dumb! Frank "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A60")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value Target.Value = "" Application.EnableEvents = True Else 'optional line to delete any text entered in range 'Target.Value = "" End If End If End Sub Mike "ksfarmer" wrote: I have a work area of 4 columns wide and 60 rows long I need the following macro 1. I enter numbers in column A1 2. Accumulate them in Column B1 3. Clear the value in Column A1 when "enter" key is pressed 4. Enter a new number in Column A1 5. Acumulate that in Column B1 6. Clear the value in Column A1 when the "enter" key is pressed 7 repeat as data entry requires. This same routine would be applied to A2-B2 through row A60-B60 Any help appreciated.. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear value in cell when the "enter" key is pressed
Mike,
Works like a champ!! One glitch was the Macro Security. I had to set it to minimum because the macro was not certified (certificate). Little info on why we wanted this routine. My son is in residential and commercial construction. He goes through plans sheet by sheet entering individual quantities of items that are required. He was doing all this with a calculator for 5 years. I have minimal working knowledge of excel so got tagged to help him. Was way above my head. So, I do sincerely THANK you for your generous help! Frank "Mike H" wrote: Hi, the problem was you had pasted the code with each line beginning with so every line was a syntax error. I've ammended the code such that if you enter data in the green column (C) it accumulates in column d. I've also removed all the formula from column d because they were circular references. http://www.savefile.com/files/2019846 Mike "ksfarmer" wrote: Mike, Link to file http://savefile.com/projects/808744698 Hope I did it right. The Green column is the quantity entry column Frank "Mike H" wrote: upload the file here and post the link, it's free http://www.savefile.com/ Mike "ksfarmer" wrote: Mike, I got there and pasted the code in then closed the code window. It does not seem to change the operation of the sheet. I could email you a copy of the sheet I have developed so you could see what it is doing. Frank "Mike H" wrote: Hi, At the bottom of your worksheet is a tab with the sheet name on it. Right click this tab and you get a popup menu. Click 'View code' on this menu and paste the code I gave you into the white space on the right. Close VB editor (That's what your now in) by clicking the red cross and start entering your values in column A Mike "ksfarmer" wrote: Hello Mike, Thank your for the code! I am not sure what the right click on the "sheet tab" is referring to. Sorry to be so dumb! Frank "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A60")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value Target.Value = "" Application.EnableEvents = True Else 'optional line to delete any text entered in range 'Target.Value = "" End If End If End Sub Mike "ksfarmer" wrote: I have a work area of 4 columns wide and 60 rows long I need the following macro 1. I enter numbers in column A1 2. Accumulate them in Column B1 3. Clear the value in Column A1 when "enter" key is pressed 4. Enter a new number in Column A1 5. Acumulate that in Column B1 6. Clear the value in Column A1 when the "enter" key is pressed 7 repeat as data entry requires. This same routine would be applied to A2-B2 through row A60-B60 Any help appreciated.. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear value in cell when the "enter" key is pressed
Mike,
One other question. Can you recommend a good book/s for learning Visual Basic and writing macros for excel? Again - Thanks "ksfarmer" wrote: Mike, Works like a champ!! One glitch was the Macro Security. I had to set it to minimum because the macro was not certified (certificate). Little info on why we wanted this routine. My son is in residential and commercial construction. He goes through plans sheet by sheet entering individual quantities of items that are required. He was doing all this with a calculator for 5 years. I have minimal working knowledge of excel so got tagged to help him. Was way above my head. So, I do sincerely THANK you for your generous help! Frank "Mike H" wrote: Hi, the problem was you had pasted the code with each line beginning with so every line was a syntax error. I've ammended the code such that if you enter data in the green column (C) it accumulates in column d. I've also removed all the formula from column d because they were circular references. http://www.savefile.com/files/2019846 Mike "ksfarmer" wrote: Mike, Link to file http://savefile.com/projects/808744698 Hope I did it right. The Green column is the quantity entry column Frank "Mike H" wrote: upload the file here and post the link, it's free http://www.savefile.com/ Mike "ksfarmer" wrote: Mike, I got there and pasted the code in then closed the code window. It does not seem to change the operation of the sheet. I could email you a copy of the sheet I have developed so you could see what it is doing. Frank "Mike H" wrote: Hi, At the bottom of your worksheet is a tab with the sheet name on it. Right click this tab and you get a popup menu. Click 'View code' on this menu and paste the code I gave you into the white space on the right. Close VB editor (That's what your now in) by clicking the red cross and start entering your values in column A Mike "ksfarmer" wrote: Hello Mike, Thank your for the code! I am not sure what the right click on the "sheet tab" is referring to. Sorry to be so dumb! Frank "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A60")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value Target.Value = "" Application.EnableEvents = True Else 'optional line to delete any text entered in range 'Target.Value = "" End If End If End Sub Mike "ksfarmer" wrote: I have a work area of 4 columns wide and 60 rows long I need the following macro 1. I enter numbers in column A1 2. Accumulate them in Column B1 3. Clear the value in Column A1 when "enter" key is pressed 4. Enter a new number in Column A1 5. Acumulate that in Column B1 6. Clear the value in Column A1 when the "enter" key is pressed 7 repeat as data entry requires. This same routine would be applied to A2-B2 through row A60-B60 Any help appreciated.. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear value in cell when the "enter" key is pressed
Hi,
First off I'm glad I could help and thanks for the feedback, a good book ? Look here http://www.j-walk.com/ Mike "ksfarmer" wrote: Mike, One other question. Can you recommend a good book/s for learning Visual Basic and writing macros for excel? Again - Thanks "ksfarmer" wrote: Mike, Works like a champ!! One glitch was the Macro Security. I had to set it to minimum because the macro was not certified (certificate). Little info on why we wanted this routine. My son is in residential and commercial construction. He goes through plans sheet by sheet entering individual quantities of items that are required. He was doing all this with a calculator for 5 years. I have minimal working knowledge of excel so got tagged to help him. Was way above my head. So, I do sincerely THANK you for your generous help! Frank "Mike H" wrote: Hi, the problem was you had pasted the code with each line beginning with so every line was a syntax error. I've ammended the code such that if you enter data in the green column (C) it accumulates in column d. I've also removed all the formula from column d because they were circular references. http://www.savefile.com/files/2019846 Mike "ksfarmer" wrote: Mike, Link to file http://savefile.com/projects/808744698 Hope I did it right. The Green column is the quantity entry column Frank "Mike H" wrote: upload the file here and post the link, it's free http://www.savefile.com/ Mike "ksfarmer" wrote: Mike, I got there and pasted the code in then closed the code window. It does not seem to change the operation of the sheet. I could email you a copy of the sheet I have developed so you could see what it is doing. Frank "Mike H" wrote: Hi, At the bottom of your worksheet is a tab with the sheet name on it. Right click this tab and you get a popup menu. Click 'View code' on this menu and paste the code I gave you into the white space on the right. Close VB editor (That's what your now in) by clicking the red cross and start entering your values in column A Mike "ksfarmer" wrote: Hello Mike, Thank your for the code! I am not sure what the right click on the "sheet tab" is referring to. Sorry to be so dumb! Frank "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A60")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value Target.Value = "" Application.EnableEvents = True Else 'optional line to delete any text entered in range 'Target.Value = "" End If End If End Sub Mike "ksfarmer" wrote: I have a work area of 4 columns wide and 60 rows long I need the following macro 1. I enter numbers in column A1 2. Accumulate them in Column B1 3. Clear the value in Column A1 when "enter" key is pressed 4. Enter a new number in Column A1 5. Acumulate that in Column B1 6. Clear the value in Column A1 when the "enter" key is pressed 7 repeat as data entry requires. This same routine would be applied to A2-B2 through row A60-B60 Any help appreciated.. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Rounding up to nearest 10 when "Enter" is pressed or user has clickedoff the cell | Excel Programming | |||
| Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
| Can I enter a "Y" and return "Yes" in the same cell? PART 2 | Excel Programming | |||
| Can I enter a "Y" in a cell and have it return a "YES"? How? | Excel Programming | |||
Linear Mode
