Dynamic Validation "Input message" Text

[jhs626]

CONTEXT:

'Tasks' sheet (Task Name... Finish date)
'Schedule' sheet has two rows.

Row 4; Dates running from project start to project finish
Row 5; Highlighted cells - using Conditional Format formula. Formula turns the cell below any/all dates where there is a task with a matching 'finish' date.​

PROBLEM:

When reviewing the schedule I need to see what the 'Tasks' are that are causing cells to be highlighted. The visual objective is to understand the bigger picture in a calendar context.
​

GOAL:

Option 1: Dynamic Validation "Input message" Text

Insert the "Task Name"

(from 'Tasks' sheet) ...​

Into the Data Validation "Input message" box

(for the 'Row 5' cell)​

Directly below the Date that corresponds with the Task's 'Finish' date

(the 'Row 4' cell)
​

See attached.

Note: Data Validation box at the bottom of the 'Schedule' sheet shows the desired solution but I'd be happy with it as described above.

In the attache example; the task called "Copy" would be seen (in the "Input message") if the user clicked on the highlighted cell*

* Highlighted cell is directly below the Schedule Date (cell H4 ) that corresponds to the "Finish" date for the 'Copy' task.

* Also note: I have Conditional Formatting highlighting the cell if any of the finish dates (range) in the 'Tasks' sheet.​

Option 2: Dynamic Text Box

Above description but the "Task Name" - or multiple "Task Names" - would be placed in a named text box. Similar to what was done here: http://www.contextures.com/xlDataVal12.html. I don't understand much VBA so I'm not sure if this is applicable.​

 

[Hui]

This could be done with VBA

Can you please post a clear example of which cells should have validation and the source for the messages etc

Please be very specific

 

[jhs626]

CONTEXT:

'Tasks' sheet (Task Name... Finish date)
'Schedule' sheet has two rows.

Row 4; Dates running from project start to project finish
Row 5; Highlighted cells - using Conditional Format formula. Formula turns the cell below any/all dates where there is a task with a matching 'finish' date.​

PROBLEM:

When reviewing the schedule I need to see what the 'Tasks' are that are causing cells to be highlighted. The visual objective is to understand the bigger picture in a calendar context.
​

GOAL:

Option 1: Dynamic Validation "Input message" Text

Insert the "Task Name"

(from 'Tasks' sheet) ...​

Into the Data Validation "Input message" box

(for the 'Row 5' cell)​

Directly below the Date that corresponds with the Task's 'Finish' date

(the 'Row 4' cell)
​

See attached.

Note: Data Validation box at the bottom of the 'Schedule' sheet shows the desired solution but I'd be happy with it as described above.

In the attache example; the task called "Copy" would be seen (in the "Input message") if the user clicked on the highlighted cell*

* Highlighted cell is directly below the Schedule Date (cell H4 ) that corresponds to the "Finish" date for the 'Copy' task.

* Also note: I have Conditional Formatting highlighting the cell if any of the finish dates (range) in the 'Tasks' sheet.​

Option 2: Dynamic Text Box

Above description but the "Task Name" - or multiple "Task Names" - would be placed in a named text box. Similar to what was done here: http://www.contextures.com/xlDataVal12.html. I don't understand much VBA so I'm not sure if this is applicable.​

This could be done with VBA

Can you please post a clear example of which cells should have validation and the source for the messages etc

Please be very specific

Thank you very much Hui! After looking everywhere I was skeptical it could be done.

In short:

• The 'Validation' cells are in Row 5 of the "Schedule" sheet in the attached example.
• The 'message source' cells are in Column D of the "Tasks" sheet.

Specific:

The 'source' information presented when validation cell is clicked:

​

With many tasks (and the possibility of shared "Finish" dates) -ideally each 'TASK' would be preceeded by it's associated 'PHASE' & 'SECTION' and separated by an empty line. Please refer to the image shown between rows 28 - 43 on the "Schedule" sheet. Every task will have at least a 'SECTION' - PHASE is optional.

Caveat
Limited number of characters in a 'validation' 'Input message' box. I try to keep my 'TASK names' ('source' cells) concise but that's not always possible. With the liklihood for multiple 'TASK names' showing up in the same 'validation' cell I'm interested in your opinion (and others) as to whether or not the use of the 'Input message' box is best. I'm open to alternatives... this approach is the best I could think of but I'm far from an expert.

