Obect variable appears to loose definitions after sucessive cancel clicks in userform

glfiedler · Apr 4, 2019

I wrote this project some 15 years ago and my wife has used it successfully for all these years to maintain our household finances. Very infrequently (maybe once a year) she will get the "Object variable not set" error. Of course I can get her up and running in a minute but I was always unable to see what caused it. Being retired now I have the spare time and decided to correct this bug or at least create a work-around.
The project has several worksheets but the one of interest to this problem is the main "desktop" for the app. It contains eight command buttons that let her enter data for a specific type of action. All the userforms have very similar code and I will post a typical one below. The routines that the buttons call are basically the same, that is they initialize the userform, get the input, and, if was clicked the module code will process the data and finally unload the userform. If the "X" or is clicked then my public cancel flag (Cancel_Box) will ignore the input, if any, and unload the userform. Everyday kind of stuff and it works.
Here is the problem: To stress-check the project I click a command button then immediately click either the "X" or . I repeat again and again with the other buttons. Sooner or later the code crashes with the "Object variable" error calling out one of my sheet names. It does not crash when closing the userform but when clicking on another button that starts a module routine that refers to one of the object names. It is like unloading the userform kills my object definitions. Nothing in my module code sets the object variables to "Nothing" or changes them in any way. I have included the top of the module code to show you how I Set the object variables. Notice that I Set the variables in the Aut

pen routine.

Here is the beginning of the module code:

Code:

Option Explicit
'Declare objects
    Public DeskTop As Worksheet
    Public Ledger As Worksheet
    Public Tables As Worksheet
    Public credit As Worksheet
    Public Checking As Worksheet
    Public BofI As Worksheet
    Public Tax As Worksheet
    '---------------------------
'Declare variables
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim numAcc As Integer
    Dim numCards As Integer
    Public blank As Integer
    Public PaymentMethod As Integer
    '-------------------------------
    Public PurDate As String
    Dim AllocateDate As String
    Dim FName As String
    Dim title As String
    Dim msg As String
    Dim result As String
    Dim style As String
    Public PickDate As String 'Used for pick date from calendar feature
    '-------------------------------
    Dim Charge As Currency
    Dim Adjustment As Currency
    '-------------------------------
    Dim Oldest As Date
    '-------------------------------
    Dim rowS As Long
    Dim rowE As Long
    '-------------------------------
    Public Cancel_Box As Boolean 'Flag indicating or "X" in userforms
'*******************************************************
Sub Auto_Open()
'Set all worksheet names
    Call Set_Worksheets
'Display Introduction
    InitializeFM.Show
End Sub
'*********************************************************
Sub Set_Worksheets()
    Set DeskTop = ThisWorkbook.Worksheets("DeskTop")
    Set Ledger = ThisWorkbook.Worksheets("Ledger")
    Set Tables = ThisWorkbook.Worksheets("Tables")
    Set credit = ThisWorkbook.Worksheets("Credit")
    Set Checking = ThisWorkbook.Worksheets("Checking")
    Set BofI = ThisWorkbook.Worksheets("BofI")
    Set Tax = ThisWorkbook.Worksheets("Tax")
End Sub

Here is a typical userform code:

Code:

Private Sub Accountlist_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Routine to ignore double click on listbox
    Cancel = True
End Sub

Private Sub Check_FIB_Click()
    ThisWorkbook.Worksheets("Tables").Range("H3").Value = 1
End Sub

Private Sub Check_Vanguard_Click()
    ThisWorkbook.Worksheets("Tables").Range("H3").Value = 2
End Sub

Private Sub CmdCancel_Click()
    Cancel_Box = True
    Unload Me
End Sub

Private Sub cmdOK_Click()
    Me.Hide
    Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
    [COLOR=red]Call Set_Worksheets 'This is a work-around for "Set" error.  This statement was NOT in the original code.[/COLOR]
    Me.Amount.SetFocus
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then Cancel_Box = True
End Sub

Here is typical code that the command button calls:

Code:

'Routine to deposit sum into checkbook under a specific account
'Called by Desktop button "Deposit"
Sub Deposit()
    Load DepositFM
    DepositFM.ActionDate.Text = Int(Now)
    DepositFM.Amount.Text = ""
'Restore previous Institution
    Select Case Tables.Range("Institution").Value
        Case 1: DepositFM.Check_FIB.Value = True
        Case 2: DepositFM.Check_Vanguard.Value = True
    End Select
    Call CountAcc
    Call CreateAccList(DepositFM)
