Can one of you kind experts please point me (VBA dilettante) in the right direction?
I have made an Excel workbook for a user who has no idea about Excel and who should not come into contact with it. The WB has two sheets, one a list of ITEMS, and one a list of CONTAINERS.
On activating a sheet a non-modal form is called containing command buttons for sheet-appropriate subroutines such as entering new items/containers, deleting same, editing data, searching for keywords in the lists, printing hard copy, changing to other page... The command buttons each fire a dialogue box shown modally on top of the non-modal menu form. Each modal form (dialogue box) must, of course, be closed after carrying out its tasks in the appropriate list and before the user can start a new task or access the excel cells directly in any way. On deactivation of a sheet, the menu form (non-modal) is unloaded (to refresh/update its labels).
This set-up works OK in general, but in certain sets of circumstances which I cannot fathom, when calling up the modeless menu list in the Worksheet_Activate() event on changing from one page to the other, the makro gives me a runtime error 401 message ("Can't show non-modal form when modal form displayed") and th edebug option marks the command line "frmITEMS.show vbmodeless" or "frmCONTAINERS.show vbmodeless resp.. I understand the message all right; but I cannot locate any modal form being displayed.
What are the commands to run through the opened forms and list their names? If I knew which forms were causing the hitch, I might get closer to a solution.
What might be causing the problem and what can I do to avoid it?