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?
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?