Default Cell Value

Question

Hi

I have a small problem with default cell values.

Currently I have a dropdown list box in a cell which has 3 options. I have restraints on the cell through data validation to prevent incorrect data entry, but I do not have a default value set.

What i need is a default value in this cell so if the user forgets to select an option, or if they select then delete without re-selecting an option, the cell will automatically revert to is default entry.

If someone can help me out, that would be great.

Thanks

Answer 1

The two lines at the very top of the code need to be changed to read like this:

Const dvCellAddress = "$B$4" ' change as needed to the address of the data validated cell

Const myDefault = "Apple" ' change as required to other text or number

Also here's a link to instructions on how to get the code into the proper place in your workbook (into the worksheet's code module) with at least one graphic:

http://www.contextures.com/xlvba01.html#Worksheet

Be sure you follow the instructions in the section for (my underline added)

Copy Excel VBA Code to a Worksheet Module

That page also has additional instructions for inserting code in other areas, so you might want to bookmark that page for future use.

No problem, ask for all the help you need.

NOTE: If when you click the [View Code] option, if you see the words

Option Explicit

Then put all of the code I provided just below those words.  If those words aren't there, don't worry about - depends on certain settings in Excel as to whether they are there by default or not.

Answer 2

Thanks very much,

Helped a lot :)

Answer 3

Thanks for your help,

code looks great but unfortunately I do not have experience With creating VBA codes.

If the cell in question is B4 and the default value must be Apple, then where would I enter that information in your code?

Would be great if you could help me just once more :)

Thank You

Answer 4

Some worksheet event handling should fix things up for you.  To put the code below to work:

Open the workbook, select the sheet with the cell that has data validation applied to it.

RIGHT-click on that sheet's name tab and choose "View Code" from the popup list.

Copy the code below, paste it into the code module presented to you and edit the two Const values as needed for your workbook/sheet.  Save the file.

'for this address you must put the $ infront of both the column letter(s) and the row number

Const dvCellAddress = "$C$1" ' change as needed to the address of the data validated cell

Const myDefault = "One" ' change as required to other text or number

Private Sub Worksheet_Activate()

  'check the cell anytime the sheet is selected

  'and if the data validated cell is empty, enter the default value

  If IsEmpty(Range(dvCellAddress)) Then

    Application.EnableEvents = False

    Target = myDefault

    Application.EnableEvents = True

  End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address <> dvCellAddress Then

    'not our data validated cell

    Exit Sub

  End If

  'here when change takes place in the data validated cell

  'is it empty?

  If IsEmpty(Target) Then

    'prevent re-entry by this change

    Application.EnableEvents = False

    Target = myDefault

    Application.EnableEvents = True

  End If

End Sub