data validation input message question

[ErinJ]

I am creating a spreadsheet that I need to represent every calendar day of the year.

Example
when A2 is selected it will show an input message of 1/1/2011
when B2 is selected it will show an input message of 1/2/2011
When C2 is selected it will show an input message of 1/3/2011
and so on and so on for the entire year.

I like the way data validation input message looks but instead of going to each cell opening data validation clicking input message and typing in each date, I would like to use a formula or VBA to change the input message.

In my mind I would have A1 through 365 columns reflect the correct date by entering the correct starting date in A:1 then extend and fill accordingly.

Then hide row 1 from being seen.

Then A2 through 365 columns as selected will show the input message but would still allow me to enter data in each cell.

How would you do this? (effectively I am creating a gantt chart to schedule jobs)
Thanks - Erin

 

[Jerry Sullivan]

Hi Erin,

You can try the event code below.

To use the code (always test new code on a copy of your workbook):
1. Right Click on the Sheet's Tab
2. Select View Code
3. Paste the Code below into the code module
4. Close the Visual Basic editor

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    With Target.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertInformation
        .IgnoreBlank = True
        .InCellDropdown = False
        .InputTitle = ""
        .InputMessage = Cells(1, Target.Column).Value
        .ShowInput = True
        .ShowError = False
    End With
End Sub

This will add validation to the cells as they are selected instead of adding them all at once to all cells in your data range.

 

[ErinJ]

Thank you very much - works great. I reposted cause I was affraid I didn't explain myself clearly. Now that I've asked the question I realize it works for all rows and I want to limit it to a certain amount of rows. Specifically 4-28 any thoughts.

 

[Jerry Sullivan]

Thank you very much - works great. I reposted cause I was affraid I didn't explain myself clearly. Now that I've asked the question I realize it works for all rows and I want to limit it to a certain amount of rows. Specifically 4-28 any thoughts.

Just add this line....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Rows("4:28")) Is Nothing Then Exit Sub
    With Target.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertInformation
        .IgnoreBlank = True
        .InCellDropdown = False
        .InputTitle = ""
        .InputMessage = Cells(1, Target.Column).Value
        .ShowInput = True
        .ShowError = False
    End With
End Sub

 

[ErinJ]

perfect! Thank you very much.