albertc30
Well-known Member
- Joined
- May 7, 2012
- Messages
- 1,091
- Office Version
- 2019
- Platform
- Windows
Hi all.
I am trying to show combboxes on userform to display the data, say customers, in alphabetical order.
I have found the code bellow on MrExcel but it's not working.
I am getting run time error 13, type mismatch.
I also read that the above code would return the data in the combBox in alphabetical order without messing with the data in the sheet.
On another website, I have read that excel VBA cannot display a combBox in alphabetical order?
Is this the case?
Any help is, as always, truly appreciated.
Cheers
P.S; The list is shown by the code bellow.
I am trying to show combboxes on userform to display the data, say customers, in alphabetical order.
I have found the code bellow on MrExcel but it's not working.
I am getting run time error 13, type mismatch.
Code:
Private Sub UserForm_Initialize()
ufcbbname.SetFocus
allowed.Text = Worksheets("Saved Invoices").Range("I7")
allcustomerowed.Text = Worksheets("Saved Invoices").Range("K7")
'bellow to try and show customers combbox in alphabetical order
Dim unsorted As Boolean, i As Integer, temp As Variant
unsorted = True
With ufcbbname
Do
unsorted = False
For i = 0 To UBound(.List) - 1
If .List(i) > .List(i + 1) Then
temp = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = temp
unsorted = True
Exit For
End If
Next i
Loop While unsorted = True
End With
'above to try and show customers combbox in alphabetical order
End Sub
I also read that the above code would return the data in the combBox in alphabetical order without messing with the data in the sheet.
On another website, I have read that excel VBA cannot display a combBox in alphabetical order?
Is this the case?
Any help is, as always, truly appreciated.
Cheers
P.S; The list is shown by the code bellow.
Code:
Private Sub ufcbbname_Change()
'upcustdetails.Enabled = True
'when data changes on the field, all other related values are retrived.
Dim i As Long, lastrow As Long
lastrow = Sheets("Customers").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
If Sheets("Customers").Cells(i, "A").Value = (Me.ufcbbname) Or _
Sheets("Customers").Cells(i, "A").Value = Val(Me.ufcbbname) Then
Me.cutbncn = Sheets("Customers").Cells(i, "L").Value
Me.tbtotalspent = Sheets("Customers").Cells(i, "r").Value
Me.tboutinvoices = Sheets("Customers").Cells(i, "q").Value
Me.tvTotalSpent = Sheets("Customers").Cells(i, "w").Value
Me.tbCreatedBy = Sheets("Customers").Cells(i, "u").Value
Me.tblastupdate = Sheets("Customers").Cells(i, "s").Value
Me.tbcrebydate = Sheets("Customers").Cells(i, "v").Value
Me.tbupdaby = Sheets("Customers").Cells(i, "t").Value
'Me.allowed = Sheets("Saved_Invoices").Cells(i, "H7").Value
'Me.tbncem = Sheets("Customers").Cells(i, "H").Value
End If
'upcustdetails.SetFocus
ufcbbname.Text = UCase(ufcbbname.Text)
Next
End Sub
Last edited: