Runtime error 380 when trying to set RowSource property of ComboBox in VBA in Excel 2010 Pro x64

[Der Hexer]

Hello everyone,
I have a problem and I hope someone can help me with this. In the first place the problem sounds like one of the typical standard mistakes but I think in this case it is different.
I will try to explain, using an example what the problem is:

• I have two worksheets "Tests" and "Definitions". In the "Definitions"-Worksheet I created three columns, each containing different physical units.
• The names of these ranges are stored in the workbook space.
• Then I have a small table containing these three names: "Pressure, Temperature, Force"
• On the worksheet "Tests" I have a small table with two cells that use data validation.
• The first cells data validation is connected to the table containing the range names
• The second ones data validation is linked to the first cell by the INDIRECT macro.

Everything works fine in that worksheet. The user can pick the type of physical quantity (e.g. Pressure) in the first cell and the list of the second cell is than populated with pressure units.
Now I tried to use this table as base for my user form. So I placed two ComboBoxes, namely "ComboBox1" and "ComboBox2" on that form. I than initialized the ComboBoxes as follows:

Private Sub UserForm_Initialize()
  [SIZE=2]'if the Worksheet wasn't set yet, than do it now[/SIZE]
  If WS Is Nothing Then
    Set WS = Worksheets("Tests")
  End If

  'set the RowSource as well as the ControlSource properties here
  Me.ComboBox1.RowSource = WS.Cells(4, 3).Validation.Formula1
  Me.ComboBox1.ControlSource = "'" + WS.Name + "'!" + WS.Cells(4, 3).Address
  Me.ComboBox2.ControlSource = "'" + WS.Name + "'!" + WS.Cells(5, 3).Address
End Sub

The Variable WS is declared globally on top of the module via

Dim WS As Worksheet

ComboBox1 is filled with "Pressure, Temperature, Force".
When the user selects one of these entries than ComboBox2 should be populated with the appropriate list of units:

Private Sub ComboBox1_Change()
  If WS Is Nothing Then Exit Sub

  On Error Resume Next

  'Write the selected value of the ComboBox1 directly into the target cell
  'to ensure, that ComboBox2 displays the appropriate list for that value NOW
  WS.Cells(4, 3).Value = ComboBox1.Text

  'reset the Err.Number
  Err.Number = 0

  'Try to set the RowSource property. In that case the Formula of the validation
  'uses an Excel-macro called "INDIRECT"
  Me.ComboBox2.RowSource = WS.Cells(5, 3).Validation.Formula1 'Often (but not always) Runtime Error 380 - but why?

  'If an error was raised than display the message here:
  If Not (Err.Number = 0) Then
    Call MsgBox(Err.Description, vbOKOnly + vbCritical, "Run-time-Error " + CStr(Err.Number))
  End If
End Sub

Unfortunately ComboBox2 isn't filled with the list of units according to the selection of ComboBox1, but an Error is raised instead:
Run-time error '380': Could not set the RowSource property

The WS.Cells(5, 3).Validation.Formula1 is defined as =INDIRECT($C$4).

So can someone tell me what the reason for that error is, because I think that the validation formula is correct so far.

Thank You in advance.

System specifications:
MS Windows 7 Ultimate x64
MS Excel Professional Plus 2010 (Version 14.0.7106.5003, x64)

 

[Andrew Poulsom]

It's not a good idea to create controls at run time. Create all the controls and VBA code at design time and show only those that are needed.

 

[Der Hexer]

You could also use:

Me.ComboBox2.RowSource = ComboBox1.Value

Well that is in some way the same as the example above. Actually I don't know that these two cells are connected via such a validation construct.
But thank You for Your try.

 

[Der Hexer]

It's not a good idea to create controls at run time. Create all the controls and VBA code at design time and show only those that are needed.

Why is it not a good Idea to generate the controls at runtime?

 

[Andrew Poulsom]

So how do you know that Cells(5,3) is the cell that you want to use for the RowSource? You could do this:

Dim WS As Worksheet

Private Sub UserForm_Initialize()
    If WS Is Nothing Then
        Set WS = Worksheets("Tests")
    End If
    Me.ComboBox1.RowSource = WS.Cells(4, 3).Validation.Formula1
End Sub

Private Sub ComboBox1_Change()
    WS.Cells(4, 3).Value = ComboBox1.Value
    Me.ComboBox2.RowSource = Evaluate(Replace(WS.Cells(5, 3).Validation.Formula1, "INDIRECT", ""))
End Sub

Notice that I omitted the RowSource assignment as it's use is not best practice.

 

[Andrew Poulsom]

Why is it not a good Idea to generate the controls at runtime?

Because it can be problematical, as evidenced by numerous threads about it on this forum (including yours maybe).

 

[Der Hexer]

Shouldn't the "INDIRECT(...)" macro return the range-address by the range name? If so, why does it not return the range in VBA than???

 

[Der Hexer]

Because it can be problematical, as evidenced by numerous threads about it on this forum (including yours maybe).

Well the dynamically generated controls work fine in every aspect, so far. But because the problem is still the same when I hard code it (to test if it is a problem from the dynamically generated controls) I can assume that the dynamically generated controls are OK so far.

 

[Der Hexer]

So how do you know that Cells(5,3) is the cell that you want to use for the RowSource? (...)

Well, if the cell has data validation than I know I have to set the RowSource property to the control that is linked with that cell.

 

[Andrew Poulsom]

Shouldn't the "INDIRECT(...)" macro return the range-address by the range name? If so, why does it not return the range in VBA than???

Why would you expect VBA to evaluate a formula that uses INDIRECT (or any other worksheet function)?

 

[Der Hexer]

(...)

Dim WS As Worksheet

Private Sub UserForm_Initialize()
    If WS Is Nothing Then
        Set WS = Worksheets("Tests")
    End If
    Me.ComboBox1.RowSource = WS.Cells(4, 3).Validation.Formula1
End Sub

Private Sub ComboBox1_Change()
    WS.Cells(4, 3).Value = ComboBox1.Value
    Me.ComboBox2.RowSource = Evaluate(Replace(WS.Cells(5, 3).Validation.Formula1, "INDIRECT", ""))
End Sub

Notice that I omitted the RowSource assignment as it's use is not best practice.

You mean ControlSource. Aren't You?

If I interprete the last line of the Private Sub ComboBox1_Change() correctly, than it means that You simply remove the INDIRECT macro so that the range name remains. That's smart man . That should work. I'll try it now!