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)

 

[Norie]

What is the validation formula in C5?

Can't it evaluate to an error or invalid range at any time?

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this work for you?

Me.ComboBox2.RowSource = WS.Cells(4, 3).Value

 

[Der Hexer]

I think You mean C4? The validation of C4 is =Definitions!$E$1:$E$3
The validation of C5 is =INDIRECT($C$4)

 

[Andrew Poulsom]

Did you try what I suggested?

 

[Der Hexer]

Hello Andrew,

I tried that and the error code is still the same. I think this is clear, because that line means that it sets the selected value of the Cell C5 as the RowSource property.

EDIT:

My mistake... It works (used wrong index) but this is not what I want to implement. I want it to be as flexible as possible. And because I don't know if these two cells are connected to each other or not I can not set it that way, unfortunately.

 

[Andrew Poulsom]

Well isn't WS.Cells(4, 3).Value the same as =INDIRECT($C$4)?

 

[Norie]

No I meant C5 which is the cell you are referencing here when trying to set the RowSource of ComboBox2.

Me.ComboBox2.RowSource = WS.Cells(5, 3).Validation.Formula1 'Often (but not always) Runtime Error 380 - but why?

 

[Andrew Poulsom]

You could also use:

Me.ComboBox2.RowSource = ComboBox1.Value

 

[Der Hexer]

Thank you all for your really fast answers.

Perhaps I should explain a bit further what I intended to implement, because the example is just a simplification.

Actually I wrote some complex code that populates the user form with user controls at run time. So all I have is the start position and the type of a table of an arbitrary worksheet. I read the dimensions of that table (Rows and Columns). Than I read the types of the cells I to generate the controls on the user form. Than the controls are linked to the cells of that table using the ControlSource property. When the cell contains data validation a ComboBox is generated and linked with that cell. So at that moment I have no clue that one cell is connected to the other as in the example above. Interestingly the formula of that data validation was interpreted correctly last day. But the other day, when I was about to extend my code it didn't work any more. I tried a lot of things to solve that problem with no luck.