Using a UserForm instead of a msgbox

Using Windows 7 Home (64bit) and Office 365 (2013) 32bit

I am trying to use a UserForm as a msgbox. To see why see posts here, and here,

The first problem I have bumped into is working out the width of the text box needed to contain a given string. And then the width of the form.

It struck me that this must have been done before - but Google hasn't come up with a solution for me yet.

Any pointers would be appreciated.
Roy
RzzBAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Roy,

I attached a zip with an accdb to the other thread that you can use instead ...

http://www.experts-exchange.com/questions/28862739/How-to-ge-MsgBoxes-to-appear-in-the-centre-of-a-UserForm-2.html

warm regards,
crystal

~ have an awesome day ~
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
the width of the MsgBox is now determined by the message ... which sometimes leaves the title truncated! To fix this, you can pad the message with no-break spaces.
RzzBAuthor Commented:
OK - many thanks - I will take a look at this tomorrow...
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Roy ~
Martin LissOlder than dirtCommented:
This workbook contains a userform that automatically adjusts to the size of the message. It has two added properties:
1.    Title: If used, sets the form's Caption to the desired value. If not used the caption defaults to "Microsoft Excel"
2.    Message: The message to be displayed
.

Run Sheet1's "test" sub to test.
28864799.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
The best collection of progress meters in my opinion are Andy Popes

Progress Meters
RzzBAuthor Commented:
Apologies guys for not looking at this yet.
Not enough hours in the day - tomorrow hopefully.
RzzBAuthor Commented:
Martin - I embedded your code into my Test Workbook and it works fine... attached... Second UserForm Length.xlsm

My next problem is to position the second user form in the middle of the first userform.  I tried positioning the second userform in the same position as the first userform by using UserForm1.Top and UserForm1.Left. But it didn't work - what am I doing wrong?
The test case is attached - Second UserForm PositionTest.xlsm

Roy - The Progress Meters look very interesting but I'm not sure how they help! Did you post to the wrong thread? :-)

Crystal - please see response in http://www.experts-exchange.com/questions/28862739/How-to-ge-MsgBoxes-to-appear-in-the-centre-of-a-UserForm-2.html

Many thanks for your help.
Roy
Second-UserForm-Length.xlsm
Second-UserForm-PositionTest.xlsm
Martin LissOlder than dirtCommented:
This is hard to test because when I click close form the workbook closes and when I try to reopen it I'm told it's locked for editing.
RzzBAuthor Commented:
Martin,

Once you have it open you can click on "show application"  then F11 to the code. Run without any other WorkBooks open. Take a look in you Task Manager and make sure there is not an instance of Excel Running  erroneously.

Roy
Martin LissOlder than dirtCommented:
OK, then...?
Martin LissOlder than dirtCommented:
BTW I don't know if it's apropos of anything but here's apiece of code that have for centering a userform.

'
Set the StartUpPosition to 0 first

'In Application
.Left = (Application.Width - fmSingle.Width) / 2

'In a range of columns (visibly contiguous)

    intWidth = Columns("A").EntireColumn.Width _
             + Columns("C").EntireColumn.Width _
             + Columns("E").EntireColumn.Width _
             + Columns("F").EntireColumn.Width _
             + Columns("G").EntireColumn.Width _
             + Columns("H").EntireColumn.Width _
             + Columns("J").EntireColumn.Width _
             + Columns("M").EntireColumn.Width _
             + Columns("N").EntireColumn.Width _
             + Columns("T").EntireColumn.Width
userform.Left = Application.Left + (intWidth - userform.Width) / 2

Open in new window

RzzBAuthor Commented:
OK - I will do a short video on running the test case tomorrow. It's 11:42pm here at the moment and I'm in bed on my tablet! :-)
A video should make it clearer.
RzzBAuthor Commented:
Ok Ok !!! After much reading and experimentation I have solved the mystery!

The key was to set the StartUpPosition property on the MsgBox form to Manual. I had it set to CenterOwner - I had completely missed this property - never used it before!

I can now work out where to start my second form in relation to to the first form with a bit of addition, subtraction and division!

Many thanks to all for your assistance.
Roy
Martin LissOlder than dirtCommented:
Please don't forget to accept any answer(s) that helped you.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you including these two new ones.
An Experts Exchange Shortcut for the Truly Lazy (for Apple OS X)
A Guide to Writing Understandable and Maintainable VBA Code
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.