Excel VBA Listbox - Dynamic RowSource & ControlSource

[PPriest]

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.

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

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?

 

[PPriest]

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!