reshow3:
    Cancel_Box = False
    [COLOR=red]DepositFM.Show[/COLOR]
    [COLOR=red]If Cancel_Box = True Then Exit Sub[/COLOR]
    If DepositFM.accountlist.ListIndex < 1 Then GoTo warning11
    If DepositFM.Amount.Text = "" Then GoTo warning4
'Enter the deposit into checking and account
    Call BlankLedger
'Enter date & description
    Ledger.Range("LedgerDate").Offset(blank, 0).Value = DepositFM.ActionDate.Text
    Ledger.Range("LedgerDate").Offset(blank, 1).Value = "Deposit"
'Enter amount into proper account
    Ledger.Range("LedgerDate").Offset(blank, DepositFM.accountlist.ListIndex + 1).Value = DepositFM.Amount.Text
'Enter tranaction into appropriate financial institution
    Select Case Tables.Range("Institution").Value
    Case 1  'Bank of Internet checkbook
        Call BlankCheck
        Checking.Range("iCheckBookDate").Offset(blank, 0).Value = DepositFM.ActionDate.Text
        Checking.Range("iCheckBookDate").Offset(blank, 1).Value = "Deposit"
        Checking.Range("iCheckBookDate").Offset(blank, 2).Value = DepositFM.Amount.Text
    Case 2  'Vanguard checkbook
        Call BlankVanguard
        Checking.Range("iVanguardDate").Offset(blank, 0).Value = DepositFM.ActionDate.Text
        Checking.Range("iVanguardDate").Offset(blank, 1).Value = "Deposit"
        Checking.Range("iVanguardDate").Offset(blank, 2).Value = DepositFM.Amount.Text
    End Select
    Unload DepositFM
Exit Sub
'Warning for no account selected
warning11:
    MsgBox "Please select an account.", vbExclamation, "Deposit Dialog Box Warning"
    GoTo reshow3
'Warning when amount is blank
warning4:
    MsgBox "Please enter the amount of the Deposit.", vbExclamation, "Deposit Dialog Box Warning"
    GoTo reshow3
End Sub

This particular example is for a "Deposit" and shows the "DepositFM" userform. I have the following questions:

1) Is setting object variables in the Aut

pen routine a good idea?
2) What in my code is apparently deleting the object variable definitions?
3) What suggestions do you have that would eliminate my clumsy work-around of continuosly resetting the object variables every time I load a userform?

Your help will be greatly appreciated.

Yongle · Apr 7, 2019

Sheet variables are unnecessary
Eliminate your problem by using sheet VBA CodeNames
A sheet's VBA codename is set by VBA when the sheet is created, but can be amended to make it more user-friendly

https://www.wiseowl.co.uk/blog/s112/microsoft-excel-vba-worksheet-sheet-name-codename.htm

glfiedler · Apr 7, 2019

Yongle, Thank you so very much for the link to the blog about CodeNames. I will modify my code to eliminate the object variables defining the tabs. I like not having to set the variables. This does not answer why unloading the userform killed the object variables. I found somewhat of an answer in a RubberDuck blog. https://rubberduckvbawordpress.com/2017/10/25/userform1-show/ According to the Duck, you should NEVER unload a useform while in the userform. That is, your command button code should never use Unload Me. This destroys the userform while the calling procedure is sitting there at the userform.Show line. It apparently is not clear what the calling procedure will do while sitting there waiting for the userform to return when in fact it has been deleted! I suppose I have been lucky for all these years to get away with unloading the userform while in the userform. So my cancel button code will change from Unload Me to Me.Hide and have the calling procedure unload it as appropriate. This leaves the "X" box in the userform which, by default, will unload the userform. So I will add Cancel = True and a call to my cancel button routine in the QueryClose procedure to prevent the "X" from unloading the userform. These changes should should fix my problem. Thanks.

Yongle · Apr 8, 2019

Variables created within a userform have the same life as the userform - all values are lost when the userform is unloaded

Calling Set_Worksheets from event macro Workbook_Open (which resides in ThisWorkbook module) would also have worked for you but IMO using sheet CodeNames is a better (and simpler) solution

Thanks for the feedback

Obect variable appears to loose definitions after sucessive cancel clicks in userform

glfiedler

Board Regular

Excel Facts

Yongle

Well-known Member

glfiedler

Board Regular

Yongle

Well-known Member

Similar threads

Forum statistics

Share this page

Obect variable appears to loose definitions after sucessive cancel clicks in userform

glfiedler

Board Regular

Excel Facts

Yongle

Well-known Member

glfiedler

Board Regular

Yongle

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock