PDA

View Full Version : Solved: Autopopulate Cell With Default List Value



hobbiton73
12-23-2012, 08:05 AM
Hi, I wonder whether someone may be able to help me please.

The section of code below automatically populates column "A" with the date, and column "AS" with the text value "No" when a new record is created withina Excel spreadsheet.

If Not Intersect(Target, Range("B5:AD400", "AF5:AQ400")) Is Nothing Then
If Target.Value <> preValue And Target.Value <> "" Then
Application.EnableEvents = False
With Rows(Target.Row)
.Range("A1").Value = Date
.Range("AS1").Value = "No"
End With
In column "C" I'm using a validation list with the values:
Select
Yes
NoCould someone perhaps tell me please how I may go about changing the functionality, so that as soon as column "A" is populated with the date, the first value from my list i.e. "Select" is automatically populated in column "C"?

Many thanks and kind regards

Chris

p45cal
12-23-2012, 04:10 PM
add
.range("C1")="Select"
next to the similar lines.

hobbiton73
12-24-2012, 07:14 AM
Hi @p45cal, thank you very much for taking the time to reply to my post and for the suggestion.

Unfortunately, I have tried this solution, and although the text value appears when a new record is created, the user is then unable to select a value from the drop down menu, without the cell value reverting back to "Select".

Many thanks and kind regards

mdmackillop
12-24-2012, 08:26 AM
Check for validation in the target cell
If Target.Validation.Creator Then .Range("C1") = "Select"

david000
12-24-2012, 09:18 AM
Can you upload a sample workbook or post more of the code.

hobbiton73
12-24-2012, 09:19 AM
Hi @mdmackillop, thank you very much for replying to my post.

I did try your solution, but unfortunately I encounter the same issue as I do when I use
.range("C1")="Select". The text value "Select" correctly populates the cell in column, "C" but after selecting a value from the drop down menu, the cell reverts back to the "Select" text.

Many thanks and kind regards

hobbiton73
12-24-2012, 09:29 AM
Hi @david000, thnak you for replying to my post. Please find below a link to the file.

This is a cut down version which only shows the pertinent columns.
https://www.box.com/s/2ucjikdr0r471q7ppvbl

To start enter your name in column "B".

Many thanks and kind regards

mdmackillop
12-24-2012, 10:48 AM
Try this variation
If ActiveCell.Validation.Type <> 3 Then .Range("C1") = "Select"

hobbiton73
12-24-2012, 11:11 AM
Hi @mdmackillop, thank you very much for this.

Unfortunately, when I try this, the text value no longer populates column "C".

Many thanks and kind regards

omp001
12-24-2012, 11:23 AM
Maybe:
If Target.Column <> 3 Then .Range("C1") = "Select"

hobbiton73
12-24-2012, 11:30 AM
Hi @omp001, thank you for taking the time to reply to my post.

I've tried your solution and it works great!

Thank you very much and kind regards

omp001
12-24-2012, 11:44 AM
Hello, hobbiton73.
Glad it helped.
Thanks for the feedback and Merry Christmas to all.

hobbiton73
12-25-2012, 09:44 AM
A Merry Christmas to you also.