CODEWITHSUNDEEP
vbaexcelcombobox
Simon Colebrook
Simon Colebrook

Reputation: 23

Populating ComboBox with dynamic values from another worksheet

---Update---

Thanks for the responses, I have found that DragonSamu's updated answer works perfectly.

---Original Post---

I have been trying to figure out where I am going wrong for the past few hours but I can't spot it. I think it's because the script is trying to draw the value from the active worksheet which is not what I want. Hopefully somebody can put me on the rite track - I think the answer should be relatively obvious but I just can't see it!

Basically, I am trying to populate a Combobox with a dynamic range of values that exist in another worksheet (but in the same workbook). I can get the Combobox to populate when I run the script in the worksheet 'Materials' (which is where the dynamic list is drawn from) but not when I run it in the worksheet 'Products'.

Unfortunately the script is designed to populate Products with Materials so is be run in a UserForm when the 'Products' worksheet is open and the 'Materials' worksheet would therefore be inactive.

I should also note that this script has been adapted from code I found elsewhere on this forum, so if it seems familiar I thank you in advance :)

Private Sub UserForm_Initialize()

Dim rRange As Range

On Error GoTo ErrorHandle

'We set our range = the cell B7 in Materials
Set rRange = Worksheets("Materials").Range("B7")

'Check if the cell is empty
If Len(rRange.Formula) = 0 Then
   MsgBox "The list is empty"
   GoTo BeforeExit
End If

'Finds the next empty row and expands rRange
If Len(rRange.Offset(1, 0).Formula) > 0 Then
   Set rRange = Range(rRange, rRange.End(xlDown))
End If

'The range's address is our rowsource
Mat1_Name_ComBox.RowSource = rRange.Address
Mat2_Name_ComBox.RowSource = rRange.Address
Mat3_Name_ComBox.RowSource = rRange.Address
Mat4_Name_ComBox.RowSource = rRange.Address
Mat5_Name_ComBox.RowSource = rRange.Address

BeforeExit:
Set rRange = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit

End Sub

Any help is much appreciated.

Cheers,

Simon

Upvotes: 2

Views: 1887

Answers (1)

DragonSamu
DragonSamu

Reputation: 1163

From what I can see your code would be giving an error here:

If Len(rRange.Offset(1, 0).Formula) > 0 Then
    Set rRange = Range(rRange, rRange.End(xlDown))
End If

Because your trying to set rRange by using Range() without defining the Worksheet first. This will get the Range from the ActiveWorksheet.

change it to the following:

If Len(rRange.Offset(1, 0).Formula) > 0 Then
    Set rRange = Worksheets("Materials").Range(rRange, rRange.End(xlDown))
End If

best practice would be the following:

Private Sub UserForm_Initialize()

Dim wb as Workbook
Dim sh as Worksheet
Dim rRange As Range

On Error GoTo ErrorHandle

'Set the Workbook and Worksheet
set wb = Workbooks("products.xlsx")
set sh = wb.Worksheets("Materials")

'We set our range = the cell B7 in Materials
Set rRange = sh.Range("B7")

'Check if the cell is empty
If Len(rRange.Formula) = 0 Then
   MsgBox "The list is empty"
   GoTo BeforeExit
End If

'Finds the next empty row and expands rRange
If Len(rRange.Offset(1, 0).Formula) > 0 Then
   Set rRange = sh.Range(rRange, rRange.End(xlDown))
End If

By properly defining and setting your Workbook and Worksheet you correctly reference to them and don't get errors.

Update:

the 2nd problem is that rRange.Address only places the Range location inside your .RowSource not the Sheet it needs to look at.

change:

Mat1_Name_ComBox.RowSource = rRange.Address

to: 

dim strSheet as String
strSheet = "Materials"
Mat1_Name_ComBox.RowSource = strSheet + "!" + rRange.Address

This way it will include the Sheet name into the .RowSource

Upvotes: 1

Related Questions