Modeless Message box (Excel 2000)

Topic

New Reply

My modeless message box is used to pause my macro while the user makes a change on the active worksheet.
My problem is that I don’t know how to stop/pause code execution when the message is displayed, so I end the procedure, and the click event in the message box calls a new procedure. This is all very well except when it’s a modification to existing code and all the dim statements have to be reviewed.
Is there an alternative to the modeless message box or another way to use it?

Reply | Quote

Replies

> except when it’s a modification to existing code and all the dim statements have to be reviewed.

Do you mean that the user modifies the code while the message box is displayed? That seems an undesirable situation to me. If I misunderstood, please explain what you mean by the above sentence.

Reply | Quote

Hans,
Do you ever sleep?
Sorry, what I meant was that when I modify my code to accommodate the Modeless message box I have to move my dim statements around because now I have two or more procedures. Maybe I’m too restrictive in the scope of my variables, but I only declare them before the procedure when they apply to more than one.

Reply | Quote

Why not use some type of “non-modeless” inputbox(es) (which stops code execution) and find out what the user wants to change and than have the code change the activebook.

The your procedure does not have to stop.

Steve

Reply | Quote

Steve,
Let’s say we have a list of 10 names and the macro is going to summarize some data for each of the 10. I want the user to review the list and see if any names have to be added or subtracted, and to make sure they are all spelled exactly as they are in the data. The modeless msg remains on the screen while the user edits the list. Is there a better way?

Reply | Quote

Several options come to mind with the limited info you give. What is best will depend on your needs and wants and abilities:

1) you can make him have the correct entries before running the code. Have a message box asking if there are any errors (or have code check for errors at the start of the code). If there are errors, quit the code and tell the user to rerun after fixing them (if automatic, you can select the cells that are in error, or even list color them, etc)

This bypasses the need to have them make changes while your code is running. It teaches them to enter data correctly before running routines…

2) If you want to have them change code in the middle of a routine, you can create userform which lists the items and have them edit it in the userform. (which “grab” the values from the ranges). After they finish editing the form, the code can validate the entries and, if valid, it can place the values in the appropriate cells, and then continue with the code. You are editing the workbook in the middle of code and not needing the code stopped.

If “automatically” testing, you can indicate which values are “bad” or even just display the “bad values” for editing.

3) you can use an application input box and have the user select the cell to edit, read the value and have them edit it a text input box. You just loop thru these 2 sections (choose a range, edit the value) until they accept it as ok.

Number 1 is probably the easiest to code. If not correct, quit and make them rerun.
Number 2 or its variants seems to be the “most professional looking” of all and (probably) easier of #2 and #3.
Number 3 does not require a user form to be created but gets “clunkier” with all the checking and validation.

Steve

Reply | Quote

Steve,
Thank you for taking the time to suggest the various options. The Modeless msg box is notably absent, so I take it you don’t see a better way to use it than I am.
With respect to option 1) There are a number of steps the code must complete before we get to checking the list, so re-starting will not work. Moreover, in principle, I am not happy with code that requires users to be “taught” anything; if the interface is not intuitive it needs improvement.
2) Yes, I could create a Userform, but that would seem to be a lot more work than my modeless msg box. There is no way to validate the input. I have no way of knowing which of the following names is correct: ABC Inc|ABC Inc.|ABC-Inc|
3) This gets closer to what I need, but does seem a bit clunkier than the modeless msg box.
Good ideas but, as you say, with the limited amount of information I provided, not ideal.
Thanks

Reply | Quote

But just allowing to edit the userform in #2 (even without validation) is not that different than editing in the workbook (which also does not have validation).

Instead of allowing them to edit directly in the workbook, you are requiring them to edit in the userform (which you could make appear to be “like a worksheet” page if desired.

[indent]

---

Moreover, in principle, I am not happy with code that requires users to be “taught” anything; if the interface is not intuitive it needs improvement.

---

[/indent]

I am not saying the interface is not intuitive or needs improvement. What I suggest is teaching them to do what you say you want them to do (while the code the running):

[indent]

---

I want the user to review the list and see if any names have to be added or subtracted, and to make sure they are all spelled exactly as they are in the data

---

[/indent]

I think that should be done before the code starts. Ask them to check before the code proceeds too far. (just to ensure that is correct). It seems like you want them to start running code, then part way thru, start their checking. The checking should be done before the code is run, or thru a more controlled means (eg userform) while the code is run.

Concerning whether ABC Inc or ABC Inc. ABC-Inc is correct, can’t you check to see what is in the “data” (match function, looping and comparing), etc. If the user can tell which is correct, the code should at least be able to indicate items that do not exist in the “data”

Perhaps I just don’t completely understand what you are doing…
Steve

Reply | Quote