Linking a Dropdown List with a Input box

Question

My current situation is following: I am creating a project management worksheet with Gantt diagram in Excel. In order to input the dates of each activity I am giving three choices in a dropdown list.  The three different mechanisms for the activities are following: If the activity is simultaneous, use the same start date of the previous activity; if the activity is consecutive then used the end date of the previous activity +1 day and if the activity is independent, I am asking the user to input the date. (IF(G68="","",(IF(L70="Simultaneous",F68,(IF(L70="Consecutive",WORKDAY.INTL(G68,1,1,References!$B$5:$B$16), "Input date")))))

Where F68= Previous Activity Start Date,  G68=Previous Activity End Date, L70= List cell with the list of Activity type option (Simultaneous, Consecutive, Independent(which here is represented by Input date)

Now what I am trying to do is to link an input message when the user chooses the Independent activity from the dropdown list. Once the user chooses the option of "Independent activity" from the dropdown list, I want an input message to prompt (without having the need to click on a button, I want for the prompt to automatically appear) asking to input the start date and after the start date has been inputted I want for it to automatically be inputted in the  column corresponding to the Activity start date.

I have tried something on VBA, but it is actually not linking the information, or it needs a button to run the command.

Your help is greatly appreciated, Thank you!! 

Kindest

Answer 1

There is only one Worksheet_Change event so all the code has to go in the one procedure.

The code you have written doesn't look quite right.

It would be better to use a formula than a value for the Consecutive and Simultaneous cases - otherwise if the earlier plan dates move the relationship will not be preserved.

You might have meant something like this.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Intersect(Target, Me.Range("L90:L115")) Is Nothing Then Exit Sub ' not a task type

  If Target.Cells.Count > 1 Then Exit Sub ' not a single cell entry

  If Target.Value = "Independent" Then

    Target.Offset(, -4).Value = InputBox("Please enter the activity start date (MM/DD/YY)

  ElseIf Target.Value = "Consecutive" Then

     Target.Offset(,-4).Formula = "=WorkDay_Intl( B3, J" & Target.Row-2 & ", 1, Referecences!$B$4:$B$16)"

   ElseIf Target.Value = "Simultaneous" Then

     Target.Offset(,-4).Formula =  "=H" & Target.Row-2

  End If

End Sub

I really recommend naming the ranges that you use in VBA code.

So, if you use Formulas > Name Manager to name L90:L115 as "TaskTypes" you can change the first line to

  If Intersect(Target, Me.Range("TaskTypes")) Is Nothing Then Exit Sub ' not a task type

Otherwise when you add or delete rows above 90 you will need to modify the code - and you won't always remember to do so; however the range name will automatically adjust. 

If you need to add rows to the task table, adding them before the last current row will cause the range name to adjust accordingly.

Answer 2

If I understand you correctly, if a user enters "Independent" in L70 then it will ask for a start date to enter in F70?

I am guessing that the same could apply on any even numbered row across a certain range of rows (e.g. from L60 to L101).  I would name that range L60:L101 as "TaskType" so that we can refer to it in the macro and not have to alter the macro if we add rows.

I would use a Worksheet_Change event procedure in the module of the worksheet (right-click sheet tab > view code)

Private Sub Worksheet_Change(Target As Excel.Range)

  If Intersect(Target, Me.Range("TaskType")) Is Nothing Then Exit Sub ' not a task type

  If Target.Cells.Count>1 Then Exit Sub ' not a single cell entry

  If Target.Value = "Independent" Then

    Target.Offset(,-6).Value = InputBox("Please enter the start date")

  End If

End Sub

Answer 3

Dear Mr. Bill Manville,

I am sorry for my extremely belated reply. I wanted to thank you, you support was really helpful and it solved my problem.

Thank you very much.

Kindest,

Answer 4

Dear Bill Manville,

Thank you very much for your reply, it was definitely helpful and it solved the problem I was dealing with.

Unfortunately now, the conditions that I had for the other two Task Types, simultaneous and consecutive, are not working any more.

I guess it is related to the fact that the condition was inputted directly into the cell? Should I also write this two conditions in Macros then? Like this:

Private Sub Worksheet_Change(ByVal Target As Range)

  If Intersect(Target, Me.Range("L90:L115")) Is Nothing Then Exit Sub ' not a task type

  If Target.Cells.Count > 1 Then Exit Sub ' not a single cell entry

  If Target.Value = "Independent" Then

    Target.Offset(, -4).Value = InputBox("Please enter the activity start date (MM/DD/YY)

  End If

End Sub

Private Sub Worksheet_Change1(ByVal Target As Range)

  If Intersect(Target, Me.Range("L90:L115")) Is Nothing Then Exit Sub ' not a task type

  If Target.Cells.Count > 1 Then Exit Sub ' not a single cell entry

  If Target.Value = "Consecutive" Then

    SettleDate = WorksheetFunction.WorkDay_Intl( B3, J90, 1, Application.WorksheetFunction("Referecences").Range("B4:B16"))

  End If

End Sub

Private Sub Worksheet_Change2(ByVal Target As Range)

  If Intersect(Target, Me.Range("L90:L115")) Is Nothing Then Exit Sub ' not a task type

  If Target.Cells.Count > 1 Then Exit Sub ' not a single cell entry

  If Target.Value = "Simultaneous" Then

    Range("L90:L115").Copy Range(H90, [H115])

  End If

End Sub

To be honest I have just started with VBA and it is higly likely that I have many mistakes, but I will continue working on it and maybe I will manage to fix it.

Thank you very much.

Kindest regards,