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.

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 FormatCells to unlock all cells in columns 1-7 and then
ToolsProtectionProtect 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.

"Chris Leeson" wrote:

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.

Sweet thanks Rowan!

"Rowan" wrote:

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 FormatCells to unlock all cells in columns 1-7 and then
ToolsProtectionProtect 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.

"Chris Leeson" wrote:

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.

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?

"Rowan" wrote:

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 FormatCells to unlock all cells in columns 1-7 and then
ToolsProtectionProtect 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.

"Chris Leeson" wrote:

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.

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

"Chris Leeson" wrote:

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?

"Rowan" wrote:

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 FormatCells to unlock all cells in columns 1-7 and then
ToolsProtectionProtect 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.

"Chris Leeson" wrote:

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.