|
|
Thread Tools | Display Modes |
#1
|
||||
|
||||
Trying to have selected values from one column populate date selection was made in 3 other columns
Oh, great and powerful Excel gods, please take mercy on my ignorant self!
I rarely use Excel, but for some reason continue to be selected to create worksheets! Creating a basic 'Current Workload' (attached) tracker sheet with task specific metrics on length of time (in days/weeks) each takes to complete. Specifically: Column H, 'Status' whenever the (dropdown) value is changed, needs to: 1. Populate the change date in Column I, 'Status Date' - regardless of the selection. Got this one working with the first VBA Sub 2. Populate the date in Column J, 'Assigned Date' that 'Assigned (not started)' option is selected. Date must not change once entered. 3. Populate the date in Column K, 'Start Date' that 'In Progress' option is selected. Date must not change once entered. 4. Populate the date in Column L, 'Date Completed' that 'Completed' or 'CANCELLED' option is selected. Date must not change once entered. And finally, Column L, 'Date Completed' needs to reconcile with Column G, 'Estimated Completion' and populate Column M, 'Task Time' and show total days/weeks it took to complete the task. This is the VBA I used for Need #1: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("H2:H" & Rows.Count), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Intersect(Range("H2:H" & Rows.Count), Target).Offset(0, 1).Value = Date Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub And it works... I then tried adding this VBA to handle Need #2,3, and 4: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRng As Range Dim Rng As Range Dim DateCol As String Set WorkRng = Intersect(Columns("H"), Target) If Not WorkRng Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each Rng In WorkRng If Not VBA.IsEmpty(Rng.Value) Then Select Case UCase(Rng.Value) Case "Assigned Date" DateCol = "J" Case "In Progress" DateCol = "K" Case "Date Completed" DateCol = "L" End Select If Len(DateCol) > 0 Then With Cells(Rng.Row, DateCol) .Value = Now .NumberFormat = "dd/mm/yyyy" End With End If Else Intersect(Rng.EntireRow, Union(Columns("J"), Columns("K:L"))).ClearContents End If Next Application.EnableEvents = True End If End Sub And it does NOT work. I have tried (unsuccessfully) to adjust these methods to work together, but I am simply guessing... Everything is as I explained in the attached file, all my rules and VBA. Again, I don't know very much about any of this beyond the basics. So, I have no idea if what I am trying to do is even possible... Please help... |
#2
|
||||
|
||||
Please add code tags around your code.
Next time it would be better to post in the VBA subforum. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to populate multiple columns with a single dropdown selection | jthomas666 | Excel | 2 | 03-27-2020 06:30 AM |
append multiple time values to date in one column | sarahafeez | Excel | 1 | 09-25-2017 11:01 PM |
Matching the values in two columns A and B and to see if there are common values in both the columns | Asadmir27 | Excel | 8 | 12-29-2016 12:06 AM |
Excel / Mail Merge - Return Column Headers for All and Any Columns with Values | eduams | Mail Merge | 1 | 09-26-2016 07:02 PM |
Display multiple values as new values based on selection from template. | MvdB | Excel | 2 | 09-29-2015 08:51 PM |