Default Value?

[HotRod]

I created a bunch of Validation lists that give me the final "DEFAULT VALUE"
lets say it's in B2 how can I set it so that when you tab through a cell
without entering a value it defaults to the value in B2?

[Glenn Ray]

You could insert a SelectionChange subroutine in the sheet object in VBA.

If, for example, cell E2 has data validation, you could insert the following
code:

Option Explicit
Dim strVal As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("E2").Value = "" Then
strVal = Range("E2").Validation.Formula1
strVal = Mid(strVal, 2, InStr(1, strVal, ":") - 2)
Range("E2").Value = Range(strVal).Value
End If
End Sub

Any time a cell is selected in the sheet and cell E2 is blank, it will look
for the first cell specified in the data validation list and insert its value
in E2 as default. Even if the cell value is deleted, it will be replaced
with the default value.

This code will work with any cell with data validation and will correctly
get the first cell in the validation list specified for that cell (looking in
Formula1).

Glenn Ray
MOS Expert


"HotRod" wrote:

> I created a bunch of Validation lists that give me the final "DEFAULT VALUE"
> lets say it's in B2 how can I set it so that when you tab through a cell
> without entering a value it defaults to the value in B2?
>
>
>

[HotRod]

I will give it a try THANKS.