Form UNLOAD issue on error

[jvoss]

Good day all. I have a snipit of code that is on the usersform1 code sheet that runs on initialization of usersform1. However when it errors on <> "Blank" (in Sheet1 row x : cell H) the userform1 loads with the values in "Properties, Appearance, Value" (used this to debug). (not sure if this is the right wording)

Sub Initialization_Form

Dim emptyRow As Long

'selects active sheet for checking if row x is ready.
     Sheet1.Activate

'Determine emptyRow
     emptyRow = WorksheetFunction.CountA(Range("i:i")) + 1 '<---------used to find first unused row in column i

'verify "blank" in first unused row, cell h

'MsgBox Sheets(1).Cells(emptyRow, 8).Text      '<---------debugging Msgbox

'On Error Resume Next ' <---------debugging statement will set once below issue is fixed.

     If Sheets(1).Cells(emptyRow, 8).Text = "Blank" Then '<---------check to see if next row has "Blank" in the "H" cell

     TextBox4 = "Blank" '<---------put this in the TextBox4 if row is "Blank"

     Else: '<---------if cell H is not "Blank"  show message and stop intiliziation and unload (me)
     MsgBox "There seems to be an error" & Sheets(1).Cells(emptyRow, 8).Text & " is not ready.", 16, "DATA ERROR"
     Unload (UserForm1) '<-----------------------------   this is the issue.  if this is not in the userform1 shows up after the sub exits
     Exit Sub
     MsgBox "sub exited", 16, "Fix Error"  '<--------------Debugging MsgBox

     End If
'rest of code to set all text boxs to ""
'as apposed to what is in the "Properties, Appearance, Value" (debugging)

End Sub

thanks in advance for the help.

 

[Jimmypop]

Hi there

What happens if you change

Unload (UserForm1)

to

Unload UserForm1

 

[jvoss]

Hi there

What happens if you change

Unload (UserForm1)

to

Unload UserForm1

same results with either way. when dose the unload or show of table happen in the stack...

 

[jvoss]

when running this way

Unload UserForm1

the code steps through and stops on
Exit Sub with a Runtime error 91
Object variable or with block variable not set

this sub is setup as Private Sub will that effect the Exit Sub statement?

 

[jvoss]

Problem is resolved. the error is/was caused by the original call for the form to show.
Since during the stack this call was never finished and the IF/THEN statement erred..

more information: by step.
step1 user activitates button to show USERFORM
step2 USERFORM is Initialized.
step3a userform call for check rows
step3b check row is good then Userform initialization form completed and step4
step3c if check row errors then sub is exited
step3d show userform request errors out and with ON ERROR RESUME NEXT set it ends sub
step4 show USERFORM is completed.