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

sathyaganapathi

Board Regular
Joined
Apr 29, 2021
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • runtimeerror380.jpg
    runtimeerror380.jpg
    28.4 KB · Views: 10

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

sathyaganapathi

Board Regular
Joined
Apr 29, 2021
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Apr 29, 2021
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Attachments

  • userform.jpg
    userform.jpg
    47.3 KB · Views: 12
  • error380.jpg
    error380.jpg
    29.1 KB · Views: 11
  • debug.jpg
    debug.jpg
    64.3 KB · Views: 11

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,926
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Apr 29, 2021
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Board Regular
Joined
Apr 29, 2021
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Apr 29, 2021
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
 
Solution

Forum statistics

Threads
1,147,479
Messages
5,741,386
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top