List Box similar Values in Cell

ArhamRajput · Jun 10th 2020

Dear Experts,

I am looking for that if i select 1 value from list box then it must show in Cell "D6" as well. which is highlighted and list box should be sorted as A-Z and empty cell should not visible in list box .

Please help in this regards.

**royUK** · Jun 11th 2020

It's not a ListBox it's a ComboBox.

What empty cell?

ArhamRajput · Jun 11th 2020

Thanks for reply Roy but i made it.

ArhamRajput · Jun 11th 2020

Is there any option to ignore blank cells in Xactive Combobox.

There are 50 Cells which i have been selected and 15 of them are empty and now combobox is also showing blank cells.

How to ignore blank cells.

**royUK** · Jun 12th 2020

It's expected on Forums that you share your solution to help others

ArhamRajput · Jun 12th 2020

Here solution is attached which i was looking for earlier.

I need to know that please how to ignore blank cell in combo box without decreasing the range.

sobi_256 · Jun 12th 2020

pls check this, if u add anything to column D save the workbook, then open it , then u will be able to see all additions in dropdown

ArhamRajput · Jun 15th 2020

sobi_256

Hi, its not working for me even i have changed the data range and sheets name please review it once I have Combox in Sheet names as "Payslip" and Data in sheet names as "Employee Data" which range is "D4:D2000"

After run the code it goes to debug

Code

.AddItem myarr(i)

Above is debug which was yellow highlited.

Code

Sub arrdta()
Dim myarr As Variant
Dim i As Integer
lrow = Cells(Rows.Count, "B").End(xlUp).Row 'counting rows of column which will be linked to dropdown
myarr = WorksheetFunction.Transpose(Worksheets("Employee Data").Range("B4:B" & lrow).Value)
myarr = srtAZ(myarr)
With Worksheets("PaySlip").OLEObjects("ComboBox1").Object
     For i = LBound(myarr) To UBound(myarr)
     If myarr(i) <> "" Then
     .AddItem myarr(i)
        End If
     Next i
End With
End Sub

Function srtAZ(myarr As Variant)

Dim i As Long
Dim j As Long
Dim stor

For i = LBound(myarr) To UBound(myarr) - 1
    For j = i + 1 To UBound(myarr)
        If UCase(myarr(i)) > UCase(myarr(j)) Then
            stor = myarr(j)
            myarr(j) = myarr(i)
            myarr(i) = stor
        End If
    Next j
Next i

srtAZ = myarr

End Function

Display More

sobi_256 · Jun 15th 2020

sorry bro can't help u, in the example file it works, maybe some one else can help u, its always better to attach a similar file. Best of luck

**royUK** · Jun 15th 2020

What are you using the selected item in the ComboBox for?

Why has the list got any empty cells?

Attach an example workbook that shows how you want to use it, i.e. containing the twosheets.

ArhamRajput · Jun 15th 2020

Please edit this.

sobi_256 · Jun 17th 2020

please see attached, when u add a name in worksheet, data (B column) , then u need to save it and close the workbook, when u will open it again, then the dropdown box in sheet payslip will have all the names, my code runs whenever the workbook is being opened.
pretty sure royuk will have a better method to sort this out.

sobi_256 · Jun 17th 2020

this one is different, when u add name on worsksheet data, then go to pay sheet and click that button, it will update the combo box

ArhamRajput · Jun 18th 2020

Thank you for help.

List Box similar Values in Cell

Participate now!

Share