cell value to show default value when a user clears/deletes its contents

[Upex]

Hi All,

I have a series of cells that are populate by the same validation list using a drop down selection. The first option on the list is "Select Planned Course".

What I'm after is that these cells will go back to "Select Planned Course" if a user deletes their contents. i.e. a user chooses course 7, then deletes it, I need it to go back to "Select Planned Course", rather than being blank.

Is there a way I can do this via either refering to the validation option or by having a default in the code? (it doesnt matter if hard written in the code as it will not change).

The cells in question are on row 15 and go up to column I by standard, but the user has a button to add on another which can be used for as many times as needed, so realistically I need this to work on 15:15 or have a way of adding it to the "add course" code so it expands when the range does.

this is the add course code:

Sub P1_AddPlanned()
Application.ScreenUpdating = False
    With Sheets("forecast of 1st period")
        With .Cells(15, .Columns.Count).End(xlToLeft).Resize(18)
            .Copy .Offset(, 1)
            On Error Resume Next
            .Offset(, 1).SpecialCells(xlCellTypeConstants).ClearContents
            .Offset(, 1).Cells(1).value = "Select Planned Course"
            On Error GoTo 0
        End With
    End With
    With Sheets("review of 1st period")
        With .Cells(17, .Columns.Count).End(xlToLeft).Resize(29)
            .Copy .Offset(, 1)
        End With
    End With
Application.ScreenUpdating = True
End Sub

Many thanks for your suggestions.

Regards, Upex

 

[Andrew Poulsom]

You can use the Worksheet_Change event procedure.

 

[Upex]

Hi Andrew,

Thanks for your speedy reply.

I'm aware of this feature but unsure how I would restrict that to a range i.e. 15:15 so that other areas could be amended without calling the proceedure. Also, would the proceedure also be called when a user selected their course from the validation? as I only need it to run when the cell is cleared, not changed.

Fairly new to VBA, sorry if I'm asking simple questions.

Many thanks if your able to offer any help with these points.

Regards, Upex.

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Application.Intersect(Target, Rows("15:15")) Is Nothing Then
        If Len(Target.Value) = 0 Then
            Application.EnableEvents = False
            Target.Value = "Select Planned Course"
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[Upex]

Mr Poulsom,

Many many thanks for your help on this. It works exactly as I wanted and has educated me a little further in the 'art' of VBA.

Kind regards and thanks again,

Upex.

 

[waitingtohear]

Hi Folks!

I too needed this coding and it works for my needs also. Just one thing is off for me. Instead of having the default value be of any value I want, I need it to input a value already found in a specific cell on the worksheet. (to incorporate a VLOOKUP value situation involved with my needs)

I assume I just need to know the correct adjustment to make in the existing code which would point to a specic worksheet cell content instead of using:

Target.Value = "any text goes here"

 

[Upex]

Try. Target.value = range(" your cell reference ").value

Should work if I understand your issue correctly