CODEWITHSUNDEEP
excelvbauserform
Seb.D
Seb.D

Reputation: 3

Error 91 when unloading form after Error Handling

Hello fellow developers,

I am currently working on a little project for my internship in VBA for Excel (it is the first time I do a project in VBA). The program includes several modules and userforms, and is almost complete, so I implemented Error handling.

I used :

Sub .......
ModName = "NameOfTheModule"
SubName = "NameOfTheSub/Function"
On Error GoTo PROC_ERR

/code

Exit Sub
PROC_ERR:
Call LogGenerator(ModName, SubName, Erl, Err.Number, Err.Description)
Exit Sub

End Sub

(Sorry I can't post the whole file, it is quite confidential)

I wrote that code in every sub or function, along with line numbers, so in case there is an error, it goes to PROC_ERR, calls LogGenerator that'll generate a .txt file with module name, sub/function name, line number, error number. I don't know if that's the best way, but it works a treat.

Until now.

Now, when this code runs in a userform code, the log is generated, but the UserForm doesn't close. Which is pretty dangerous, because it is still "active", meaning the user could still put data and validate, even though there was an error before.

I tried putting "Unload Me "in PROC_ERR, (I tried putting it after/before "Exit Sub", after/before the call of LogGenerator), but I get an error 91 (can't post the picture, I need more reputation...). However, it works, and unloads the UserForm...

I wrote a Sub UnloadAllUserForm that I call with the LogGenerator Sub but get the same error.

I don't have acces to any debugging help, like there is usually in VBA editor (highlighting the faulty line), but I managed, with Debug.Print, to find that it's the line with "Unload" that raises the error.

I added a error handling in that UnloadAllUserform Sub, even trying "On Error Resume Next", but the error still shows!!!

Sub UnloadAllUserForm() 
  On Error GoTo PROC_ERR 
  ModName = "Errhandler" 
  SubName = "UnloadAll"  
  Dim frm As UserForm  
  For Each frm In UserForms 
    frm.Hide 
  Next frm 
  Exit Sub 
  PROC_ERR: 
    Call LogGenerator(ModName, SubName, Erl, Err.Number, Err.Description)
  Exit Sub 
End Sub

I get the error91 only when there's an error in a UserForm_Initialize sub. However, I get error 91 in any UserForm_Initialize sub, whatever the UserForm is. i still put a sample of one UserForm_initialize here :

'Initialisation de l'UserForm
Private Sub UserForm_Initialize()

ModName = "UserFormAjoutItem"
SubName = "UserFormAjoutItem_Initialize"
On Error GoTo PROC_ERR


10  'On définit la page "Ajout pièce comme la page par défaut à l'ouverture
20  MultiPage.Value = 0
30
40  'Pour chaque objet de type TextBox présent dans l'UserForm, on       initialise à valeur vide
50  Dim z As Control
60  For Each z In UserFormAjoutItem.Controls
70  If TypeName(z) = "TextBox" Then
80      z.Value = vbNullScript
90  End If
100 Next z

Exit Sub

PROC_ERR:
Unload Me
Call LogGenerator(ModName, SubName, Erl, Err.Number, Err.Description)
Exit Sub


End Sub

I never had anything like that and I'm already out of ideas.

Your help would be much appreciated, thanks a lot!

Upvotes: 0

Views: 1165

Answers (1)

DragonSamu
DragonSamu

Reputation: 1163

As stated by Me and user3964075, You can't unload in an Initialize event.

You would have to pass a Boolean that after the Initialize event gets checked in a sub
If TRUE= Unload if False = continue

Upvotes: 0

Related Questions