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

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
423
Office Version
  1. 2010
Platform
  1. Windows
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?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,159
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
423
Office Version
  1. 2010
Platform
  1. Windows
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
VBA Code:
Application.EnableEvents = True
to be IN FRONT OF of the unloading of the form part. (y)

Previously I had it listed like this:
VBA Code:
'
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. (y) (thank you!)

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

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,159
Office Version
  1. 365
Platform
  1. Windows
Glad I could help. Perhaps mark this one as solved then?
 

Forum statistics

Threads
1,181,219
Messages
5,928,751
Members
436,627
Latest member
caligirl626

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top