Getting input within macro without the input box

[JR Hester]

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.

 

[Ron de Bruin]

Hi JR Hester

Why not unlock only the 64 cells(ctrl-1.. Protection) and protect your sheet

For Excel 2002-2003
Tools>Protection>Protect sheet
Uncheck "select locked cells"

If you have a older version you must use code to do this

 

[JR Hester]

Protecting the spreadsheet will not direct users to required cell locations

 

[Ron de Bruin]

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

 

[bhofsetz]

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

 

[JR Hester]

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

 

[Gord Dibben]

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

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

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

 

[Joerg]

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

 

[Paul D. Simon]

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.

 

[Paul D. Simon]

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
Insert>Name>Define, 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.

 

[JR Hester]

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.

 

[JR Hester]

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.

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

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

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



Protecting the spreadsheet will not direct users to required cell locations

:

Hi JR Hester

Why not unlock only the 64 cells(ctrl-1.. Protection) and protect your sheet

For Excel 2002-2003
Tools>Protection>Protect 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



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.

 

[JR Hester]

Thanks Paul, this sounds really good, will be implementing later this
afternoon!

Thanks again

 

[JR Hester]

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

 

[JR Hester]

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]

You're very welcome, JR. Glad I was able to help.

 

[lschuh]

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]

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

 

[Ron de Bruin]

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

 

[lschuh]

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?