New Excel Forum

This forum has been moved to

Ask all future questions in the New Excel Forum.


Advanced MsgBox / InputBox / TextBox Messages

Free Excel Macros

Advanced MsgBox / InputBox / TextBox Messages

Postby Sisyphus » Sun Feb 02, 2014 3:29 am

If you ever felt that MsgBox texts clutter up your code you are a likely supporter of having a Message Text Library from which a text might be called with simple code, like MsgLib 1 to recall text #1. But if you had such a library you would likely wish to explore its further possibilities, like letting MsgLib 1.2 return a different message than MsgLib 1.1, like the same message in another language or another choice of word.

Of course, if you have read up to here you are likely willing to take the plunge. How about modifying the message depending upon parameters fed to it as arguments? Like, if you need 80% to pass and else fail, why not create a message like "You have {[<80] "FAILED", "PASSED"} this test with {85}%." and let the code figure out which information to impart based on the number (85) furnished as argument?

Are you tired of seeing "There are 1 apples in the basket"? Would you prefer to see "There are no apples" in place of "There are 0 apples"? The attached code can do all of that and much, much more. I am attaching two versions of it. The workbook marked SOURCE CODE contains fully commented code and a special module for testing. It also has testing examples in its library. The other workbook has identical code minus the testing module and form and very few comments. Better start with the Source Code versions and try some of the tests.

Structurally, the code consists of three parts.
  1. The message library
    This is a worksheet where you can enter your messages. It also contains 25 rows of help. This Help is available in both attached versions even though the worksheet will hide itself once it is deployed. Read the comments in the ThisWorkbook module to better understand this functionality. Click on the Help! button and read the Help.
    Finally, study the examples. The sheet shows the message text, the function call and the resulting message - from simple to intricate.
  2. The Message class
    This code takes the message number and arguments supplied by the calling procedure, picks the message from the library and uses the parameters according to the instructions contained in the message itself. It returns a pair of strings, the message body and its caption.
  3. The interface functions InfoBox, DataBox and MsgText
    InfoBox is another name for MsgBox, DataBox replaces InputBox and MsgText returns the same strings for use in MSForms Controls like Labels or TextBoxes.
All three functions have the same syntax.
Code: Select all
FunctionName MsgID, [Language], [Auxiliary], [Arg1], [Arg2], [Arg3, ... [Arg256]

As you see, all arguments but one are optional. InfoBox 1 will do much the same as MsgBox "Something". Astr = MsgTxt(1) will do much the same as Astr = "Something". The difference is in having the convenience of managing the strings in a library and being able to return modified strings which would otherwise require custom code to be written for each function call.

The Message class "understands" three codes.
  1. INS [Insert]
    Message simply replaces the placeholder found in the message text (as deposited in the library) with the argument supplied by the calling procedure.
  2. REF [Refer]
    Message refers to the argument and inserts one or the other or no text into the message depending upon which value is passed as the argument to be referenced. For example, "PASSED" if the value is 80 or higher. The referenced parameter need not appear in the message.
    EVAL [Evaluate]
    This command conditionally replaces the parameter with a value found in the message text (as retrieved from the library), but does nothing if the condition isn't met. Where, with REF, doing nothing means that there will be no trace of its activity, for EVAL "doing nothing" means that the referenced argument is incorporated in the message unchanged. EVAL works similar to VB's Select Case function. Many conditions can be examined successively until one - or none - is found to be true. This function enables the return of "There are no apples in the basket" where the argument was 0, or "There are x apples in the basket", if the value was a number.
Equipped with this basic knowledge you are ready to go to the Test module available in the SOUCE CODE version and follow the instructions you find there to start playing with the existing examples and, perhaps, add some of your own.

In order to install the functionality in your project please follow the steps laid out below. It doesn't matter whether you opt to install the commented or uncommented code. However, you may refrain from installing the Test module and the form TestMsgText. This is why these two aren't mentioned below. All other parts can be taken from either of the attached workbooks.
  • Copy the worksheet ‘MsgLib’ to the target project.
    Using the ‘Move or Copy’ function available from the tab’s right-click menu will ensure that the code sheet is copied along with the worksheet itself. Check the code sheet after copying to be sure.
  • Drag the class module ‘Message’,the code module ‘MsgMan’, and the code module ‘Utilities’ into the target project in VBE’s ‘Project Explorer’ window.
    In the case of 'Utilities', you can also add the procedures into another, existing standard code module if you so prefer.
  • Copy all procedures from the 'ThisWorkbook' code sheet to the code sheet by the same name in the target project. You can use Ctl+C and Ctl+V.
  • Check, if an event procedure ‘Workbook_Open’ already existed in the 'ThisWorkbook' module transfer the code from the procedure by that same name just pasted into the existing procedure and delete the one just pasted.
  • The code module MsgMan includes the three functions InfoBox, DataBox and MsgText. Any of these that are not required in your project may be deleted.
  • The Utilities module includes functions LastRow, LastColumn and SetRange. All of them are required for this project (for managing the Help in the worksheet MsgLib). However, they are also available for use by your own code. Most projects need at least two of them, and having them around will save you effort elsewhere.
  • Note that the worksheet ‘MsgLib’ will hide itself when the workbook is next opened. To make it visible for admin purposes run the procedure ‘ThisWorkbook.ShowMsgLib’.
Should you need help or - God forbid! - find fault with this code please do contact me. Come back here from time to time and look for updates. Edits to the attached workbooks will be mentioned below this line.

15 Feb 2014 - Version 8.1.1
A procedure was added to better convert the ParamArray received by the utilities into a normal array required by the class. The effect of this modification is that the program can now handle omissions of the language and auxiliary parameters even if more paramters follow, for example, "Test", , ,"apples" is now acceptable in place of, and as well as, "Test", 0, 0,"apples". This modification affects all three interface functions InfoBox, DataBox and MsgText, but none of the rest of the code.

24 May 2014 - Version 2.0
The code tended to throw datatype errors when parameters of different datatypes. This was a nuisance because the errors were inconsistent - gone when setting out to track them down. Finally, the cause was found with variables of no declared type. They are now explicitly declared as of variant datatype. In the same context the syntax for handling ParamArrays was reviewed. The new version should be a good deal more stable.
Affected parts of the code:
  • Declarations & Properties in the Message Class module
  • The procedure ConvertParam in the module MsgMan
  • The 3 procedures InfoBox, DataBox and MsgText, likewise in the module MsgMan
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Former Moderator
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 11 guests