Excel VBA Listbox - Dynamic RowSource & ControlSource

PPriest

New Member
Joined
Jun 11, 2018
Messages
36
I am creating a logsheet with a UserForm. The UserForm has several list boxes that point to a sheet with various "drop-down lists" that we can modify over time as needed. Due to the nature of list boxes, I want to eliminate all of the blank spots in a range and only display cells in the range that contain data using a dynamic range. How do I make a dynamic range in a RowSource?

As a workaround, I was able to do this, but it only works when I have the "Drop Down List" sheet open. The user form will be opened from the "Down Time Log" sheet.
1614018397467.png


I have the dynamic ranges for each drop-down list formulated here on the "Drop Down Lists" sheet.
1614018468440.png


My code:
List Box Name: lbMachineNumber

Private Sub UserForm_Initialize()
lbMachineNumber.RowSource = Sheet8.Range("R4").Value
End Sub

In addition, I want my ControlSource to change the caption for a label on the UserForm. How do I set that?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

PPriest

New Member
Joined
Jun 11, 2018
Messages
36
I am creating a logsheet with a UserForm. The UserForm has several list boxes that point to a sheet with various "drop-down lists" that we can modify over time as needed. Due to the nature of list boxes, I want to eliminate all of the blank spots in a range and only display cells in the range that contain data using a dynamic range. How do I make a dynamic range in a RowSource?

As a workaround, I was able to do this, but it only works when I have the "Drop Down List" sheet open. The user form will be opened from the "Down Time Log" sheet.
View attachment 32775

I have the dynamic ranges for each drop-down list formulated here on the "Drop Down Lists" sheet.
View attachment 32776

My code:
List Box Name: lbMachineNumber

Private Sub UserForm_Initialize()
lbMachineNumber.RowSource = Sheet8.Range("R4").Value
End Sub

In addition, I want my ControlSource to change the caption for a label on the UserForm. How do I set that?
Scatch the last line - I figured that part out. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,088
Messages
5,628,603
Members
416,327
Latest member
Chimay

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
Top