Why is this VBA code exiting my form?

Question

I'm fairly new to VBA but have always been able to find my answers online and figure things out...until now.

I have a userform that has several comboboxes. One combobox "cmboCERNumber" list is always static while the other combobox "cmboPONumber" list changes depending on what is selected in the first combobox. The list in cmboPONumber is created temporarily on "Sheet1."

I can get the boxes to change correctly, that's not the problem.

When the form loads, if the user selects a CER Number in "cmboCERNumber" then a new sheet "Sheet1" is created. Some data is copied onto "Sheet1" and the list for "cmboPONumber" is also created. If the user then changes "cmboCERNumber" again, the "Sheet1" needs to be deleted and then the new list for "cmboPONumber" needs to be created.

My problem is that somewhere in the below code, the form is exiting after deleting "Sheet1." I don't want the form to exit.

I've narrowed my problem to the below code by clearing everything out of the sub except for this.

Private Sub cmboCERNumber_Change()
'If the temporary Sheet1 exists, delete it
    Dim SheetExists As Boolean
    Dim shtName As String
    Dim wb As Workbook
    Dim sht As Worksheet
    Dim ws As Worksheet

    shtName = "Sheet1"

     If wb Is Nothing Then Set wb = ActiveWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing

     If SheetExists = True Then
            For Each ws In Worksheets
            If ws.Name = "Sheet1" Then
                Application.DisplayAlerts = False
                Sheets("Sheet1").Delete
                Application.DisplayAlerts = True
                End
            End If
            Next
        Else
        MsgBox ("Sheet1 does not exist")
        'Do other things
        End If
End Sub

EDIT: I changed my method and found a better way to get to the end goal. However, I'm still curious why this exited my form.

seems there is no form name in above code..can u specify the form name. — Techie, Sep 24 '16 at 22:10
http://stackoverflow.com/questions/36491908/whats-the-deference-between-end-and-exit-sub-in-vba — Tim Williams, Sep 24 '16 at 23:15

user3598756 · Accepted Answer · 2016-09-25T14:21:02.697

Other people already told you why your form exited

Here follows some possibly useful tips for your code

Deleting "Sheet1"

there's no need to check if a sheet exist to delete it, just delete it!

you may want to delegate this task to a specific sub like follows:

Option Explicit

Private Sub cmboCERNumber_Change()
    Dim shtName As String

    shtName = "Sheet1"
    DeleteSheet shtName '<-- If the temporary "Sheet1" exists in ActiveWorkbook, delete it

    'do your things
End Sub

an here is the specific sub code:

Sub DeleteSheet(shtName As String, Optional wb As Variant)
    If IsMissing(wb) Then Set wb = ActiveWorkbook '<--| assume Active Workbook if no workbook has been passed
    Application.DisplayAlerts = False
    On Error Resume Next '<--| ignore error messages should passed name worksheet not exist
    wb.Worksheets(shtName).Delete '<--| delete passed name worksheet, be it there or not!
    Application.DisplayAlerts = True
End Sub

where it accepts an optional argument for the Parent workbook of the worksheet to be deleted: if this argument is not passed then Activeworkbook is assumed as the Parent workbook

furthermore it uses Worksheets collection instead of Sheets since this latter "can contain Chart or Worksheet" while the former contains worksheets only

Eco use of "Sheet1"

being "Sheet1" a temporary sheet to be used throughout Userform life then you could simply:

declare a variable for it at userform level

i.e. place the following code at the top of your userform code pane before any other sub or function code
```
Dim tempSht As Worksheet '<--| declare the temporary sheet variable at userfom level
```
this way all subs/functions in the userform code pane will see tempSht without any need to always set it, which must be done at a proper early stage (see below)

create it upon your userfom loading

this could be done through UserForm_Initialize sub

Private Sub UserForm_Initialize()
    Set tempSht = SetSheet("Sheet") '<--| initialize temporary sheet to be used throughout your userfom life

    '... other code for your userform initialization
End Sub

which demands the temporary sheet creation to the following Function:

Function SetSheet(shtName As String, Optional wb As Variant) As Worksheet
    If IsMissing(wb) Then Set wb = ActiveWorkbook '<--| assume Active Workbook if no workbook has been passed
    On Error Resume Next '<--| ignore error messages should passed name worksheet not exist
    Set SetSheet = wb.Worksheets(shtName) '<--| check if there's already a worksheet with passed name
    If SetSheet Is Nothing Then '<--| if it was not there then...
        Set SetSheet = wb.Worksheets.add.Name = shtName '<--| ...add a new worksheet
        SetSheet.Name = shtName '<--| ...and rename it
    Else '<--|... otherwise
        SetSheet.UsedRange.ClearContents '<--| ...clear its content
    End If
End Function

clear its content before any subsequent new activity with it (e.g. at any cmboCERNumber value change)

this could be done via ClearContents() method of Range object like follows:
```
Private Sub cmboCERNumber_Change()        
    tempSht.Cells.ClearContents '<--| clear temporary sheet content

    'do your things
End Sub
```

delete it once it's no longer needed, i.e. at userform exiting or closing

this could be done by this sub:

Sub DeleteTempSheet()
    Application.DisplayAlerts = False
    tempSht.Delete '<--| delete temporary sheet
    Application.DisplayAlerts = True
    Set tempSht = Nothing
End Sub

to be called in the userform exit event handler sub

usually the userform exit event handler sub is some button Click event handler

Private Sub OkButton_Click()
    DeleteTempSheet '<--| delete temporary sheet

    ' other code to be run at exiting userform
End Sub

but you'd better put it in the UserForm_QueryClose() as well to be sure the temporary sheet gets deleted even if the user exits the userform by clicking white cross at the top-right

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    DeleteTempSheet
End Sub

Thanks for the great advice! – cwdreiling Sep 25 '16 at 17:19 — cwdreiling, Sep 25 '16 at 17:19

Why is this VBA code exiting my form?

1 Answers1