Posted 25 February 2009 - 04:01 AM
I’m just thinking out loud on this one, so please bear with me.
There are only 2 distinct methods to achieve what you want.
1 – your drop down list (irrespective of defaults or other values) is ONLY populated when a new row (record) is entered. I’m assuming that each row in your spreadsheet equates to a unique data record.
2 – you pre-populate as many rows as you want, but the actual text, default or otherwise is ‘hidden’ by making the text colour exactly the same as the cell background colour, then setting it back to normal text (black?) when a new row is entered.
Option 1 can be triggered either by a specifically written macro, or less commonly, you could write a little bit of code to create & populate your drop down by using either the ‘Worksheet_Change’ or the ‘Worksheet_SelectionChange’ built in macros.
In fact, thinking about it, Worksheet_Change will work a lot better.
Try something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, c As Long
Application.ScreenUpdating = False
r = ActiveCell.Row
c = ActiveCell.Column
If IsEmpty(Cells(r, 2)) = True Then 'This indicates it's a NEW record
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="10, 15, 20, 25, 30"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
ActiveCell.FormulaR1C1 = "30"
ElseIf IsEmpty(ActiveCell) = True Then
The only downside to this is that the macro will be invoked EVERY time a change is made to the worksheet, although it only takes a second to run, this might prove distracting.
The assumptions are:
a) Column A contains some sort of Identifier, which is only entered when a new record is entered
b) Column C contains your drop down, pre-populated with the number 30, but allowing you to also select 10, 15, 20 or 25
c) Column B must only populated AFTER Column A – this is because Column B is tested every time the macros is run. If it’s empty, it’s a new record. If it isn’t empty, the macro stops execution