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.