Dynamic Range Rantime error

In the attached spreadheet, I have a dynamic range defined which populates a dropdown

https://dl.dropboxusercontent.com/u/...Dashboard.xlsm

Dynamic Range Is

=OFFSET(Projects!$C$1,1,0,COUNTIF(Projects!$C:$C,">""")-1,2)

This populates a drop down in the Dashboard TAB under label Project ID & Name. In the code linked to this dropdown I have the following

Sub ComboBox1_Change()
ComboBox1.ListFillRange = "DynamicList"
With ThisWorkbook.Worksheets("Projects")
ProjDesc.Value = Application.VLookup(ComboBox1.Value, .Range("C2:T1000"), 3, 0)
TextBox1.Value = Application.VLookup(ComboBox1.Value, .Range("C2:T1000"), 5, 0)
TextBox2.Value = Application.VLookup(ComboBox1.Value, .Range("C2:T1000"), 6, 0)
TextBox3.Value = Application.VLookup(ComboBox1.Value, .Range("C2:T1000"), 7, 0)
End With
End Sub

This all works fine when I have only this one workbook opened, but as soon as I have a second spreadsheet open I start getting runtime errors related to the dynamiclist range, e.g below

Runtime_error.png

https://dl.dropboxusercontent.com/u/...Dashboard.xlsm

Reposting link

Code to fill dropdown has been working nicely, just this problem when I have more than one spreadsheet opened

Hi - OK I will try it, from memory it was something to do with if I added in a project to the Projects tab and hadn't saved the workbook, it wasn't pickking up the latest additions in the dropdown, but I'll try your code and see if it handles the scenario...

OK, I changed the combobox code to the following

Sub ComboBox1_Change()
ComboBox1.ListFillRange = "'" & ThisWorkbook.Name & "'!DynamicList"
ProjDesc.Value = Application.VLookup(ComboBox1.Value, .Range("C2:T1000"), 3, 0)
TextBox1.Value = Application.VLookup(ComboBox1.Value, .Range("C2:T1000"), 5, 0)
TextBox2.Value = Application.VLookup(ComboBox1.Value, .Range("C2:T1000"), 6, 0)
TextBox3.Value = Application.VLookup(ComboBox1.Value, .Range("C2:T1000"), 7, 0)
End Sub

.. and it now gives me a compile error at .Range on the second line of code?

OK, that was an accident. I will test out the worksheet with your suggested code and see whether I get the error re-occurring.. Thanks

OK, your code for populating the dropdown is fine, but has not resolved the runtime error i was originally getting when multiple spreadsheets are open. When I click debug on the error it goes to this line of code

ComboBox1.ListFillRange = "'" & ThisWorkbook.Name & "'!DynamicList"

Can anyone work out why I am getting this runtime error then?

It is 2010.. It is odd, it seems to happen only when I have another workbook open that also has VBA code in it. if I open just a standard workbook then it doesn't happen

still needing a resolution, basically i am getting the runtime error whenever I have the spreadsheet linked to in this posting opened along with any other spreadsheet that also is running vba code

Ill post the other one in dropbox that causes the error to occur if opened at the same time