Home » Forums » Open Source Learning Center » Question and Answer

Complex userform with user-populated listbox

Submitted by olivierpbeland on 20 December, 2012 - 12:55

Hi, I want to do a userform allowing users to create new "projects", each with a number of "milestones" and targeted completion dates for each milestones.

Right now, my userform allows users to (1) give the name of the new project (say "temp") and (2) give the number of milestones (say "x"). These are inputed in a data table with "temp" repeated "x" times (i.e. over "x" rows).

The columns "milestones names" and "milestones targeted completion dates" are left empty and must be filled manually by the user. The form therefore only creates placeholders based on the number of milestones but does not allow user to specify each milestone property.

Attached is a WB with the current userform and a sketch of what I would like to achieve.

Basically, I would like to have users input (a) Milestones name, (b) milestone date, (c) click an "add milestone" button. Each added milestone with date would be added to a listbox below. Users could then select an item to delete it or edit it.

Any help greatly appreciated.

AttachmentSize
Dashboard (dec 20, v1 Shared).xlsm28.94 KB
‹ ADD AND SEARCH FILES WITH LINK Automate emailing items on the spreadsheet ›
»

Hi, You can continue the

Submitted by Argyronet on 21 December, 2012 - 14:00.

Hi,

You can continue the existing code by adding what I wrote for you :

'**********************************************************************
' Module : frmNewProject
' Type : Feuille
' DateTime : 21/12/2012
' Author : Argyronet
' Purpose : Module used to
'
'**********************************************************************
Option Explicit

Private Sub cmdAddNew_Click()
ClearForm
'To be completed
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
ClearForm
End Sub

Private Sub cmdDelete_Click()
'To be completed
End Sub

Private Sub cmdEdit_Click()
'To be completed
End Sub

Private Sub lboMilestones_Click()
Me.txtProjectName = lboMilestones.Value
Me.cboAnalyst.Value = lboMilestones.List(lboMilestones.ListIndex, 1)
Me.txtMilestoneName.Value = lboMilestones.List(lboMilestones.ListIndex, 2)
Me.cboMDate.Value = Format(lboMilestones.List(lboMilestones.ListIndex, 3), "d-mmm-yyyy")
Me.txtNBmilestone = CountMilestone(lboMilestones.Value)
End Sub

Private Sub UserForm_Initialize()
FillListbox "Dashboard", "PF"
End Sub

Sub FillListbox(ByVal TargetSheetName As String, ByVal TableName As String)
Dim oRngData As Range
Dim strRngData As String

'Define the range you want to use
Set oRngData = Worksheets(TargetSheetName).ListObjects(TableName).Range
strRngData = Range(Cells(oRngData.Row + 1, oRngData.Column), Cells(oRngData.Rows.Count + oRngData.Row - 1, oRngData.Columns.Count + 1)).Address
With Me.lboMilestones
.ColumnHeads = True
.RowSource = strRngData
End With
End Sub

Private Function CountMilestone(ByVal ProjectName As String) As Integer
Dim I As Integer
Dim intCount As Integer

For I = 0 To lboMilestones.ListCount - 1
If lboMilestones.List(I, 0) = ProjectName Then
intCount = intCount + 1
End If
Next
CountMilestone = intCount
End Function

Private Sub ClearForm()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

See that I renamed some controls.
The subroutine to be completed are not ready because, I don't know what you want to do...
For example, if you delete a project, do you want that all projects with the same name are deleted too.
What ID Key you want to use for deletion ? The project, the name or the selected row in the listbox ?

It's you turn for your feedback.

»

Clarifications

Submitted by olivierpbeland on 21 December, 2012 - 14:44.

Thank you so much for taking the time to work on this.

I'm afraid I'm not sure how to integrate the code you gave with what I currently have in the WB.

Not sure if I can ask this but I would really appreciate if you could insert you code within my existing code under the form "frmNewProject" and link it with actual boxes within the form.

The form currently only allows to input new project each with several milestones. My next step will be to allow users to search for existing project (for instance by filling the "name of project" box by cliking on an existing project in the table). All milestones associated with it would then be listed, allowing users to modify their name or date, delete some, add new ones.

Ideally, by clicking on a row in the "list of milestones and targeted date" box, the info for that milestone would pop up in the "name of milestone" and "targeted completion date" boxes. Users could then modify the info or delete the selected milestone. I hadn't thought of this but it would be very useful if users could select multiple rows (milestones) in the "list of milestones and targeted date" box to delete multiple milestones at once. Maybe a "delete entire project" button would also make sense.

To answer your specific question, we would ideally have two ID key. Either delete an entire project (and all its milestones) or delete individual milestones for a given project.

Does that make sense? Let me know if you want me to provide you with an updated (un-coded) layout for the userform I have in mind.

Many thanks.

»

Ok, I will going to do a

Submitted by Argyronet on 21 December, 2012 - 15:48.

Ok, I will going to do a sample for you.

»

Amazing. Thank you so much.

Submitted by olivierpbeland on 21 December, 2012 - 15:52.

Amazing. Thank you so much. Will you update the file attached to the post?

»

Hi, My answer is into this

Submitted by Argyronet on 23 December, 2012 - 23:15.

Hi,

My answer is into this post :
http://www.excelexperts.com/complex-userform-user-populated-listbox-anss...

Regards.

»