Relieve Reptitive Stress

[shades]

Here is an interesting problem. Someone has to enter a number (1-5) into hundreds of cells. However, the person would like to not have to hit the tab or return between each entry. I suspect VBA would be the only way to handle this. My thought is to use a Private_SelectionChange, but haven't come up with anything that works. And there is another consideration - suppose that the data field is six columns wide, then immediately go to the next row.

Thus, the person enters 123453421

1 goes into A2, 2 into B2, 3 into C2, 4 into D2, 5 into E2, 1 into F2, 3 into A3, 4 into B3, 2 into C3, etc.

My original thought was something like this for Column A only:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim i As Integer
    If Intersect(Target, Range("A2:A100")) Is Nothing Then
        Exit Sub
    Select Case i
        Case 1
            Target.Value = i
            Target.Offset(1, 0).Select
        Case 2
Case 3
Case 4
Case 5
Case Else
End Select
End Sub

Any ideas on how to proceed?

[mdmackillop]

Hi Shades,
Foe entering data in restricted columns, have a look here. I use it all the time now
http://www.vbaexpress.com/kb/getarticle.php?kb_id=347

For your main question, try the following. Note that you'll need to start your list of numbers with an appostophe, ie make it text, or excell will do nasty things to it.
The code needs a little more work to carry on where the fill finished, if this is neccessary.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, k As Long, w As Long, m As Long, x As Long, Tmp As String
    Application.EnableEvents = False
    k = 3 'StartRow
    x = 2 'StartColumn
    w = 3 'No of cols
    m = 0 'Character order
    Tmp = Target.Text
    For i = 1 To Int(Len(Tmp) / w)
        k = k + 1
        For j = x To x + w
            m = m + 1
            Cells(k, j) = Mid(Tmp, m, 1)
        Next j
    Next i
    Application.EnableEvents = True
End Sub

[mvidas]

Hi Shades,

First off, I have no idea about vba on a mac. That being said, have you considered a userform with textboxes? Take a look at the following example, you specify the max length for each of the text boxes, and once that max length has been reached, it goes to the next textbox. After the last is filled out, it goes to the Done button, which when clicked populates those values in the specified cells. So your user only has to enter "12345<enter>" and a1 will get 1, b2 will get 2, c3 will get 3, etc.

Matt

[shades]

Thanks, guys. This gives me more to chew on.

Hi Shades,

First off, I have no idea about vba on a mac. That being said, have you considered a userform with textboxes? Take a look at the following example, you specify the max length for each of the text boxes, and once that max length has been reached, it goes to the next textbox. After the last is filled out, it goes to the Done button, which when clicked populates those values in the specified cells. So your user only has to enter "12345<enter>" and a1 will get 1, b2 will get 2, c3 will get 3, etc.

Matt

I like this approach - I had originally thought about this, but had no idea where to go. Now, given what you have produced, This works for the first row, but what about next row? My thought was to do something like this:

Private Sub DoneButton_Click()
Dim i As Integer
Dim LastRow As Long
LastRow = InputBox("Number of Rows")
For i = 2 To LastRow
ThisWorkbook.ActiveSheet.Cells(2, 1) = TextBox1.Text
ThisWorkbook.ActiveSheet.Cells(2, 2) = TextBox2.Text
ThisWorkbook.ActiveSheet.Cells(2, 3) = TextBox3.Text
ThisWorkbook.ActiveSheet.Cells(2, 4) = TextBox4.Text
ThisWorkbook.ActiveSheet.Cells(2, 5) = TextBox5.Text
Next i
Unload Me
End Sub

