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)

 

[Der Hexer]

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

This is an interesting question. Did not ask this myself. Will meditate about this...

 

[Andrew Poulsom]

You mean ControlSource. Aren't You?

Yes, sorry.

 

[Der Hexer]

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

AWESOME! It works! This solution is very close to what I need and I will adapt it into my code. Great job!

Thank You very much, that you took the time to solve my problem.