Assign Default Selected Value to ListBox based on cell value

kal10

New Member
Joined
Mar 24, 2015
Messages
17
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
 
Is the listbox definitely called ListBoxPlan1?
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Yep. I double checked to be sure. In the VBA Properties window, the (name) for the User Form is UserFormPlan1 and the name for the ListBox is ListBoxPlan1. Could the problem be that your code ListBoxPlan1.List = Sheets("Base Rates Factors").Range("A1:A3000").Value references a sheet/range (which I edited to my workbook's range) while the ListBox's source is a named range (RowSource PlanList which is range 'Base Rates Factors'!$A$7:$A$613 )?
 
Upvote 0
Shouldn't matter how the listbox is populated.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top