Vba Programming

[Dogga786]

in my combo box i have selected a range as a drop down to show but the range has multiple duplicate entries how to combine the same values to show as one in the combobox

right now range is like this

indirect costs
indirect costs
indirect costs
sitework
sitework
sitework
concrete
concrete
concrete

i want it show show only
indirect cost
sitework
concrete

i just selected the rowsource and referenced it to the name range "Groups" I created for the list ( i selected the whole column and named it groups)




another thing i want to ask you guys if there's also a way to do as following

Group Item Cost Code

indirect Cost 101-office
indirect Cost 102-telephone
indirect Cost 103- misc
indirect Cost 104-vehicle
sitework 205-planning
sitework 210-digging
sitework 220-forming
concrete 330 - concrete pour
Concrete 340 - Concreteslab
concrete 440 - Concrete Labour

in user form i have two combo box
one for group and
one for item costs code

i want the user form to act so that
if in group combo box i select indirect cost
then the drop down list only shows item cost codes under that group instead of all the item cost codes

example it should show only if indirect costs is selected in group combobox
101-office
102-Telephone
103-Misc

as this is an example actual list is too long to navigate through so it will be very helpful if you can help me address these issues

I made this PO app for work on my own without knowing any of the VBA
i would love to share it with the group as it works really good apart from these two issues

Also if anyone is expert at VBA Programming i would love you to see my file and maybe clean up the coding and in return i will get you a gift card worth $50

 

[mrhstn]

Hi

To populate your combobox with a unique list you can try this, keep in mind the range and name of the combobox will need to be edited.

If you are using a Userform, then in the initialise event try...

Option Explicit
Private Sub UserForm_Initialize()


Dim v, e
With Sheet1.Range("A2:A500")
    v = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In v
        If Not .exists(e) Then .Add e, Nothing
    Next
    If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
End With
End Sub

If it is just an activeX object then you can use the same code but something is going to need to trigger the population, in this instance I used the selection change event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PopulateCombobox
End Sub


Private Sub PopulateCombobox()
Dim v, e
With Sheet1.Range("A2:A500")
    v = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In v
        If Not .exists(e) Then .Add e, Nothing
    Next
    If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
End With
End Sub