Getting input within macro without the input box
Thanks to all the posters and responders, whose Q&A I've read to get me this
far. I am looking for a simple way to direct users through a spreadsheet and accept data entry in specific locations. I have the following code that is functional, but I wonder if thre might not be a more efficient way of accomplishing this task. I would prefer to simply move to a cell, and accept data entry directly into the cell, then advance to the next assigned location when the ENTER key finalizes the cell input. With 64 non-contiguous cells requiring data entry, you amy see why I am looking for a shorter solution. Plus my users are used to teh look and feel of the spreadsheet cells, and the dialog boxes confuse them. Here is currently functional macro, for first three cells only Dim InputData As String Application.Goto reference:=Range("B1") ' go to B2 on open sheet InputData = InputBox("Promt to input", "Please input your data", "") Range("B1").Value = InputData 'Retrieve InputData value to range B1 Application.Goto reference:=Range("G1") InputData = InputBox("Promt to input", "Please input your data", "") Range("G1").Value = InputData 'place data into cell G1 Application.Goto reference:=Range("C4") 'Go to C4, accept data InputData = InputBox("Promt to input", "Please input your data", "") Range("C4").Value = InputData 'Retrieve InputData value to range C4 ' Thanks for any suggestion, pointers etc. |
Getting input within macro without the input box
Hi JR Hester
Why not unlock only the 64 cells(ctrl-1.. Protection) and protect your sheet For Excel 2002-2003 ToolsProtectionProtect sheet Uncheck "select locked cells" If you have a older version you must use code to do this -- Regards Ron de Bruin http://www.rondebruin.nl "JR Hester" wrote in message ... Thanks to all the posters and responders, whose Q&A I've read to get me this far. I am looking for a simple way to direct users through a spreadsheet and accept data entry in specific locations. I have the following code that is functional, but I wonder if thre might not be a more efficient way of accomplishing this task. I would prefer to simply move to a cell, and accept data entry directly into the cell, then advance to the next assigned location when the ENTER key finalizes the cell input. With 64 non-contiguous cells requiring data entry, you amy see why I am looking for a shorter solution. Plus my users are used to teh look and feel of the spreadsheet cells, and the dialog boxes confuse them. Here is currently functional macro, for first three cells only Dim InputData As String Application.Goto reference:=Range("B1") ' go to B2 on open sheet InputData = InputBox("Promt to input", "Please input your data", "") Range("B1").Value = InputData 'Retrieve InputData value to range B1 Application.Goto reference:=Range("G1") InputData = InputBox("Promt to input", "Please input your data", "") Range("G1").Value = InputData 'place data into cell G1 Application.Goto reference:=Range("C4") 'Go to C4, accept data InputData = InputBox("Promt to input", "Please input your data", "") Range("C4").Value = InputData 'Retrieve InputData value to range C4 ' Thanks for any suggestion, pointers etc. |
Getting input within macro without the input box
Protecting the spreadsheet will not direct users to required cell locations
"Ron de Bruin" wrote: Hi JR Hester Why not unlock only the 64 cells(ctrl-1.. Protection) and protect your sheet For Excel 2002-2003 ToolsProtectionProtect sheet Uncheck "select locked cells" If you have a older version you must use code to do this -- Regards Ron de Bruin http://www.rondebruin.nl "JR Hester" wrote in message ... Thanks to all the posters and responders, whose Q&A I've read to get me this far. I am looking for a simple way to direct users through a spreadsheet and accept data entry in specific locations. I have the following code that is functional, but I wonder if thre might not be a more efficient way of accomplishing this task. I would prefer to simply move to a cell, and accept data entry directly into the cell, then advance to the next assigned location when the ENTER key finalizes the cell input. With 64 non-contiguous cells requiring data entry, you amy see why I am looking for a shorter solution. Plus my users are used to teh look and feel of the spreadsheet cells, and the dialog boxes confuse them. Here is currently functional macro, for first three cells only Dim InputData As String Application.Goto reference:=Range("B1") ' go to B2 on open sheet InputData = InputBox("Promt to input", "Please input your data", "") Range("B1").Value = InputData 'Retrieve InputData value to range B1 Application.Goto reference:=Range("G1") InputData = InputBox("Promt to input", "Please input your data", "") Range("G1").Value = InputData 'place data into cell G1 Application.Goto reference:=Range("C4") 'Go to C4, accept data InputData = InputBox("Promt to input", "Please input your data", "") Range("C4").Value = InputData 'Retrieve InputData value to range C4 ' Thanks for any suggestion, pointers etc. |
Getting input within macro without the input box
If you hit enter you go to the next unlocked cell but if you want to go from
A1 to A10 and then to A3 then this is not working Can you post a example from your range and how you want to loop through them -- Regards Ron de Bruin http://www.rondebruin.nl "JR Hester" wrote in message ... Protecting the spreadsheet will not direct users to required cell locations "Ron de Bruin" wrote: Hi JR Hester Why not unlock only the 64 cells(ctrl-1.. Protection) and protect your sheet For Excel 2002-2003 ToolsProtectionProtect sheet Uncheck "select locked cells" If you have a older version you must use code to do this -- Regards Ron de Bruin http://www.rondebruin.nl "JR Hester" wrote in message ... Thanks to all the posters and responders, whose Q&A I've read to get me this far. I am looking for a simple way to direct users through a spreadsheet and accept data entry in specific locations. I have the following code that is functional, but I wonder if thre might not be a more efficient way of accomplishing this task. I would prefer to simply move to a cell, and accept data entry directly into the cell, then advance to the next assigned location when the ENTER key finalizes the cell input. With 64 non-contiguous cells requiring data entry, you amy see why I am looking for a shorter solution. Plus my users are used to teh look and feel of the spreadsheet cells, and the dialog boxes confuse them. Here is currently functional macro, for first three cells only Dim InputData As String Application.Goto reference:=Range("B1") ' go to B2 on open sheet InputData = InputBox("Promt to input", "Please input your data", "") Range("B1").Value = InputData 'Retrieve InputData value to range B1 Application.Goto reference:=Range("G1") InputData = InputBox("Promt to input", "Please input your data", "") Range("G1").Value = InputData 'place data into cell G1 Application.Goto reference:=Range("C4") 'Go to C4, accept data InputData = InputBox("Promt to input", "Please input your data", "") Range("C4").Value = InputData 'Retrieve InputData value to range C4 ' Thanks for any suggestion, pointers etc. |
Getting input within macro without the input box
How about using a Worksheet_SelectionChange event and comment boxes to give direction. I'm sure some of the experts on this site could see ways to improve this idea but this could be a start. You could set this up in a loop which would loop through your list of cells which need to be changed so updates/changes would be easier. Be sure all the cells you want to user to enter data into have comment boxes before testing the code otherwise it will give you errors. Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$B$2" Then Range("B1").Comment.Visible = False Range("G1").Comment.Visible = True Application.Goto reference:=Range("G1") End If If Target.Address = "$G$2" Then Range("G1").Comment.Visible = False Range("F4").Comment.Visible = True Application.Goto reference:=Range("F4") End If If Target.Address = "$F$5" Then Range("F4").Comment.Visible = False Range("D10").Comment.Visible = True Application.Goto reference:=Range("D10") End If If Target.Address = "$D$11" Then Range("D10").Comment.Visible = False End If End Sub -------------------- HTH -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=567184 |
Getting input within macro without the input box
Thanks Ron for your continuing efforts and ideas. I am using Excel 2000. With
protection turned on, pressing [enter] simply moves teh active cell to teh next contiguous cell, either to the right or down, depending upon the defined movement setting for a particular range. a sample of my movement begins at B1 and moves to G1, C4, E4, G4, D5, F5, C7, A12, E12, F12, H12. The sequence never "backs up" to a cell to the left or above. However teh spreadsheet is only 8 columns wide, with column H being rightmost column. If I could only find a way to "GOTO" next cell, "Pause" in that cell until the [enter] key is depressed, then "GOTO" next cell in my sequence that would be Ideal. Thanks again for your determination "Ron de Bruin" wrote: If you hit enter you go to the next unlocked cell but if you want to go from A1 to A10 and then to A3 then this is not working Can you post a example from your range and how you want to loop through them -- Regards Ron de Bruin http://www.rondebruin.nl "JR Hester" wrote in message ... Protecting the spreadsheet will not direct users to required cell locations "Ron de Bruin" wrote: Hi JR Hester Why not unlock only the 64 cells(ctrl-1.. Protection) and protect your sheet For Excel 2002-2003 ToolsProtectionProtect sheet Uncheck "select locked cells" If you have a older version you must use code to do this -- Regards Ron de Bruin http://www.rondebruin.nl "JR Hester" wrote in message ... Thanks to all the posters and responders, whose Q&A I've read to get me this far. I am looking for a simple way to direct users through a spreadsheet and accept data entry in specific locations. I have the following code that is functional, but I wonder if thre might not be a more efficient way of accomplishing this task. I would prefer to simply move to a cell, and accept data entry directly into the cell, then advance to the next assigned location when the ENTER key finalizes the cell input. With 64 non-contiguous cells requiring data entry, you amy see why I am looking for a shorter solution. Plus my users are used to teh look and feel of the spreadsheet cells, and the dialog boxes confuse them. Here is currently functional macro, for first three cells only Dim InputData As String Application.Goto reference:=Range("B1") ' go to B2 on open sheet InputData = InputBox("Promt to input", "Please input your data", "") Range("B1").Value = InputData 'Retrieve InputData value to range B1 Application.Goto reference:=Range("G1") InputData = InputBox("Promt to input", "Please input your data", "") Range("G1").Value = InputData 'place data into cell G1 Application.Goto reference:=Range("C4") 'Go to C4, accept data InputData = InputBox("Promt to input", "Please input your data", "") Range("C4").Value = InputData 'Retrieve InputData value to range C4 ' Thanks for any suggestion, pointers etc. |
Getting input within macro without the input box
JR
If you have some time and don't mind typing you can set up a change event in your worksheet. Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Address Case "$C$2" Range("C5").Select Case "$C$5" Range("E2").Select Case "$E$2" Range("E5").Select 'add more Cases and selects intil you get bored. 'save then start again when no so bored End Select End Sub Another method would use a named range which will give you up to about 46 cells. You can combine a couple of ranges to get your 64 cells. See Bob Phillips' site for more on named ranges and the order in which you pick cells. Gord Dibben MS Excel MVP On Tue, 1 Aug 2006 15:22:01 -0700, JR Hester wrote: Thanks Ron for your continuing efforts and ideas. I am using Excel 2000. With protection turned on, pressing [enter] simply moves teh active cell to teh next contiguous cell, either to the right or down, depending upon the defined movement setting for a particular range. a sample of my movement begins at B1 and moves to G1, C4, E4, G4, D5, F5, C7, A12, E12, F12, H12. The sequence never "backs up" to a cell to the left or above. However teh spreadsheet is only 8 columns wide, with column H being rightmost column. If I could only find a way to "GOTO" next cell, "Pause" in that cell until the [enter] key is depressed, then "GOTO" next cell in my sequence that would be Ideal. Thanks again for your determination "Ron de Bruin" wrote: If you hit enter you go to the next unlocked cell but if you want to go from A1 to A10 and then to A3 then this is not working Can you post a example from your range and how you want to loop through them -- Regards Ron de Bruin http://www.rondebruin.nl "JR Hester" wrote in message ... Protecting the spreadsheet will not direct users to required cell locations "Ron de Bruin" wrote: Hi JR Hester Why not unlock only the 64 cells(ctrl-1.. Protection) and protect your sheet For Excel 2002-2003 ToolsProtectionProtect sheet Uncheck "select locked cells" If you have a older version you must use code to do this -- Regards Ron de Bruin http://www.rondebruin.nl "JR Hester" wrote in message ... Thanks to all the posters and responders, whose Q&A I've read to get me this far. I am looking for a simple way to direct users through a spreadsheet and accept data entry in specific locations. I have the following code that is functional, but I wonder if thre might not be a more efficient way of accomplishing this task. I would prefer to simply move to a cell, and accept data entry directly into the cell, then advance to the next assigned location when the ENTER key finalizes the cell input. With 64 non-contiguous cells requiring data entry, you amy see why I am looking for a shorter solution. Plus my users are used to teh look and feel of the spreadsheet cells, and the dialog boxes confuse them. Here is currently functional macro, for first three cells only Dim InputData As String Application.Goto reference:=Range("B1") ' go to B2 on open sheet InputData = InputBox("Promt to input", "Please input your data", "") Range("B1").Value = InputData 'Retrieve InputData value to range B1 Application.Goto reference:=Range("G1") InputData = InputBox("Promt to input", "Please input your data", "") Range("G1").Value = InputData 'place data into cell G1 Application.Goto reference:=Range("C4") 'Go to C4, accept data InputData = InputBox("Promt to input", "Please input your data", "") Range("C4").Value = InputData 'Retrieve InputData value to range C4 ' Thanks for any suggestion, pointers etc. |
Getting input within macro without the input box
"bhofsetz" wrote in message ... How about using a Worksheet_SelectionChange event and comment boxes to give direction. I'm sure some of the experts on this site could see ways to improve this idea but this could be a start. You could set this up in a loop which would loop through your list of cells which need to be changed so updates/changes would be easier. Be sure all the cells you want to user to enter data into have comment boxes before testing the code otherwise it will give you errors. Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$B$2" Then Range("B1").Comment.Visible = False Range("G1").Comment.Visible = True Application.Goto reference:=Range("G1") End If If Target.Address = "$G$2" Then Range("G1").Comment.Visible = False Range("F4").Comment.Visible = True Application.Goto reference:=Range("F4") End If If Target.Address = "$F$5" Then Range("F4").Comment.Visible = False Range("D10").Comment.Visible = True Application.Goto reference:=Range("D10") End If If Target.Address = "$D$11" Then Range("D10").Comment.Visible = False End If End Sub -------------------- Instead of giving directions in the comment, why not put the next address into the comment and let the macro read it. Here is a quick and dirty version (code goes into code page of 'ThisWorkbook'): Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next 'in case activecell contains no comment Range(ActiveCell.Comment.Text).Select End Sub Assume you put a comment into cell G1 with text 'C4', the cursor would jump to C4 after input in G1. Go to Tools-Options-View and set 'comments' to 'none' if you want to completely hide them. Cheers, Joerg |
Getting input within macro without the input box
JR:
I don't know if this exactly satisfies your needs, but here's a way to have the Enter key or Tab key go to each of the cells you specify in the exact order you specify regardless of where they are. And you will not need to protect your worksheet in order for this to work. 1. Select the second cell of your input sequence, which in your case is cell G1. 2. Hold down <Ctrl and continue to select all the rest of the cells in your desired sequence ending with your first cell last (i.e., C4, E4, G4, D5, F5, C7, A12, E12, F12, H12, B1) 3. While the cells are still selected, click in the name box and give this range a short name, such as "Entry1". (Don't use the name "Input".) 4. Now click anywhere in the spreadsheet to unhighlight the cells (and again, do not protect the sheet). 5. Click the down arrow at the end of the name box and choose the name you gave this range (e.g., "Entry1"). 6. The cells you specified in #2 above will now be highlighted with cell B1 (the actual first cell in your sequence) the active cell. Now, when you make an entry in cell B1 and hit either <Enter or <Tab, the cell pointer will move to the next cell in the sequence you chose. Hope this helps you. |
Getting input within macro without the input box
Forgot to mention 2 things:
1. While <Enter or <Tab will move you forward through your sequence of cells, <Shift<Tab will allow you to go in reverse order in case you have to go back to a previous entry. 2. Excel apparently has a limit of 25 non-contiguous cells that can be used in a range name. However, you can get around this. Say you have 50 cells in your sequence. Give the first 25 cells a name (like "Entry1") and the next 25 cells another name (like "Entry2"). Then do InsertNameDefine, assign a range name (like "EntryAll") and in the Refers To field type =Entry1,Entry2 Now, when you choose a name from the drop-down in the Name box, choose EntryAll, and all 50 cells will now be highlighted. |
Getting input within macro without the input box
I am obviously missing some valuable information about private sub routines.
An dthat is due to my lack of knowledge. I am storing yoru suggestion for use as I gain the necessary tools to use these private subs Thanks for yoru suggestion and the added threads it produced. "bhofsetz" wrote: How about using a Worksheet_SelectionChange event and comment boxes to give direction. I'm sure some of the experts on this site could see ways to improve this idea but this could be a start. You could set this up in a loop which would loop through your list of cells which need to be changed so updates/changes would be easier. Be sure all the cells you want to user to enter data into have comment boxes before testing the code otherwise it will give you errors. Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$B$2" Then Range("B1").Comment.Visible = False Range("G1").Comment.Visible = True Application.Goto reference:=Range("G1") End If If Target.Address = "$G$2" Then Range("G1").Comment.Visible = False Range("F4").Comment.Visible = True Application.Goto reference:=Range("F4") End If If Target.Address = "$F$5" Then Range("F4").Comment.Visible = False Range("D10").Comment.Visible = True Application.Goto reference:=Range("D10") End If If Target.Address = "$D$11" Then Range("D10").Comment.Visible = False End If End Sub -------------------- HTH -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=567184 |
Getting input within macro without the input box
The idea of using case, select statements certainly seemed right on target.
Unfortunately my lack of experience with teh macro language left me stymied in activating the Private subroutine. I tried nesting it inside a standard macro sub, but that did no work for me. Thanks for the suggestion. "Gord Dibben" wrote: JR If you have some time and don't mind typing you can set up a change event in your worksheet. Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Address Case "$C$2" Range("C5").Select Case "$C$5" Range("E2").Select Case "$E$2" Range("E5").Select 'add more Cases and selects intil you get bored. 'save then start again when no so bored End Select End Sub Another method would use a named range which will give you up to about 46 cells. You can combine a couple of ranges to get your 64 cells. See Bob Phillips' site for more on named ranges and the order in which you pick cells. Gord Dibben MS Excel MVP On Tue, 1 Aug 2006 15:22:01 -0700, JR Hester wrote: Thanks Ron for your continuing efforts and ideas. I am using Excel 2000. With protection turned on, pressing [enter] simply moves teh active cell to teh next contiguous cell, either to the right or down, depending upon the defined movement setting for a particular range. a sample of my movement begins at B1 and moves to G1, C4, E4, G4, D5, F5, C7, A12, E12, F12, H12. The sequence never "backs up" to a cell to the left or above. However teh spreadsheet is only 8 columns wide, with column H being rightmost column. If I could only find a way to "GOTO" next cell, "Pause" in that cell until the [enter] key is depressed, then "GOTO" next cell in my sequence that would be Ideal. Thanks again for your determination "Ron de Bruin" wrote: If you hit enter you go to the next unlocked cell but if you want to go from A1 to A10 and then to A3 then this is not working Can you post a example from your range and how you want to loop through them -- Regards Ron de Bruin http://www.rondebruin.nl "JR Hester" wrote in message ... Protecting the spreadsheet will not direct users to required cell locations "Ron de Bruin" wrote: Hi JR Hester Why not unlock only the 64 cells(ctrl-1.. Protection) and protect your sheet For Excel 2002-2003 ToolsProtectionProtect sheet Uncheck "select locked cells" If you have a older version you must use code to do this -- Regards Ron de Bruin http://www.rondebruin.nl "JR Hester" wrote in message ... Thanks to all the posters and responders, whose Q&A I've read to get me this far. I am looking for a simple way to direct users through a spreadsheet and accept data entry in specific locations. I have the following code that is functional, but I wonder if thre might not be a more efficient way of accomplishing this task. I would prefer to simply move to a cell, and accept data entry directly into the cell, then advance to the next assigned location when the ENTER key finalizes the cell input. With 64 non-contiguous cells requiring data entry, you amy see why I am looking for a shorter solution. Plus my users are used to teh look and feel of the spreadsheet cells, and the dialog boxes confuse them. Here is currently functional macro, for first three cells only Dim InputData As String Application.Goto reference:=Range("B1") ' go to B2 on open sheet InputData = InputBox("Promt to input", "Please input your data", "") Range("B1").Value = InputData 'Retrieve InputData value to range B1 Application.Goto reference:=Range("G1") InputData = InputBox("Promt to input", "Please input your data", "") Range("G1").Value = InputData 'place data into cell G1 Application.Goto reference:=Range("C4") 'Go to C4, accept data InputData = InputBox("Promt to input", "Please input your data", "") Range("C4").Value = InputData 'Retrieve InputData value to range C4 ' Thanks for any suggestion, pointers etc. |
Getting input within macro without the input box
Thanks Paul, this sounds really good, will be implementing later this
afternoon! Thanks again "Paul D. Simon" wrote: JR: I don't know if this exactly satisfies your needs, but here's a way to have the Enter key or Tab key go to each of the cells you specify in the exact order you specify regardless of where they are. And you will not need to protect your worksheet in order for this to work. 1. Select the second cell of your input sequence, which in your case is cell G1. 2. Hold down <Ctrl and continue to select all the rest of the cells in your desired sequence ending with your first cell last (i.e., C4, E4, G4, D5, F5, C7, A12, E12, F12, H12, B1) 3. While the cells are still selected, click in the name box and give this range a short name, such as "Entry1". (Don't use the name "Input".) 4. Now click anywhere in the spreadsheet to unhighlight the cells (and again, do not protect the sheet). 5. Click the down arrow at the end of the name box and choose the name you gave this range (e.g., "Entry1"). 6. The cells you specified in #2 above will now be highlighted with cell B1 (the actual first cell in your sequence) the active cell. Now, when you make an entry in cell B1 and hit either <Enter or <Tab, the cell pointer will move to the next cell in the sequence you chose. Hope this helps you. |
Getting input within macro without the input box
This sounds like what I was looking for! Will be putting this to work later
today. Thanks for pointing me in this direction, sounds like just the simple solution I was looking for! Thanks again "Joerg" wrote: "bhofsetz" wrote in message ... How about using a Worksheet_SelectionChange event and comment boxes to give direction. I'm sure some of the experts on this site could see ways to improve this idea but this could be a start. You could set this up in a loop which would loop through your list of cells which need to be changed so updates/changes would be easier. Be sure all the cells you want to user to enter data into have comment boxes before testing the code otherwise it will give you errors. Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$B$2" Then Range("B1").Comment.Visible = False Range("G1").Comment.Visible = True Application.Goto reference:=Range("G1") End If If Target.Address = "$G$2" Then Range("G1").Comment.Visible = False Range("F4").Comment.Visible = True Application.Goto reference:=Range("F4") End If If Target.Address = "$F$5" Then Range("F4").Comment.Visible = False Range("D10").Comment.Visible = True Application.Goto reference:=Range("D10") End If If Target.Address = "$D$11" Then Range("D10").Comment.Visible = False End If End Sub -------------------- Instead of giving directions in the comment, why not put the next address into the comment and let the macro read it. Here is a quick and dirty version (code goes into code page of 'ThisWorkbook'): Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next 'in case activecell contains no comment Range(ActiveCell.Comment.Text).Select End Sub Assume you put a comment into cell G1 with text 'C4', the cursor would jump to C4 after input in G1. Go to Tools-Options-View and set 'comments' to 'none' if you want to completely hide them. Cheers, Joerg |
Getting input within macro without the input box
Thanks PAul that was exactly the kind of solution I was looking for. Simple
and straight forward. Some additional points of clarification for anyone else following this train: IN Excel 2000, I was able to link a total of 44 individual cells, Admittedly 32 or so were contiguous. By defining these contiguous cells as a range within the named range, I was able to get ALL cells into one named range. I then used a command button with macro to "goto" the range. This solution really fit the niche here. Thanks for your idea and directions. I certainly learned a new trick here, that will get lots of use in the future! Thanks again JR "Paul D. Simon" wrote: JR: I don't know if this exactly satisfies your needs, but here's a way to have the Enter key or Tab key go to each of the cells you specify in the exact order you specify regardless of where they are. And you will not need to protect your worksheet in order for this to work. 1. Select the second cell of your input sequence, which in your case is cell G1. 2. Hold down <Ctrl and continue to select all the rest of the cells in your desired sequence ending with your first cell last (i.e., C4, E4, G4, D5, F5, C7, A12, E12, F12, H12, B1) 3. While the cells are still selected, click in the name box and give this range a short name, such as "Entry1". (Don't use the name "Input".) 4. Now click anywhere in the spreadsheet to unhighlight the cells (and again, do not protect the sheet). 5. Click the down arrow at the end of the name box and choose the name you gave this range (e.g., "Entry1"). 6. The cells you specified in #2 above will now be highlighted with cell B1 (the actual first cell in your sequence) the active cell. Now, when you make an entry in cell B1 and hit either <Enter or <Tab, the cell pointer will move to the next cell in the sequence you chose. Hope this helps you. |
Getting input within macro without the input box
You're very welcome, JR. Glad I was able to help.
|
Getting input within macro without the input box
I am trying to use the code you put in this post but I don't know where in
the vba it goes. Should it go in a sub from within the worksheet? Where are the variables declared? Also I can't get most of the code to work. Should it be all in one place? I want to open the spreadsheet on the first line that the user will be putting his data into. I then want to go from the next user cell and so on until the end of the form. How would I go about using your code and where? "JR Hester" wrote: Thanks to all the posters and responders, whose Q&A I've read to get me this far. I am looking for a simple way to direct users through a spreadsheet and accept data entry in specific locations. I have the following code that is functional, but I wonder if thre might not be a more efficient way of accomplishing this task. I would prefer to simply move to a cell, and accept data entry directly into the cell, then advance to the next assigned location when the ENTER key finalizes the cell input. With 64 non-contiguous cells requiring data entry, you amy see why I am looking for a shorter solution. Plus my users are used to teh look and feel of the spreadsheet cells, and the dialog boxes confuse them. Here is currently functional macro, for first three cells only Dim InputData As String Application.Goto reference:=Range("B1") ' go to B2 on open sheet InputData = InputBox("Promt to input", "Please input your data", "") Range("B1").Value = InputData 'Retrieve InputData value to range B1 Application.Goto reference:=Range("G1") InputData = InputBox("Promt to input", "Please input your data", "") Range("G1").Value = InputData 'place data into cell G1 Application.Goto reference:=Range("C4") 'Go to C4, accept data InputData = InputBox("Promt to input", "Please input your data", "") Range("C4").Value = InputData 'Retrieve InputData value to range C4 ' Thanks for any suggestion, pointers etc. |
Getting input within macro without the input box
I can't take credit for the code referenced in the oroginal post. It was
imported from response to an earlier thread, and unfortunately I can't recall the resxponder who offered this solution. I believe this was copied into a worksheet change event, but can't recall the method of accessing that particular macro code. There are no "variables" other than the "InputData" declared in the first line. It was setup to automatically ec\xecute as soon as the spreadshett was opened. This was functioning, and I eventually duplicated this sequence through about 64 iterations totaling some 192 lines of code. This process worked, however it was confusing my users as the dialog box ususally hid the actual cell the data was being stored to. I reposted this thread t\looking for a more simple and direct way to simply move teh active cell from one specific location to another upon pressing teh ENTER key. PAUL SIMON offered the most efficient solution with a series of defined ranges. If you simply need to move from one non-contigous cell to another, I suggest you look through the Paul Simon postings above! A very simple and functional solution. Hope thsi helps "lschuh" wrote: I am trying to use the code you put in this post but I don't know where in the vba it goes. Should it go in a sub from within the worksheet? Where are the variables declared? Also I can't get most of the code to work. Should it be all in one place? I want to open the spreadsheet on the first line that the user will be putting his data into. I then want to go from the next user cell and so on until the end of the form. How would I go about using your code and where? "JR Hester" wrote: Thanks to all the posters and responders, whose Q&A I've read to get me this far. I am looking for a simple way to direct users through a spreadsheet and accept data entry in specific locations. I have the following code that is functional, but I wonder if thre might not be a more efficient way of accomplishing this task. I would prefer to simply move to a cell, and accept data entry directly into the cell, then advance to the next assigned location when the ENTER key finalizes the cell input. With 64 non-contiguous cells requiring data entry, you amy see why I am looking for a shorter solution. Plus my users are used to teh look and feel of the spreadsheet cells, and the dialog boxes confuse them. Here is currently functional macro, for first three cells only Dim InputData As String Application.Goto reference:=Range("B1") ' go to B2 on open sheet InputData = InputBox("Promt to input", "Please input your data", "") Range("B1").Value = InputData 'Retrieve InputData value to range B1 Application.Goto reference:=Range("G1") InputData = InputBox("Promt to input", "Please input your data", "") Range("G1").Value = InputData 'place data into cell G1 Application.Goto reference:=Range("C4") 'Go to C4, accept data InputData = InputBox("Promt to input", "Please input your data", "") Range("C4").Value = InputData 'Retrieve InputData value to range C4 ' Thanks for any suggestion, pointers etc. |
Getting input within macro without the input box
You can test this code example as a macro
Change this line Set myRange = Range("a1,b3,d7") Sub With_InputBox() Dim myRange As Range Dim myCell As Range Dim myAns As Variant Set myRange = Range("a1,b3,d7") For Each myCell In myRange.Cells myAns = InputBox _ (prompt:="Please enter something for cell: " _ & myCell.Address, Title:="Get Data") If myAns < "" Then myCell.Value = myAns End If Next myCell End Sub You can run the macro when you activate the worksheet See http://www.cpearson.com/excel/vbe.htm -- Regards Ron de Bruin http://www.rondebruin.nl "JR Hester" wrote in message ... I can't take credit for the code referenced in the oroginal post. It was imported from response to an earlier thread, and unfortunately I can't recall the resxponder who offered this solution. I believe this was copied into a worksheet change event, but can't recall the method of accessing that particular macro code. There are no "variables" other than the "InputData" declared in the first line. It was setup to automatically ec\xecute as soon as the spreadshett was opened. This was functioning, and I eventually duplicated this sequence through about 64 iterations totaling some 192 lines of code. This process worked, however it was confusing my users as the dialog box ususally hid the actual cell the data was being stored to. I reposted this thread t\looking for a more simple and direct way to simply move teh active cell from one specific location to another upon pressing teh ENTER key. PAUL SIMON offered the most efficient solution with a series of defined ranges. If you simply need to move from one non-contigous cell to another, I suggest you look through the Paul Simon postings above! A very simple and functional solution. Hope thsi helps "lschuh" wrote: I am trying to use the code you put in this post but I don't know where in the vba it goes. Should it go in a sub from within the worksheet? Where are the variables declared? Also I can't get most of the code to work. Should it be all in one place? I want to open the spreadsheet on the first line that the user will be putting his data into. I then want to go from the next user cell and so on until the end of the form. How would I go about using your code and where? "JR Hester" wrote: Thanks to all the posters and responders, whose Q&A I've read to get me this far. I am looking for a simple way to direct users through a spreadsheet and accept data entry in specific locations. I have the following code that is functional, but I wonder if thre might not be a more efficient way of accomplishing this task. I would prefer to simply move to a cell, and accept data entry directly into the cell, then advance to the next assigned location when the ENTER key finalizes the cell input. With 64 non-contiguous cells requiring data entry, you amy see why I am looking for a shorter solution. Plus my users are used to teh look and feel of the spreadsheet cells, and the dialog boxes confuse them. Here is currently functional macro, for first three cells only Dim InputData As String Application.Goto reference:=Range("B1") ' go to B2 on open sheet InputData = InputBox("Promt to input", "Please input your data", "") Range("B1").Value = InputData 'Retrieve InputData value to range B1 Application.Goto reference:=Range("G1") InputData = InputBox("Promt to input", "Please input your data", "") Range("G1").Value = InputData 'place data into cell G1 Application.Goto reference:=Range("C4") 'Go to C4, accept data InputData = InputBox("Promt to input", "Please input your data", "") Range("C4").Value = InputData 'Retrieve InputData value to range C4 ' Thanks for any suggestion, pointers etc. |
Getting input within macro without the input box
I was trying to use the method you described but nothing is activated. I put
data in several cells. I then went to the 2nd cell and held down the control key, enter and did that for a number of cells. I then tried to name it and nothing happens. What am I doing wrong? "Paul D. Simon" wrote: JR: I don't know if this exactly satisfies your needs, but here's a way to have the Enter key or Tab key go to each of the cells you specify in the exact order you specify regardless of where they are. And you will not need to protect your worksheet in order for this to work. 1. Select the second cell of your input sequence, which in your case is cell G1. 2. Hold down <Ctrl and continue to select all the rest of the cells in your desired sequence ending with your first cell last (i.e., C4, E4, G4, D5, F5, C7, A12, E12, F12, H12, B1) 3. While the cells are still selected, click in the name box and give this range a short name, such as "Entry1". (Don't use the name "Input".) 4. Now click anywhere in the spreadsheet to unhighlight the cells (and again, do not protect the sheet). 5. Click the down arrow at the end of the name box and choose the name you gave this range (e.g., "Entry1"). 6. The cells you specified in #2 above will now be highlighted with cell B1 (the actual first cell in your sequence) the active cell. Now, when you make an entry in cell B1 and hit either <Enter or <Tab, the cell pointer will move to the next cell in the sequence you chose. Hope this helps you. |
All times are GMT +1. The time now is 12:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com