Hi AlphaFrog, I've been searching around for a few days now, and your topic has been very helpful. I'm pretty much stumped right now though and was hoping you were still subscribed to this topic!
I have written up a summary of my spreadsheet and what I'm trying to do:
Workbook has 2 sheets. Workbook is shared amongst a team of people who all have it open.
Sheet1 has 2 relevant columns(E & F). Sheet1 is a list of projects that each need to have a set of specific tasks completed from Task 1 to Task 19. Each task needs to be sequentially completed on each project, and I need to be able to look into each account and see that they have been completed (We also need to keep a history of what user has completed each task and on what date – but I believe the Excel Track Changes history log can accomplish this?)
- Column E uses your VB drop down method from this forum post to display a list of the 19 required tasks when a user clicks on the corresponding cell on Column E
- Column F from cell F3 downward (Infinitely, as new projects are pasted in to the bottom of the sheet) is filled with the deadline date of each project.
Sheet2 has a bunch of lists used elsewhere in the spreadsheet
The spreadsheet is using conditional formatting on the deadline dates to highlight the date based on how close today is to the deadline listed. The spreadsheet then also colour codes the project row based on the status of the task entry in Column E. I’m currently just using a normal dropdown list, but the problem is there is no way to easily look at an account and see that a user has checked off the other prior tasks.
There are a few problems I’m running into, as I am completely new to VB:
- The only way I could get the “list entries” to show up on the list was to paste them way over in Column P of Sheet1
o It would be preferred to have the list on Sheet2, where there is a summary of other functional lists
- The current way the list works is everything that gets selected will concatenate in the cell, causing the colour coding for the project to fail (The conditional formatting is currently set up to look for a specific entry for example: =$E3="File Complete")
o Is there a way to make the dropdown list prioritize the list in a certain way, so when a higher level task has been clicked it will be displayed over any lower level, but still show that the other tasks <19 have been clicked (As in they are still highlighted/checked off when the user clicks on the E Cell)
- The main reason I wanted to use your method for the list instead of just a normal drop down, is because we need to be able to be able to audit the projects and see that people have been completing their tasks. Do you think there is an easier way to do this? I was thinking there might be a way to just use the normal drop down and try to figure out a way to update a log that would show a user changed a project’s task status from X to Y on Z date?
See below for the VB code I'm using:
UserForm1
Code:
Private Sub UserForm_Initialize()
With ListBox1
.List = Range("P3:P21").Value 'Range of cells with the list of tasks
.MultiSelect = fmMultiSelectMulti
End With
UserForm1.Caption = "Select Completed Tasks"
CommandButton1.Caption = "Okay"
End Sub
Private Sub CommandButton1_Click()
Dim i As Long, strTemp As String
With ListBox1
'Read selections
For i = 0 To .ListCount - 1
If .Selected(i) Then
strTemp = strTemp & .List(i) & MySeperator
.Selected(i) = False ' Clear selections
End If
Next i
End With
If Len(strTemp) Then
strTemp = Left(strTemp, Len(strTemp) - Len(MySeperator))
ActiveCell.Value = strTemp
Else
ActiveCell.ClearContents
End If
UserForm1.Hide
End Sub
Private Sub UserForm_Activate()
'Match Listbox selections with ActiveCell selections
Dim strCell As String, i As Long
strCell = ActiveCell.Value & MySeperator
With ListBox1
For i = 0 To .ListCount - 1
.Selected(i) = InStr(1, strCell, .List(i), 1)
Next i
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Hide userform if Close "X" clicked
If CloseMode = 0 Then
Cancel = True
Me.Hide
End If
End Sub
Sheet1
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
'Range of cells to have the pop-up userform
If Not Intersect(Range("E3:E1000"), Target) Is Nothing Then
UserForm1.Show
End If
End If
End Sub
