5 replies to this topic

[K e n]

Can one of you kind experts please point me (VBA dilettante) in the right direction?

 

Situation:

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).

 

Problem:

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.

 

Questions:

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?

 

Cheerio

 

Ken

 

 

 

 

[paws]

Hi Ken, I noticed that no one had yet responded to your post, so thought I'd take a look.

Are you sure this is not a business issue?

We tend to deal, here on the WTT forum with folks' private computers used for non business purposes leaving people with business type issues to consult their usual IT adviser or buy in the expertise from a consultant who deals in these areas.

Have a look at the Terms of Use... here's an extract:

"What the Tech is a family friendly site, accessible to anyone over 13 years of age. Please keep all content appropriate.
We offer free computer help and tech support for home and personal use. We are not here to support others that work for profit, or to support/replace your company's IT department."

Post back if I have misunderstood the situation.
Regards
paws

[K e n]

Hello, Paws.

Not business. I'm making a household inventory for my wife, who hates to have anything out of her sight; trouble is she gets easily discouraged when faced with computer programmes like Access or Excel. So I'm trying to present her with a very simple interface and with lists of household and clothing items, what containers they're stored in and where these containers are in the attic.

 

As far as the problem is concerned, I seem to have found a solution by accident. At least, the error message and its cause have not appeared in several days. Before that it was driving me nuts.

 

Thanks for picking up the ball, here, by the way. I thought my question must have been so boring that everybody was ignoring it (maybe that's still true!).

 

Regards

 

K e n

[paws]

 Hi Ken
Thanks for the extra information, and glad to hear you got it fixed.

 

If any other folks are suffering, trying to figure out a similar issue, it would be great if you could post back with the steps that worked for you...

 

The search engines will then index it and maybe other folks will bless the name of Ken if it helps them with their issues.

Regards

paws

[K e n]

Well, Paws, I don't really know what I did.

Possibly, because of my working on two monitors simultaneously and in debug mode, I had been failing to see that a dialogue box (modal) was in fact open; when the code window was activated for editing, any dialogue box positioned on the same monitor got concealed behind it  and I had been thinking it had been unloaded or "hidden" (in the VBA sense). This would, of course, be in line with the sense of the error message.

 

Regards

 

K e n

 

[paws]

Ok

All understood Ken

Thanks for responding

 

Regards

paws