Intermittent Row Source Issue

[dellehurley]

Hi
I have a procedure to reset a userform. It works fine except for every now and then the "Could not set the rowsource property. Invalid property value". I cannot find what the issue is, I have tried changing the rowsource (eg named source, by naming the range) while the issue is debugging the second rowsource throws the error too if I comment out this line. BUT the weird thing is if I end the procedure and then reenter the exact/different details into the userform and the error does not appear again for hours. Any ideas what the issue might be?
Dannielle
The line of code with the issue plus named range also the range also saved in name manager.

PopWs.Range("A2:A" & aLastRow).Name = "Categories"

    .cmbCategory.RowSource = "Populate!Categories"   'Populate!A2:A" & aLastRow

The entire procedure

Sub Reset_Enter()

Dim DbLastRow As Long
Dim BgtLastRow As Long
Dim Payee As String
Dim aLastRow As Long
Dim cLastRow As Long
Dim TomorrowDt As Date

DbLastRow = DbWs.Cells(DbWs.Rows.Count, "A").End(xlUp).Row
BgtLastRow = BgtWs.Cells(BgtWs.Rows.Count, "A").End(xlUp).Row
cLastRow = PopWs.Cells(PopWs.Rows.Count, "C").End(xlUp).Row
aLastRow = PopWs.Cells(PopWs.Rows.Count, "A").End(xlUp).Row
TodayDt = Format(Now(), "dd/mm/yyyy")
TomorrowDt = Format(Now + 1, "dd/mm/yyyy")

DbWs.Range("A1:G" & DbLastRow).Name = "AllDatabase"
DbWs.Range("A2:G" & DbLastRow).Name = "Database"
PopWs.Range("C2:E" & cLastRow).Name = "Payees"
PopWs.Range("A2:A" & aLastRow).Name = "Categories"
BgtWs.Range("A2:N" & BgtLastRow).Name = "BudgetList"

BgtWs.Range("ALL_ONE").Value = ""
SchWs.Cells(1, 1).Value = "" 'date held when start date given is in the past and next date not supplied until Submit Rpt cal

DbWs.Range("Database").Sort Key1:=DbWs.Range("A:A"), order1:=xlAscending, Header:=xlYes
PopWs.Range("Categories").Sort Key1:=PopWs.Range("Categories"), order1:=xlAscending, Header:=xlYes
PopWs.Range("Payees").Sort Key1:=PopWs.Range("C:C"), order1:=xlAscending, Header:=xlYes
    
With frmEnter
    .chkVary.Value = False
    .txtAmount.Value = ""
    .cmdEdit.Caption = ""
    .txtRowNumber = ""
    .txtEndDt = ""
    .chkCat.Value = False
    .chkPay.Value = False
    .chkNoEnd.Value = False
    .cmbFreq.Value = ""
    .cmbFreq.AddItem "Once Only"
    .cmbFreq.AddItem "Daily"
    .cmbFreq.AddItem "Weekly"
    .cmbFreq.AddItem "Fortnightly"
    .cmbFreq.AddItem "Monthly"
    .cmbFreq.AddItem "Quarterly"
    .cmbFreq.AddItem "Tri Annually"
    .cmbFreq.AddItem "Bi Annually"
    .cmbFreq.AddItem "Annually"
    .cmbCategory.Value = ""
    .cmbCategory.BackColor = RGB(255, 255, 255)
    .cmbCategory.RowSource = "Populate!Categories"   'Populate!A2:A" & aLastRow
    .cmbPayee.Value = ""
    .cmbPayee.BackColor = RGB(255, 255, 255)
    .cmbPayee.RowSource = "Populate!Payees"
    .optBill.Value = False
    .optDeposit.Value = False
    Call EnterColour
    With .DTPickersStart
        .Value = Now
        .CustomFormat = "dd/mm/yyyy"
    End With
    With .DTPickerEnd
            .CheckBox = True
            .Value = Null
            .CustomFormat = "dd/mm/yyyy"
            .MinDate = TomorrowDt
            .UpDown = True
    End With
    
    .txtRepeat.Value = ""
    .lstDatabase.ColumnCount = 14
    .lstDatabase.ColumnHeads = True
    .lstDatabase.ColumnWidths = "50,50,50,25,50,50,100,80,75,10,50,0,0,0"
    
    If BgtLastRow > 1 Then
        .lstDatabase.RowSource = "BudgetList"
    Else
        .lstDatabase.RowSource = "Budget!A2:N2"
    End If
 End With
   
End Sub

Budgeting Tool 1.xlsm
	A
1	Categories
2	+ Add Item
3	Electricity
4	Gifts
5	Groceries
6	Holiday
7	Housing
8	Insurance
9	Medical & Healthcare
10	Mortgage
11	Pension
12	Personal
13	Rates
14	Recreation
15	Salary
16	Savings
17	Transportation
18	Utilities
19	Water
Populate

 

[Akuini]

Well, do not use Rowsource:
This is from VBA for smarties How to fill a Combobox / Listbox
It says:
"If you use 'rowsource' you make a direct link to a certain range in a worksheet.
Combined with the combobox it can cause Excel to blackout, because any change in the combobox will be transferred directly to the 'source'.
Excel has proven not to be able to do this correctly.
Besides: the changing of the source at every change in the combobox slows down your code.
You should reduce the reading/writing from/to a workbook in your code as much as possible."

Without Rowsource, you can insert the range directly into the listbox like this:
First, clear Rowsource property of the combobox, then:
Combobox1.List = Sheets("Sheet1").Range("A1:A10").Value
or using a named range:
Combobox1.List =Range("myRange").Value

 

[dellehurley]

Well, do not use Rowsource:
This is from VBA for smarties How to fill a Combobox / Listbox
It says:
"If you use 'rowsource' you make a direct link to a certain range in a worksheet.
Combined with the combobox it can cause Excel to blackout, because any change in the combobox will be transferred directly to the 'source'.
Excel has proven not to be able to do this correctly.
Besides: the changing of the source at every change in the combobox slows down your code.
You should reduce the reading/writing from/to a workbook in your code as much as possible."

Without Rowsource, you can insert the range directly into the listbox like this:
First, clear Rowsource property of the combobox, then:
Combobox1.List = Sheets("Sheet1").Range("A1:A10").Value
or using a named range:
Combobox1.List =Range("myRange").Value

Thank you for that, I will update the file now and I truly appreciate the help.
Dannielle

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[dellehurley]

You're welcome, glad to help & thanks for the feedback.

Hi I just want to let you know I have had another issue arise due to the change from rowsource to list, in case you're interested this is the post. Userform Listbox reversing date format from database.
Dannielle