Could not set the RowSource property. Invalid Property Value error when switching between excel files

[sathyaganapathi]

Hi,
I am using VBA to store and get the data from Access database using Userforms.
I used drop down and sub dropdown comboboxes for selection of items. 'Select case' option is used for better control of the comboboxs.
The Rowsource data for both comboboxes are from 'name manager' defined in the same excel workbook.
Combobox style is set to dropdowncombo.

The problem is, when I switch between excel workbook and come back to main workbook where userform is running, I get the error while clicking on the listbox line item to select and edit.
The application closes and again we have to start the macro. This happens only when we switch between workbook and come back.
If we continue work with same workbook it works fine.
Please somebody help to get rid of this.

 

[sathyaganapathi]

Hi,
Can somebody please help me to resolve the issue? me and my team is really strugling due to this error as we have to close the application and start again every time.
Please let me know if you need more info on the issue.
Thanks

 

[sathyaganapathi]

Hi,
I have attached an example and error message I get and also the screenshot of error line.
The error occures sometime when switching between other excel sheets which are open.
Please note that, error line will change when clicking on different line in list box.
Please help on this and let me know if more information is required. I have prepared an excel file. But, unable to upload the file here.

Private Sub CommandButton1_Click()
    'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet2")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.Department.Value
        .Cells(lRow, 2).Value = Me.Process.Value
    End With
    'Clear input controls.
    Me.Department.Value = ""
    Me.Process.Value = ""
    
Call List_box_Data
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub Department_Change()
    Select Case Me.Department.Value
    Case Is = "Copper"
    Me.Process.Text = ""
    Me.Process.RowSource = "Copper"
    Case Is = "Drilling"
    Me.Process.Text = ""
    Me.Process.RowSource = "Drilling"
    Case Is = "Photo"
    Me.Process.Text = ""
    Me.Process.RowSource = "Photo"
    Case Is = "Quality"
    Me.Process.Text = ""
    Me.Process.RowSource = "Quality"
    Case Is = "Relam"
    Me.Process.Text = ""
    Me.Process.RowSource = "Relam"
    Case Is = "Route"
    Me.Process.Text = ""
    Me.Process.RowSource = "Route"
    Case Is = "SM"
    Me.Process.Text = ""
    Me.Process.RowSource = "SM"
    Case Is = "SurfaceFinish"
    Me.Process.Text = ""
    Me.Process.RowSource = "SurfaceFinish"
    Case Is = "BBT"
    Me.Process.Text = ""
    Me.Process.RowSource = "BBT"
    Case Is = "FI"
    Me.Process.Text = ""
    Me.Process.RowSource = "FI"
    Case Is = "PECAM"
    Me.Process.Text = ""
    Me.Process.RowSource = "PECAM"
    Case Is = "Tech"
    Me.Process.Text = ""
    Me.Process.RowSource = "Tech"
    
    End Select
End Sub

Sub List_box_Data()
 
Dim sh As Worksheet
Dim i As Integer
Dim n As Long

Set sh = ThisWorkbook.Sheets("Sheet2")

With Me.ListBox1
    .ColumnCount = 2
    .ColumnHeads = True
    .ColumnWidths = "40,80"

If Me.ListBox1.ListCount > 0 Then
Me.ListBox1.TopIndex = Me.ListBox1.ListCount - 1
End If

n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

If n > 1 Then
 .RowSource = "Sheet2!A2:B" & n
Else
 .RowSource = "Sheet2!A2:B2"
End If
End With
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
 
  If Me.ListBox1.List(Me.ListBox1.ListIndex, 1) <> "" Then
    Me.Department.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
    Me.Process.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
End If
End Sub

Private Sub UserForm_Initialize()
Call List_box_Data
End Sub

 

[sathyaganapathi]

Link to other forum

 

[SQUIDD]

Hi

So i have just set up a workbook, with the userform as you have coded.

I do not recieve any errors.

Does your second workbook contain any code?
Does your main workbook rely on the second workbook, or are users just using excel for other things while your app is open?

Dave

 

[sathyaganapathi]

Hi

So i have just set up a workbook, with the userform as you have coded.

I do not recieve any errors.

Does your second workbook contain any code?
Does your main workbook rely on the second workbook, or are users just using excel for other things while your app is open?

Dave

Hi SQUIDD,
The error occurs when activate any other workbook, click on any cell in that work book and come back to the userform again. The app is still running.
There is no link to any other workbook that are opened.
as you mentioned user just uses other excel for other things while the app is still open.
Hope this info gives some hint. please let me know if any more info required.
Thanks.

 

[RoryA]

Your code requires the workbook containing the named ranges to be the active one. If it might not be, you need to qualify the names with the workbook name.

 

[sathyaganapathi]

Hi RoyA, Thanks for the input.
It is named ranges I used in the workbook. the rowsource for 'Department' and for 'Process' are from named ranges from the same excel.
what is 'qualifying the names with workbook name'? how to do it?
can you please give an example?

Your code requires the workbook containing the named ranges to be the active one. If it might not be, you need to qualify the names with the workbook name.

 

[sathyaganapathi]

Your code requires the workbook containing the named ranges to be the active one. If it might not be, you need to qualify the names with the workbook name.

Hi RoryA,
I can send an example excel with the code and userform in it.. But, how I can send it?

 

[RoryA]

I don't need to see the workbook. You just need to use the same syntax you would in a formula - for example:

'workbook name.xlsm'!Department