Making Excel macro for fast data entering ::: VBA ::: Excel :::

Question

I have a job to enter survey results (in paper form) to excel. I've never written any macro in Office :(

Here I what I basically need:

1. I have predefined columns (|A|B|...|AG|AH|)
2. All surveys are grouped into groups. All surveys from same group have few (like predefined) same columns. It's always same columns that 'define' group
3. All other survey answers are in numerical type [1..10].
4. Columns are not in same order as answers in servey
5. I want macro that will take my input (for example '1575'), and first place predefined values to that 'group' to |A| |B| |C|, and then |E| = 1, |D| = 5, |F| = 7, |G| = 5, and automatically start entering next row.

Anything that will give me a clue how to write this macro in more than welcome

Huge thanks for reading this...

EDIT1: I suppose question is not clear enough... I need macro that will read my keyboard input ( '1575' ) and write integers '1' '5' '7' and '5' to predefined rows. For now, I have an idea to make a form, but I need event handler that will change focus to next input when I press a key, as I want to avoid pressing TAB all the time...

Answer 1

You can try to record a macro while filling in the data once, then have a look at the code (click "edit" in the macro's window) and edit it to your likings.
If you don't understand what's happening in the VBA code, post the lines of code here. I'll see what I can do then.

Answer 2

It seems to me you need a small userform with just a textbox, a range to show the order of data entry, say:

Group A | E | D | F | G

And a little code to go with the form, say:

Dim LastCol As Integer
Dim CurRow As Integer


Private Sub UpdateCells()
Dim Col As Variant
Dim ColumnOrder As Range

'Range that specifies data entry order'
Set ColumnOrder = Range("A3:A15")

LastCol = LastCol + 1

If LastCol > WorksheetFunction.CountA(ColumnOrder) Then
    LastCol = 1
    CurRow = CurRow + 1
End If

Col = Range("A3").Offset(0, LastCol)
Range(Col & CurRow) = TextBox1.Value
TextBox1 = ""

End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
    UpdateCells
End If
End Sub

Private Sub UserForm_Initialize()
CurRow = ActiveCell.Row
End Sub

Edit Re Comment

In Excel, the last row can be a bit tricky in that Excel does not update the range after deletions until the workbook is saved. One one these may suit:

Function FindLastRow()
'Assuming that data starts in A1'
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
FindLastRow = r
End Function

.

Sub LastRow()
LastRowA = ExecuteExcel4Macro("GET.DOCUMENT(10)")
LastRowB = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
End Sub

Further information: http://www.tek-tips.com/faqs.cfm?fid=2115

Answer 3

If I understand the question correctly, you want the user to be able to type numbers only, without using enter or arrow to finalize the entry.

For example, typing "1253216..." instead of "1 2 5 ..."

If so, unfortunately there is no event that corresponds to individual characters going into the formula bar. The only events that are remotely related are Selection Change and Change. However, I don't think there is any way to use these for this purpose (they are not triggered during formula bar typing).

One idea: Make and entry column with text format and instruct the users to type the answers in one big string (like "1253216" etc.), then put formulas in each of the other cells that parses out the nth character from the input column and changes it to a value. For example:

Apply range name "Inputs" to column A, range name "N" to row 1, then in any of the other cells, put this formula:

=VALUE(MID(Inputs,N,1))

The drawback is, the user won't know if they made some error until after they finish typing the whole string of numbers and press enter or arrow.

Answer 4

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sText As String

    Application.EnableEvents = True

    If Target.Column = 1 Then  'Four digits entered in col A
        sText = CStr(Target.Value) 'convert to string
        If Len(sText) = 4 Then
            Target.Offset(0, 4).Value = Left$(sText, 1) 'write to E
            Target.Offset(0, 3).Value = Mid$(sText, 2, 1) 'D
            Target.Offset(0, 5).Value = Mid$(sText, 3, 1) 'F
            Target.Offset(0, 6).Value = Mid$(sText, 4, 1) 'G
        End If
    End If

    Application.EnableEvents = True

End Sub

If you enter your four digits in column A, this will spread them to the correct columns. It goes in the worksheet's module, not a standard module.