VBA stops working… but only for certain/specific functions…?

[kbishop94]

I have a user form that I use as a search function to find certain records that match the pre selected criteria chosen by the user.

The userform is activated via a worksheet tab. User clicks on the tab, and the userform is opened and initialized.

After running a search, and if a ‘no cells were found’ error condition occurs, then the userform reloads for another search. (instead of showing the VBA error message box, the code redirects via onerror goto and displays my own message box that states ‘no records were found, please try another search selecting different criteria’)

It’s at this point that things can start to go sideways.

If the user does NOT close out the form and instead performs another search (and it’s successful) then everything behaves normally and no problem exists.

However, if at this point the user instead closes out the form, he is redirected back to the main worksheet (which is what is supposed to happen.) But, when the user goes to open the form up again (via clicking on the worksheet tab) nothing happens this time and instead it’s like it’s in design mode and all that happens is the blank worksheet is shown instead of populating and opening the userform.

However, if I assign the code to open the userform into a command button, and the same set of conditions are encountered again, then it will work fine(!)

What is different that the same code to open a userform works when activated via a command button but not when activated via a worksheet tab?

 

[Micron]

Can only guess that in the first scenario, an application setting that you disabled isn't being enabled again. Perhaps Application.EnableEvents?
Suggest that you put a break point at the start of the code, click the sheet tab as before and step through the code (F8) and watch how things execute.

 

[kbishop94]

Can only guess that in the first scenario, an application setting that you disabled isn't being enabled again. Perhaps Application.EnableEvents?
Suggest that you put a break point at the start of the code, click the sheet tab as before and step through the code (F8) and watch how things execute.

Thank you. I saw what was wrong (you mentioning re-enabling events got me to dig alittle deeper into this...)

I moved my

Application.EnableEvents = True

to be IN FRONT OF of the unloading of the form part.

Previously I had it listed like this:

'
Unload Me
frmReportCriteria.Show
'
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
'
Exit Sub
'

And now that I changed it to like this, I havent ran into the issue where the worksheet tab suddenly stops working. (thank you!)

'
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
'
Unload Me
frmReportCriteria.Show
'
Exit Sub
'

 

[Micron]

Glad I could help. Perhaps mark this one as solved then?