Selected Answer
Hello Tubrak,
In the attached workbook I had added the following code in the renamed module TBX_5675.
Option Explicit
Sub ShowUserForm()
' 307
Dim FrmNoName As New fmNoName
Dim DataRng As Range ' data range of "Inventory" tab
Dim CbxNames() As String ' list of Cbx controls
Dim i As Long ' loop counter
With Worksheets("Inventory")
' there must be 1 column for each Cbx in CbxNames
Set DataRng = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "D").End(xlUp))
End With
CbxNames = Split("CbxCom CbxTT CbxPR")
With FrmNoName
' the form's initialize event is triggered here
' all controls are now available here
For i = 0 To UBound(CbxNames)
With .Controls(CbxNames(i))
.List = mySort(DataRng.Columns(i + 1))
.ListIndex = 0 ' this will trigger the control's Change event
End With
Next i
.Show
' when the OK button on the form is clicked
' code resumes here with the form hidden but all
' controlls still accessible.
Dim Txt As String
For i = 0 To UBound(CbxNames)
CbxNames(i) = CbxNames(i) & vbTab & .Controls(CbxNames(i)).Value
Next i
MsgBox Join(CbxNames, vbCr), vbInformation, "Result"
End With
Unload FrmNoName
Set FrmNoName = Nothing
End Sub
Function mySort(Arr As Variant) As Variant
' 307
Dim R As Long ' loop counter: Arr() row
Dim f As Long ' loop counter: Temp
Dim Tmp As String
Arr = Application.Transpose(Arr)
For R = LBound(Arr) To UBound(Arr) - 1
For f = R + 1 To UBound(Arr)
If Arr(R) > Arr(f) Then
Tmp = Arr(R)
Arr(R) = Arr(f)
Arr(f) = Tmp
End If
Next
Next
mySort = Arr
End Function
I have also renamed your user form and three of its combo boxes and added a command button which I named CmdOK. It's important that you don't unload the form in its own code because if you hide it control is resumed by the calling procedure and you can extract data from the form before destroying the form there.
Two things. One, I really don't understand what you want to load to the other 9 combo boxes. However, I have shown the way to do it and you should have no further problem replicating the same method.
Two, I'm reluctant to help you because I spent half my time doing work you could have done equally well if not better. Comprehensive and meaningful naming of procedures and variables is very important. It saves everybody's time, including and especially your own. Option Explicit at the top of each module isn't a luxury but a little extra which will save you hours and hours of work. Proper formatting of the code makes it more readable and therefore easier - faster - to understand and modify. Formatting includes proper indenting and avoiding the merge of several code lines into one with commas (in the case of Dim statements) and colons. It may be my own inability but it is one that is shared by all who do programming regularly: I can't work with code that looks like this:-
Sub J3v16()
Dim Data, Chk, temp, Dict As Object, ws As Worksheet, Str As String, i As Long, x As Long, xx As Long: xx = 3
If you want my help in the future please clear up the mess before you post your code on this forum. I have cleaned out the mySort function for you. You can use it of an example of the work you should do. You may not be able to do it perfectly but you can do it in a way that displays your effort rather than the lack of it.