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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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,127,333
Messages
5,624,066
Members
416,010
Latest member
NJT

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