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
Interactive userform
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Interactive userform
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Interactive userform
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Interactive userform
Wowww
Fantastic again
Thanks a lot Hans
Fantastic again
Thanks a lot Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Interactive userform
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Interactive userform
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
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Interactive userform
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Interactive userform
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
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Interactive userform
That's amazing
Fantastic
Thanks a lot
Fantastic
Thanks a lot