As it turns out, the little Data validation input message box has a Class name of EXCELA.I found out using WindowSpy. So i thought maybe I could use some API functions and the Selection_Change event in order to format its background color and text. And guess what it worked! Well at least in excel 2003 where i tested it.
I have a string (msg) that is pretty much a very long list of items. I need to put this in a msgbox but it is not long enough to show the whole text. Is there an alternative to this?
Thank you!
user2385809user23858092 Answers
The Message Box function is a built-in function of VBA and cannot exceed 1024 Characters. You are limited to creating your own UserForm or some other alternative... Such as opening and writing to an unsaved instance of notepad...
An ALL API solution to open Notepad and Write your message to it...NOTE: If your running VBA 7.0 (Office 2010) then you'll have to add PtrSafe just after each Declare Statement...
At the top of your module paste the API Declarations and Global Variables
Write2Notepad function opens a new instance of Notepad and writes to it. If it succeeds, then it will return the Process ID of the Notepad instance.
Routine to Test the Write2Notepad Function
The above code might look like a lot of trouble or more complicated but it will likely work much more reliably and efficiently then any other method.
The below function will copy your message to the clipboard using the MS clip tool, open notepad, and then paste the clipboard contents (your message) into Notepad... This way you don't have to save anything to a file and its easily closed... Or you can save it if you choose.
You can also add an additional routine to 'Sleep' while notepad is open to halt code if you need... See Below
EDIT:I just realized that I wrote the above code to work for VBScript... Since this is Excel, if you want to look into other methods to copy contents to the Clipboard without using the WshShell.Exec method; you can also try:
To use the DataObject in your code, you must set a reference to the Microsoft Forms 2.0 Object Library. This can also be done by creating a UserForm and then Deleting it... The reference will remain (Excel 2007).
For additional Clipboard API's and code take a look at:
1) http://www.cpearson.com/excel/Clipboard.aspx2) http://msdn.microsoft.com/en-us/library/office/ff192913.aspx3) http://msdn.microsoft.com/en-us/library/windows/desktop/ms648709%28v=vs.85%29.aspx
There are other possible methods but I think these are the most stable and reliable. I will leave the code the way it is so that it will work for both VBA and VBScript
Message Box Instructables
Use a TextBox. I know ActiveX TextBoxes can even be assigned scrollbars.
Gary's StudentGary's Student