For those 'validation' cells that exceed the limit; perhaps it results in an error msg. that says to refer to 'deadlines_txt_box' [borrowing the approach taken here: http://www.contextures.com/xlDataVal12.html] that is below the schedule.

End Goal:
Again, the goal is to see the big-picture in a calendar/schedule view so that meeting participants can see 'TASKS' and their 'finish dates' together. Occassionally having to look below to a text box is not a big deal.​

 

[Hui]

Please see attached file:

 

[jhs626]

Thanks Hui,
I really appreciate your efforts and I think we're getting there. I have some refinement comments below but there's something more significant that I'll try to explain first. Perhaps the later is because I'm:

• too inexperienced to understand how to make it work... or
• experiencing an issue with my particular settings (which haven't been an issue for years) that are preventing the code from working properly... or
• not being clear in my posts

Bigger issue:
I'm not seeing the actual Phase, Section, or Task names in the InputMessage box. They're actuall all the same - exactly as illustrated in your embedded image.

Perhaps these are indications of ignorance or a settings issue: In my first attempt to open the sheet I didn't recieve any warnings as I opened it directly without saving. I also had Excel up and running with my original. I attempted to paste the code in my original and, in both your version and my original, the 'InputMessage' only showed the text in your code (e.g. Deadlines... Copy (C10)...etc.) not "Deadlines... Concept | Design & Mfg | Copy" as I was expecting. I made some edits to your version and saved it in My Documents and then I went to re-open it - to check something - I couldn't find it anywhere. Strange... but possibly useful inf.

So, I opened another version from your post and I was presented with some warnings this time ("... are you sure you want to open this from the internet...") but it still doesn't appear to be working. After a few attemps to modify task names - to see if that would result in an updated 'InputMessage' - I figured I'd see if there might be a reason why it would work for you and not me.

I even tried exited Excel, saving a new version (renamed) of your spreadsheet first, before opening, but no luck.

Minor Enhancements/Comments:
I included some comments/questions in the code below. Essentially; for whichever TASK has a FINISH date endered in Col N... I would like see the TASK Name be preceeded by the PHASE and SECTION [which is always the 'phase' or 'section' cell immediately above the corresponding TASK(s)].

When there are multiple tasks with the same finish date (as with the "copy" and "delete" tasks) they would be included in the same 'InputMessage' box - or text box as described earlier.

Perhaps code comments will help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B5:DB5")) Is Nothing Then Exit Sub
    'QUESTION REGARGING MsgLines BELOW
   
    'How do I get the "Copy (C10)" to reflect the actual corresponding
    'text value for each TASK Name in the TASK worksheet as illustrated
    'in the 'comment' box for Mar 20 & and Apr 3rd?
   
    'Below
    'the MsgLines would dynamically pull the 'phase' text (value found in the
    'Col D above the task)... [possibly identifying that value using going 1 col
    'left (C) and up to the firt occurrence of a cell with the text value =
    ' "phase"
MsgLine1 = "Copy (C10)"
    'this would dynamically reflext the 'section' text... using the same
    'relative position
MsgLine2 = "Delete (C12) from the Tasks sheet"
    'this would dynamically reflect the 'TASK Name' in Col D - whenever
    'it has a "FINISH" date entered in Col N
MsgLine3 = "Line 3"
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Deadlines"
        .ErrorTitle = "Schedule Sheet Error"
   
    'The InputMessage box would contain possibly several 'Phase' | 'Section'
    ' | 'Task Names' and, if there were more than one TASK... it would be
    'separated by a line [Chr(10)].
   
    'this might read > .InputMessage = MsgLine1 + Chr(32) + Chr(124) +
    'Chr(32) & MsgLine2 + Chr(10) + MsgLine3
        .InputMessage = MsgLine1 + Chr(10) & MsgLine2 + Chr(10) + MsgLine3
        .ErrorMessage = "See Schedule Sheet VBA 'Worksheet_SelectionChange'"
        .ShowInput = True
        .ShowError = True
    End With
       
End Sub

Please see attached file:
View attachment 17194