Edit existing working code for 1 Textbox value population entry not multiple of the same value

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
6,159
Office Version
  1. 2024
Platform
  1. Windows
Currently the code populates the 3 Textboxes with values from my sheet.
All entries are loaded multiple times as opposed to the one time in each Textbox.
See attached images

If there are 27 values of 2 BUTTON KEY & 11 values of 3 BUTTON KEY i only need to see 1 of each not all of them
Can the existing code be edited so once a value is taken care of then there is no need to load another of the same value.
Thanks


Rich (BB code): 
Private Sub UserForm_Activate()
  Dim dic1 As Object, dic2 As Object, dic3 As Object
  Dim i As Long
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  Set dic3 = CreateObject("Scripting.Dictionary")
  
  a = Range("A1:G" & Range("D" & Rows.Count).End(3).Row).Value
  ListBox1.ColumnCount = 7
  
  For i = 2 To UBound(a, 1)
    dic1(Range("D" & i).Value) = Empty
    dic2(Range("F" & i).Value) = Empty
    dic3(Range("G" & i).Value) = Empty
  Next
  
  ComboBox1.List = dic1.Keys
  ComboBox2.List = dic2.Keys
  ComboBox3.List = dic3.Keys
  
  With Me.ListBox1
   .ColumnWidths = "190;100;140;140;100;150;150"
   .Width = 975
  End With

  Dim rngData As Range
  
  Set rngData = Worksheets("DATABASE").Range("D6:D1000")
      Me.ComboBox1.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")
        
  Set rngData = Worksheets("DATABASE").Range("F6:F1000")
      Me.ComboBox2.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")

  Set rngData = Worksheets("DATABASE").Range("G6:G1000")
      Me.ComboBox3.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")
      
  End Sub
 

Attachments

  • EaseUS_2025_04_ 3_08_55_37.jpg
    EaseUS_2025_04_ 3_08_55_37.jpg
    24.3 KB · Views: 6
  • EaseUS_2025_04_ 3_08_55_46.jpg
    EaseUS_2025_04_ 3_08_55_46.jpg
    19.7 KB · Views: 6
  • EaseUS_2025_04_ 3_08_56_07.jpg
    EaseUS_2025_04_ 3_08_56_07.jpg
    22.2 KB · Views: 6
Sort by date Sort by votes
1. Delete
Code: 
Dim rngData As Range
 
  Set rngData = Worksheets("DATABASE").Range("D6:D1000")
      Me.ComboBox1.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")
      
  Set rngData = Worksheets("DATABASE").Range("F6:F1000")
      Me.ComboBox2.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")

  Set rngData = Worksheets("DATABASE").Range("G6:G1000")
      Me.ComboBox3.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")

2. I guess that's how it's supposed to be. In any case, try to write clearly. Don't copy the bad habits of others.


Rich (BB code): 
a = Worksheets("DATABASE").Range("A1:G" & Range("D" & Rows.Count).End(3).Row).Value
 
Upvote 0
But then the Textboxes are not sorted A-Z like before
Look at image screenshot
 

Attachments

  • EaseUS_2025_04_ 3_11_28_50.jpg
    EaseUS_2025_04_ 3_11_28_50.jpg
    23.1 KB · Views: 2
Upvote 0
Try replacing this:
VBA Code: 
    Dim rngData As Range
  
    Set rngData = Worksheets("DATABASE").Range("D6:D1000")
        Me.ComboBox1.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")
        
    Set rngData = Worksheets("DATABASE").Range("F6:F1000")
        Me.ComboBox2.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")
  
    Set rngData = Worksheets("DATABASE").Range("G6:G1000")
        Me.ComboBox3.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")

With This:
VBA Code: 
    Dim rngData As Range
  
    With Worksheets("DATABASE")
        Set rngData = .Range("D6", .Cells(Rows.Count, "D").End(xlUp))
        Me.ComboBox1.List = Evaluate("UNIQUE(SORT(" & rngData.Address(, , , 1) & "))")
        
        Set rngData = .Range("F6", .Cells(Rows.Count, "F").End(xlUp))
        Me.ComboBox2.List = Evaluate("UNIQUE(SORT(" & rngData.Address(, , , 1) & "))")
      
        Set rngData = .Range("G6", .Cells(Rows.Count, "G").End(xlUp))
        Me.ComboBox3.List = Evaluate("UNIQUE(SORT(" & rngData.Address(, , , 1) & "))")
    End With
 
Upvote 0
Solution
ipbr21054 said:
But then the Textboxes are not sorted A-Z like before
Look at image screenshot
Click to expand...
Then what is it for?

Code: 
Dim dic1 As Object, dic2 As Object, dic3 As Object
  Dim i As Long
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  Set dic3 = CreateObject("Scripting.Dictionary")
  
  a = Range("A1:G" & Range("D" & Rows.Count).End(3).Row).Value

