Assign Default Selected Value to ListBox based on cell value

[kal10]

Hello. I have a user activated Userform with a ListBox in Excel 2016. The user clicks an activation button, which launches the form, the user chooses an entry from the listbox (its source list is a named ranged on another worksheet) and the macro assigns the chosen entry to a cell on the main worksheet. This is all working fine. However, I'd like the default value that's highlighted on the listbox to be the value of a particular cell (unless that cell contains the word "Plan", in which case the listbox default value should be the first value in the list). Below are the macros that launch the UserForm/ListBox and that assign the ListBox value to a cell, respectively. I've searched quite a bit for a similar solution, but can't seem to find anything that works. Thank you for any insight, it's greatly appreciated.

Sub NewPlan1()

With UserFormPlan1
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
.Show
End With
End Sub

Private Sub ListBoxPlan1_Click()

Sheets("Model").Range("A21").Value = ListBoxPlan1.Value
Unload Me
End Sub

 

[kpasa]

When selecting listbox indexes, I always use the following syntax

Listbox1.Selected(0) = True

A quick and dirty way would be a select case of the A21 value. Then do case select for each of the options you're comparing A21 to. You'd have to hard code it, but without an example file to tailor to your needs, that's all I can personally offer.

I'd be glad to look at the code for you to make a better suggestion.

 

[kal10]

Thanks for the help! When you say you could look at the code, what do you mean specifically? The code I included in my original post contained the only code sets I have (one for the launch and one to assign the value to the worksheet's destination cell. But let's say the source list for the listbox is:
Plan A
Plan B
Plan C
Plan D
Plan E
So the default value on the worksheet of A21 is "Plan". The user clicks on the activation button and the listbox comes up showing the list of Plan A through Plan B, with the default value being Plan A. The user then selects from the list box, and that selected value replaces "Plan" in cell A21. But let's say A21 has "Plan C" in it. In that case, when the user hits the activation button, I'd like the listbox to appear with "Plan C" being the default/highlighted option. Does that make sense?

 

[kpasa]

    Select Case Range("A21")
        Case "Plan A"
            Listbox1.Selected(0) = True
        Case "Plan B"
            Listbox1.Selected(1) = True
        Case "Plan C"
            Listbox1.Selected(2) = True
        Case "Plan D"
            Listbox1.Selected(3) = True
        Case "Plan E"
            Listbox1.Selected(4) = True
    End Select

 

[Norie]

The code to set the 'default' value for the listbox should not be in the Click event of the listbox, put the code in the form's Initialize code.

Private Sub UserForm_Initialize()
Dim arrPlans As Variant
Dim Res As Variant

    ListBoxPlan1.List = Sheets("Model").Range("A1:A10").Value
    
    arrPlans = ListBoxPlan1.List

    Res = Application.Match(Sheets("Model").Range("A21").Value, arrPlans, 0)

    If Not IsError(Res) Then
        ListBoxPlan1.Selected(Res - 1) = True
    End If

End Sub

 

[kal10]

kpasa: thank you for the response. I don't quite understand your solution (I think that's due to my lack of experience with VBA and that I don't know how the "case" function works). But I have several hundred plans (I just used A-E as an example).

Norie: I implemented your code (see below). But when I click on the userform activation macro, it launches the form, but no default value is highlighted at all. Further, when I try to choose a Plan from the list box, I get a run-time error '424': Object Required on the ListBoxPlan1.List = Sheets... line (though it does still deliver the selected Plan to Cell A21. Maybe this is because my listbox references a named range (called: PlanList) as its RowSource, rather than referencing the cell range on that Base Rates Factors tab?

Sub NewPlan1()
    
With UserFormPlan1
 .StartUpPosition = 0
 .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
 .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
 .Show
 End With
 
Dim arrPlans As Variant
Dim Res As Variant
    ListBoxPlan1.List = Sheets("Base Rates Factors").Range("A1:A3000").Value
    arrPlans = ListBoxPlan1.List
    Res = Application.Match(Sheets("Model").Range("A21").Value, arrPlans, 0)
    If Not IsError(Res) Then
        ListBoxPlan1.Selected(Res - 1) = True
    End If
 
End Sub

 

[Norie]

The code to set the default value of the listbox should be in the UserForm_Initialize event, not in the code that shows the form.

 

[kal10]

Thanks Norie. Sorry, it looks like I tried to enter your code into the wrong macro. However, I also tried entering it into the UserForm "view code" macro (below:I assume this is the Initialize Event macro?) but had no luck that way either. Am I goofing up your instructions? I only have a little experience with UserForms.

Private Sub ListBoxPlan1_Click()

Sheets("Model").Range("A21").Value = ListBoxPlan1.Value
Unload Me
Dim arrPlans As Variant
Dim Res As Variant
    ListBoxPlan1.List = Sheets("Base Rates Factors").Range("A1:A3000").Value
    arrPlans = ListBoxPlan1.List
    Res = Application.Match(Sheets("Model").Range("A21").Value, arrPlans, 0)
    If Not IsError(Res) Then
        ListBoxPlan1.Selected(Res - 1) = True
    End If
    
End Sub

 

[Norie]

All you should need to do is put the code I posted in post #5 into the userform module.

 

[kal10]

Thanks Norie. That's indeed what I did in #6 (including changing you range reference from A10 to "Sheets("Base Rates Factors").Range("A1:A3000").Value "). But I keep getting the "Runtime Error 424 Object Required at the "ListBoxPlan1.List = Sheets("Base Rates Factors").Range("A1:A3000").Value" part of you code. I'm sure it's my lack of experience with User Forms and List Boxes that is causing the issue.