listbox, select item based on cell/variable value vba

[smakatura]

I have a listbox that allows for multiple selections. However I want to select just one of theitems based on a variable value.


So I will set variable “Department” to the value in cellD28


Then use that variable to select just that one item from themultiselect listbox. Prior to thisselection, there will be no items selected in the listbox.

 Dim Department As String
 
 Department = Range("D28")
 With Sheets("Distribution").OLEObjects("ListBox1").Object
    For i = 0 To .ListCount - 1
        if .selected(i) =  Department
            .Selected(i) = True
        End If
        
    Next i
 End With

 

[Fluff]

How about

 Dim Department As String
 Dim i As Long
 Department = Range("D28")
 With Me.ListBox1
    For i = 0 To .ListCount - 1
        If .List(i) = Department Then
            .Selected(i) = True
        End If
    Next i
 End With

 

[smakatura]

Thanks, I got an error stating that the ME command had an issue... but I did get it working
Fix: made

if .selected(i) =  Department

into

If .List(i) = Department Then

at the very lease I forgot the "then"...not sure what I was thinking


the full code that worked was

 Dim i As Long
 Dim Department As String
 
 Department = Range("D28")
 With Sheets("Distribution").OLEObjects("ListBox1").Object
    For i = 0 To .ListCount - 1
        If .List(i) = Department Then
            .Selected(i) = True
        End If
        
    Next i
 End With

thank you for the help

 

[Fluff]

You're welcome & thanks for the feedback