For i = 2 To UBound(a, 1)
    dic1(Range("D" & i).Value) = Empty
    dic2(Range("F" & i).Value) = Empty
    dic3(Range("G" & i).Value) = Empty
  Next
  
  ComboBox1.List = dic1.Keys
  ComboBox2.List = dic2.Keys
  ComboBox3.List = dic3.Keys
 
Upvote 0
Georgiboy said:
Try replacing this:
VBA Code: 
    Dim rngData As Range
 
    Set rngData = Worksheets("DATABASE").Range("D6:D1000")
        Me.ComboBox1.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")
       
    Set rngData = Worksheets("DATABASE").Range("F6:F1000")
        Me.ComboBox2.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")
 
    Set rngData = Worksheets("DATABASE").Range("G6:G1000")
        Me.ComboBox3.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")

With This:
VBA Code: 
    Dim rngData As Range
 
    With Worksheets("DATABASE")
        Set rngData = .Range("D6", .Cells(Rows.Count, "D").End(xlUp))
        Me.ComboBox1.List = Evaluate("UNIQUE(SORT(" & rngData.Address(, , , 1) & "))")
       
        Set rngData = .Range("F6", .Cells(Rows.Count, "F").End(xlUp))
        Me.ComboBox2.List = Evaluate("UNIQUE(SORT(" & rngData.Address(, , , 1) & "))")
     
        Set rngData = .Range("G6", .Cells(Rows.Count, "G").End(xlUp))
        Me.ComboBox3.List = Evaluate("UNIQUE(SORT(" & rngData.Address(, , , 1) & "))")
    End With
Click to expand...

This not only sorted my Combobox A-Z but it also populated it with 1 instance as opposed to all that was on the sheet
 
Upvote 0
hungtbatman1 said:
Then what is it for?
Click to expand...
Indeed, a lot of that code may not be needed. I am only guessing as I can't see the spreadsheet nor understand fully the aesthetics of the end goal but the whole code may only need to be the below now:
VBA Code: 
Private Sub UserForm_Activate()
    Dim rngData As Range
  
    With Worksheets("DATABASE")
        Set rngData = .Range("D6", .Cells(Rows.Count, "D").End(xlUp))
        Me.ComboBox1.List = Evaluate("UNIQUE(SORT(" & rngData.Address(, , , 1) & "))")
        
        Set rngData = .Range("F6", .Cells(Rows.Count, "F").End(xlUp))
        Me.ComboBox2.List = Evaluate("UNIQUE(SORT(" & rngData.Address(, , , 1) & "))")
      
        Set rngData = .Range("G6", .Cells(Rows.Count, "G").End(xlUp))
        Me.ComboBox3.List = Evaluate("UNIQUE(SORT(" & rngData.Address(, , , 1) & "))")
    End With
  End Sub
 
Upvote 0
Dont have time to check above at present as out but i will advise what it does for me.
Userform with 3 Comboboxes.

Populate Combobo1 with values from column D

Populate Combobo2 with values from column F

Populate Combobo3 with values from column G

If there are more than one instance ONLY populate the combobox with 1 of them.

The combobox values need to be sorted A-Z

I know some has been covered in this post but as im out i thought i would just say whats needed.

Thanks
 
Upvote 0
Without that code & trying the shorter one above it then gives issues to the filter side of things

Rich (BB code): 
Sub FilterData()
  Dim cmb1 As Variant, cmb2 As Variant, cmb3 As String
  Dim i As Long, j As Long, k As Long, n As Long
  Dim b As Variant
  
  ListBox1.Clear
  n = WorksheetFunction.CountIfs(Range("D:D"), ComboBox1.Value & "*", _
        Range("F:F"), ComboBox2.Value & "*", Range("G:G"), ComboBox3.Value & "*")
  If n = 0 Then
    MsgBox "THERE ARE NO RECORDS TO DISPLAY", vbCritical, "NO DISPLAY RECORDS MESSAGE"
    Exit Sub
  End If
  ReDim b(1 To n, 1 To 7)
  
  For i = 1 To UBound(a, 1)
    If ComboBox1.Value = "" Then cmb1 = a(i, 4) Else cmb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cmb2 = a(i, 6) Else cmb2 = ComboBox2.Value
    If ComboBox3.Value = "" Then cmb3 = a(i, 7) Else cmb3 = ComboBox3.Value
        
    If a(i, 4) = cmb1 And a(i, 6) = cmb2 And a(i, 7) = cmb3 Then
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)
      Next
    End If
  Next
  ListBox1.List = b
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Make selection in Listbox & be taken to that customer on worksheet
2
Replies
12
Views
308
ipbr21054
I
K
  • Question Question
two request on userform delete empty in combobox and implementation sheets
2 3
Replies
24
Views
624
KalilMe
K
D
  • Question Question
Need to include Sum total of certain columns in the report
Replies
2
Views
209
dsubash
D
I
  • Solved
Search worksheet for with a changeable column search
Replies
4
Views
168
mumps
mumps
I
  • Solved
Edit existing working code if certain condition is met
2
Replies
10
Views
185
ipbr21054
I

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top