Making Excel behave like Project

[NewOrderFac33]

Good afternoon,

I am trying to build a worksheet that mimics Microsoft Project - I have all the plotting based on entries in various cells sorted out, but need to be able to create a dependency. So, I want to be able to click on the Start Date cell for the current task then, hold down [Control] (to create a non-contiguous selection), click on the Start Date cell of the current task and somehow trap the two values to make the End Date cell = the value in the Start Date cell +1 day (or 3 if the End Date is a Friday, to move it on to Monday)
I have figured out how to do this assuming that I only want to create a Start date based on an End Date in the previous row, but I need to be able to do it to, say, create a start date in Row 10, based on an End Date in Row 5.

So, my code would allow me to click (say) B10 for my Current Task Start Date, hold down [Control], click D5 for my previous Task End Date, then do something to populate B10 with the value contained in D5, plus the appropriate number of additional days (1 if D5 is Monday-Thursday or 3 if D5 is Friday) - I know how to do this using the WeekDay function.

Can anyone help me figure out how to trap the values in the two selected cells to two different variables?

Thanks in advance!

 

[Jerry Sullivan]

Hi Pete,

You could try using Worksheet_SelectionChange event code.

The code below watches for the condition of having exactly two cells selected with both cells being in Column D or E.
(At some point you are likely to want to extend your Project mimic app to allow Start-Finish, Finish-Start and Finish-Finish relationships).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("D:E")) Is Nothing Then Exit Sub
    If Target.Cells.Count = 2 And Intersect(Target, _
        Range("D:E")).Cells.Count = 2 Then
            MsgBox "Dependency: " & Target.Address
    End If
End Sub

 

[NewOrderFac33]

Thanks for this, it's a good start! If I can just trap the values in the two cells to two variables, that's the next bit... or just persuade someone to buy Project!

 

[NewOrderFac33]

Combining your suggestion with some .item help from Chip Pearson's pages, I came up with:

Sub AA_Test()
If Intersect(Selection, Range("D:E")) Is Nothing Then Exit Sub
If Selection.Cells.Count = 2 And Intersect(Selection, Range ("D:E")).Cells.Count = 2 Then
MsgBox "Dependency: " & "First Item is: " & Selection.Item(1).Address & ", contents = " & Selection.Item(1).Value & Chr(10) & _
"Second Item is: " & Selection.Item(2).Address & ", contents = " & Selection.Item(2).Value
End If
End Sub

The ides is to select two cells in Columns D and E and display both their addresses and their contents.

If I select, say, D6:E6, it works perfectly.

If, however, I select D6, then hold down [Control], then E6, (which is how I want the final thing to work, based on non-contiguous cells the message displays both addresses correctly, but only the contants of D6.

Can anyone suggest why selecting the cells by dragging with the mouse allows me to trap the contents of both, but not when the cells are non-contiguous?

Thanks

Pete

 

[Jerry Sullivan]

Pete,
You can parse the Target.address using its comma separator to get the addresses of each selected cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("D:E")) Is Nothing Then Exit Sub
    If Target.Cells.Count = 2 And Intersect(Target, _
        Range("D:E")).Cells.Count = 2 Then
            Dim sAddress1 As String, sAddress2 As String
            sAddress1 = Split(Target.Address, ",")(0)
            sAddress2 = Split(Target.Address, ",")(1)

            MsgBox "Dependency: " & sAddress1 _
             & " to " & sAddress2
    End If
End Sub

If, however, I select D6, then hold down [Control], then E6, (which is how I want the final thing to work, based on non-contiguous cells the message displays both addresses correctly, but only the contants of D6.

.Item returns cells relative to the upper-left cell of the range selected (similar to the way Offset allows you to return a cell X rows by Y rows from a range). That's why your code worked when the two second cell was directly below the first.

 

[chenyu768]

Pete, did you ever get it to work? also would you mind sharing what it ended up looking like? i really would like to not spend the money on microsoft project if i dont have to