Interactive userform

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Interactive userform

Post by jimpatel1993 »

Thanks for looking at my post.
Any help will be appreciated.

I have userform. When user select either one name or multiple names along with condtions i would like to get results in Text box 2 and text box 3 please.
That is sheet 2 have some conditions
Example if user select following :
Anderson(4)
So key number is 4 and therefore textbox 2 will be 4
Combobox selected is Condition 2
So in sheet 2, number 4 and for condition 2 the time is 40 so the result number will be 40 in texbox 3

2nd example

Silk(1), Amsit(1), James(2) and Loku(4)
so key numbers are 1,2 and 4, therefore textbox 2 will be 1,2,4
Combobox selected is condtion 1
In sheet2, number 1,2 and 4 for condition 1 is 80,80 and 60. So result will be 80+80+60 divided by 3 which is 73 in texbox 3

3rd example
Silk(1),Amsit(1),Balurat(2), James(2) and Loku(4)
So key numbers are 1,2 and 4, therefore textbox 2 will be 1,2,4
Combox selected is condtion 2
In sheet2, number 1,2 and 4 for condition 2 is 60,60 and 40. So result will be 60+60+40 divided by 3 which is 53 in texbox 3

Thanks a lot for your time and support
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Interactive userform

Post by HansV »

See the attached version.

2nd modelling.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Interactive userform

Post by jimpatel1993 »

Wowww
Fantastic again
Thanks a lot Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Interactive userform

Post by jimpatel1993 »

Hans
Code worker perfect but I have one question please.
My actual listbox does not have name in format like Andy(1) but something like 7282728-xxx xxx xxx,xx(hd1) or 77282828-xxxxx...,xxxxxx(hf2) or multiple different criteria.
The common thing is () bracket.
Is there any option to just pick value or alphabet inside bracket alone please?
Sorry for this confusion

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Interactive userform

Post by HansV »

It's always better to post an example that is representative of the real data. That avoids having to modify the code because the real requirements are different. Here is a new version of the Changed procedure:

Code: Select all

Private Sub Changed()
    Dim dct As Object
    Dim i As Long
    Dim itm As String
    Dim n As String
    Dim c As String
    Dim v As Variant
    Dim sum As Double
    Dim p1 As Long
    Dim p2 As Long
    Set dct = CreateObject(Class:="Scripting.Dictionary")
    c = Me.ComboBox2
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            itm = Me.ListBox1.List(i)
            p1 = InStrRev(itm, "(")
            p2 = InStr(p1 + 1, itm, ")")
            n = Mid(itm, p1 + 1, p2 - p1 - 1)
            dct(n) = 1
        End If
    Next i
    Me.TextBox2 = Join(dct.keys, ",")
    For Each v In dct.keys
        sum = sum + Evaluate("SUMIFS(Table1[Time],Table1[Names number]," & v & ",Table1[Conditions],""" & c & """)")
    Next v
    If dct.Count = 0 Then
        Me.TextBox3 = ""
    Else
        Me.TextBox3 = sum / dct.Count
    End If
End Sub
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Interactive userform

Post by jimpatel1993 »

Apologies for not providing correct information. As some of them are sensitive thats why.
Above code works perfectly fine apart from textbox3. I am getting 0 in textbox3 for some reason. If I change Names number format I am not getting any value in textbox3.
That is if I put actual Names number to K123, L933,etc instead of 1,2,3 and4 I am getting nothing in textbox3.
Any idea please and sorry for this question

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Interactive userform

Post by HansV »

My apologies, my mistake. I didn't treat the value between parentheses as a string.

Code: Select all

Private Sub Changed()
    Dim dct As Object
    Dim i As Long
    Dim itm As String
    Dim n As String
    Dim c As String
    Dim v As Variant
    Dim sum As Double
    Dim p1 As Long
    Dim p2 As Long
    Set dct = CreateObject(Class:="Scripting.Dictionary")
    c = Me.ComboBox2
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            itm = Me.ListBox1.List(i)
            p1 = InStrRev(itm, "(")
            p2 = InStr(p1 + 1, itm, ")")
            n = Mid(itm, p1 + 1, p2 - p1 - 1)
            dct(n) = 1
        End If
    Next i
    Me.TextBox2 = Join(dct.keys, ",")
    For Each v In dct.keys
        sum = sum + Evaluate("SUMIFS(Table1[Time],Table1[Names number],""" & v & """,Table1[Conditions],""" & c & """)")
    Next v
    If dct.Count = 0 Then
        Me.TextBox3 = ""
    Else
        Me.TextBox3 = sum / dct.Count
    End If
End Sub
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Interactive userform

Post by jimpatel1993 »

That's amazing
Fantastic
Thanks a lot