[SOLVED] Excel Userform checklist with date and userid

[confusebox]

Hi All,

I am new to VBA coding and Trying to create Excel form based checklist which can pull Date and and user id of system.

We have different different teams and they have particular task and due date for each task.

If i select any task and press complete then in front of that task User ID and Date should get populated. if task completion cross the due date it should ask reason for late.

Completion and reviewer should not same then msgbox "Processor and can not be same"

Same goes with 2 Review

Currently I am using this code to get userid and date in D6 cell but how i can link it to task which is selected in listbox for getting output in complete or review column?

Sub CommandButton15_Click()
Range("D6") = Environ("Username") & " " & Format(Date, "dd-mm-yyyy")
End Sub

Please let me know if it is possible.

Please reply me if you have any codes for this.

PFA Sheet for your reference.

Highly appreciate.

[Neil_]

added some code to get you on your way. Hope you find it helpful.

[confusebox]

Wow man....

Thank you so much...

I highly appreciate your help..

Have a great day

[confusebox]

added some code to get you on your way. Hope you find it helpful.

Hey Neil,
I made some changes in sheet and userform.

Is it possible if i add client code and press go button then only task for that client will show in listbox(Task list box)?

Also can we filter task by team name?

Thank you.

[Neil_]

Hi,
there are two ways of adding values to a list box. One way is to recursively add each value. The other is to set the Row Source to a range, which you have done. Unfortunately, the Row Source must be contiguous for a list box. If there are filtered rows within the range, then the list box will not populate. The following code will work as it stands, but only if the clients are contiguous. You can't have a gap, then 'Google' again a bit further down.
Also as the teams are incongruous, the Row Source would end up a multi-select range, so the list box would not populate.

Please Login or Register  to view this content.

You could change the list box to one that has values added, but there are consequences. The List Box can no longer have a header row. Also a representative array to match the list would need to be added to the code.

[Neil_]

By the way, the initial code I posted was for a single select style list box. If you change it to multi-select, only the first item selected will be updated by 'complete' and 'Review'. The code would need to be re-written for a multi-select style list box.

[Neil_]

Added code to enable completion / review using multi-select. changed method of populating list box to value list. Added filter for Client. Added filter for team.

[bakerman2]

@ Neil_

Actually there's a third and possibly best way to fill ListBox/Combobox being the List-method.

[Neil_]

@ Bakerman. Not familiar with it. Could you explain what you mean? I only know of using a range / named range in the Row Source or adding values recursively. You could also add an array directly to the list, but this isn't helpful in this instance as the listbox represents a range in a sheet.

[confusebox]

@ Bakerman. Not familiar with it. Could you explain what you mean? I only know of using a range / named range in the Row Source or adding values recursively. You could also add an array directly to the list, but this isn't helpful in this instance as the listbox represents a range in a sheet.

Hi Neil, Thank you.

If task is overdue then it ask late reason if i cancel also it get complete without reason.

Can we browse clients by client code as these are unique every time.

If i want to select 2 or more teams how can select.

One more help.

Please help me for other buttons code.

2nd Review NA = NA should get typed in that cell

Unverify = Complete verify 2nd review cancel all .(Clear all cell for that task.)

NA= NA should print in all cells for that task.

Save and close=Save changes and close form.

Thanks a lot..

[Neil_]

Hmmm. There's helping and helping.

Changed search for client code rather than client name.
Search adds client name to box.
Teams select-able.

2nd Review NA = NA should get typed in that cell

Unverify = Complete verify 2nd review cancel all .(Clear all cell for that task.)

NA= NA should print in all cells for that task.

Save and close=Save changes and close form.

code added.

Make sure you click on the Add Reputation star in the post. Mark the thread as SOLVED if you are happy with the code. You will have to start a new thread if you need help with other things in the form.

[confusebox]

Hmmm. There's helping and helping.

Changed search for client code rather than client name.
Search adds client name to box.
Teams select-able.


code added.

Make sure you click on the Add Reputation star in the post. Mark the thread as SOLVED if you are happy with the code. You will have to start a new thread if you need help with other things in the form.

I really Appriciate your help.

Have a great day