Speedy line item entry with Macros

[Guest]

I'm trying to make a quick way to enter a line of data that is NOT repetitive
for time tracking.

I want to click into the first column, then Tab through the rest and have it
either autopopulate the data in the cell, or let me type a couple of letters
to bring up the appropriate menu item from a list:

DATE(auto enter today or allow to type)
HOURS(default to 0.25 but allow free entry)
Select(combo box choice of a few text items)
Select2(another quick-choice list)
Text field(free entry only)
Reference(automatically add one to the field above it)
Select3(another quick-choice list)

Not a complex spreadsheet, but some macros might save me hours per year of
typing considering I have to enter everything I do in the sheet during the
day.

Any help is appreciated as I have no idea where to start other than to fire
up the VB editor.

 

[Guest]

Chris

You can use Validation from the Data menu to set up the drop down lists for
your 3 selection columns.

Then in the VBA Project select the relevant sheet and add the following code
to the code window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Value = Empty Then
If ActiveCell.Column = 1 Then
ActiveCell.FormulaR1C1 = Date
ElseIf ActiveCell.Column = 2 Then
ActiveCell.Value = 0.25
ElseIf ActiveCell.Column = 6 Then
If ActiveCell.Row = 2 Then
ActiveCell.Value = 1
Else
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
End If
End If
End If

End Sub

This code assumes that you are starting with Date in Column A.

Regards
Rowan

PS If you use Format>Cells to unlock all cells in columns 1-7 and then
Tools>Protection>Protect Sheet to protect the sheet, tabbing along a line
will automatically take you to the next line when you get to the end of your
range.

 

[Guest]

Sweet thanks Rowan!

Chris

You can use Validation from the Data menu to set up the drop down lists for
your 3 selection columns.

Then in the VBA Project select the relevant sheet and add the following code
to the code window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Value = Empty Then
If ActiveCell.Column = 1 Then
ActiveCell.FormulaR1C1 = Date
ElseIf ActiveCell.Column = 2 Then
ActiveCell.Value = 0.25
ElseIf ActiveCell.Column = 6 Then
If ActiveCell.Row = 2 Then
ActiveCell.Value = 1
Else
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
End If
End If
End If

End Sub

This code assumes that you are starting with Date in Column A.

Regards
Rowan

PS If you use Format>Cells to unlock all cells in columns 1-7 and then
Tools>Protection>Protect Sheet to protect the sheet, tabbing along a line
will automatically take you to the next line when you get to the end of your
range.

 

[Guest]

Oh... the reference number column that i wanted to auto-increment isnt really
a number... it has two letters at the beginning followed by 4 numbers

XX1111 format

If I set one like that and then do the excel drag-to-populate thing, it
neatly increases the number part of it by 1, but the macro only does that if
the field is actually a number. Is there a programmatic way to achieve the
same result?

 

[Guest]

Try something like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim intPart As Integer
Dim strPart As String
If ActiveCell.Value = Empty Then
If ActiveCell.Column = 1 Then
ActiveCell.FormulaR1C1 = Date
ElseIf ActiveCell.Column = 2 Then
ActiveCell.Value = 0.25
ElseIf ActiveCell.Column = 6 Then
If ActiveCell.Row = 2 Then
ActiveCell.Value = "XX0001" 'change xx to your letters
Else
intPart = Right(ActiveCell.Offset(-1, 0).Value, 4) + 1
strPart = Left(ActiveCell.Offset(-1, 0).Value, 2)
If intPart < 10 Then
ActiveCell.Value = strPart & "000" & intPart
ElseIf intPart < 100 Then
ActiveCell.Value = strPart & "00" & intPart
ElseIf intPart < 1000 Then
ActiveCell.Value = strPart & "0" & intPart
Else
ActiveCell.Value = strPart & intPart
End If
End If
End If
End If
End Sub


Regards
Rowan