[eidt: That's not the approach to the lastrow I want.... I am working on it ]
But this leaves a problem. I suppose it would be better to locate the next blank cell in Column A for the next series of entries?

[mvidas]

It would be better to locate the last used row, and you could remove the "Unload Me" line too. I actually only made this as a quick example, assuming you might create many more text boxes for all of the input.
Does the entry have a pattern to it? Your example shows A2:F2 being entered, and then F3, if that were the case I'd say it should have 6 text boxes (or 12, or whatever the data comes in as), and put a 2nd button on the sheet to stop data entry. The entry button would enter the data on the next row, and clear the text boxes. The stop button would unload the form.
If you can give me a layout of how you would like it (probably based on how the end user gets the data), I can gladly modify the above userform (or create a new one) to be exactly as you need it. Let me know!

[shades]

Thanks. This helps me clarify what needs to be done. I would like to experiment with this on my own, then come back for the experts to clean it up.

I'm not on my Mac right now, so can't fully test it. But this is great help. Thanks!

[mvidas]

We'll be here when you need us!

[shades]

Okay, here is what I have come up with - but it errors out on the first text line (Application-defined or object-defined error):

Private Sub DoneButton_Click()
Dim NextRow As Long
NextRow = ActiveSheet.Range("A65536").End(xlUp).Row + 1
ThisWorkbook.ActiveSheet.Cells(NextRow, 0) = TextBox1.Text ' here is problem
With ActiveSheet
.Cells(NextRow, 1) = TextBox2.Text
.Cells(NextRow, 2) = TextBox3.Text
.Cells(NextRow, 3) = TextBox4.Text
.Cells(NextRow, 4) = TextBox5.Text
End With
End Sub

Any ideas? This is still with Windows, XL 2002

[Zack Barresse]

Did you actually name your object (not just the Caption) DoneButton??

[shades]

No, Zack, I didn't. Is that an issue?

I adjusted the code above and got it to work one time.

Private Sub DoneButton_Click()
Dim NextRow As Long
NextRow = Range("A65536").End(xlUp).Row + 1
With ActiveSheet
.Range("A" & NextRow) = TextBox1.Text
.Range("B" & NextRow) = TextBox2.Text
.Range("C" & NextRow) = TextBox3.Text
.Range("D" & NextRow) = TextBox4.Text
.Range("F" & NextRow) = TextBox5.Text
End With
End Sub

Now, I have to clear the Text boxes and be ready for the next line.

[Zack Barresse]

Yeah, it's an issue when dealing with UserForms and such. One thing to watch out for is if you already have code associated with an Object Control and then you change the name, the code will not adopt the new name. It's a real pain in the kisser.

[shades]

Thanks, Zack. I will have to study that.

I just got the textboxes to clear, now I only have to set the focus back to TextBox1.

Thanks for being patient with me. I'm trying to work through this as best I can. Then tonight I get to try it on the Mac!

Okay, here is the "finished" product so far: It works as intended, for five columns:

Private Sub DoneButton_Click()
Dim NextRow As Long
NextRow = Range("A65536").End(xlUp).Row + 1
With ActiveSheet
.Range("A" & NextRow) = TextBox1.Text
.Range("B" & NextRow) = TextBox2.Text
.Range("C" & NextRow) = TextBox3.Text
.Range("D" & NextRow) = TextBox4.Text
.Range("E" & NextRow) = TextBox5.Text
End With
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox1.SetFocus
End Sub

I went through and changed the button to "Enter" and changed it in all the code - works great. I also added a Cancel button to the UserForm to unload it.

Works almost as if I knew what the heck was going on!

Thanks, everyone.

Now, the Mac.....

[johnske]

Yeah, it's an issue when dealing with UserForms and such. One thing to watch out for is if you already have code associated with an Object Control and then you change the name, the code will not adopt the new name. It's a real pain in the kisser.

Hi Shades, yeh, it's a right royal pain alright... You have to go to the properties window for the object (the command button in this case) and re-name it in there, then cut the code that you have for CommandButton1 (or whatever), go to the code-pane and select the new name for the button and paste it in

Private Sub DoneButton_Click()
 ... your code...
End Sub

[tommy bak]

Hi Shades
I might have misunderstood the question, but if you're using a form with a textbox you could just check for KeyPressed

Public lRow                   As Long
Public lCol                   As Long

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
   If lCol = 6 Then
      lCol = 1
      lRow = lRow + 1
   Else
      lCol = lCol + 1
   End If
   Range("a1").Offset(lRow, lCol - 1) = Chr(KeyAscii)
   TextBox1 = "" 
End Sub

BR
Tommy Bak

[shades]

Thanks, Tommy. I keep learning every time I come here.

Solved as far as the Windows side - never could get it to work on the Mac side. But the person requesting decided to approach it from an entirely different perspective,... which he has not bothered to share with me... must be the mushroom syndrome.