Input Box/Message Box queries

[freekrill]

I have three queries about boxes which I hope that some one can help m
with.

1) Is it possible to write into code, so that a message box will appea
for 2 seconds, say, and then the macro will continue without the use
having to click "OK' in the message box?

2) Is it possible to have an input box, where the user clicks a "Yes
button or a "No" button and the macro proceeds accordingly?

3) Is it possible to change the font size of text within an input bo
or a message box?

Thanks for any help

fre

 

[Dave Peterson]

1. You could use this code from Jim Rech:

Sub SelfClosingMsgBox()
CreateObject("WScript.Shell").Popup "Hello", 2, _
"This closes itself in 2 seconds"
End Sub

2. yep, but use a msgbox.
dim resp as long
resp = msgbox(prompt:="ok or cancel",buttons:=vbokcancel)
if resp = vbok then
'ok
else
'cancel
end if

3. Not built into excel. Maybe you could build a UserForm and do all you want.

 

[Gord Dibben]

Dave

Tried the selfcloser code, but will not close unless acknowledged.

What simple thing am I missing?

I checked the Windows Script Host Object Model along with WSHControllerLibrary
in Tools>References.

Excel 2002 and 2003

Gord

 

[Dave Peterson]

This came up a few weeks/months ago. There was a poster who reported the same
results as you. (Well, I went to google to look and can't find anything--so
maybe I'm not remembering correctly.)

It worked for me in win98 and xl2002.

Ivan Moala posted a link to do it using scripting and API's in this thread:
http://groups.google.com/groups?threadm=0r57c.31314$wg.19972@okepread01

He pointed at:
http://www.xcelfiles.com/Excel03.html

===
And you shouldn't need those references as long as createobject() is used.

 

[Sandy Mann]

For what its worth, it works for me in XL97 but it displays for 5 - 6
seconds on the first run. I am used to macros not firing as quiclkly on
first use but the message box displays almost instantly and then stays on
screen too long. Subsequent runs work as advertised.

Regards

Sandy

 

[Sandy Mann]

Thinking about it some more perhaps I should have added more info. I was
using a Samsung laptop running Windows XP home. I will try it at work
tomorrow out of interest.

Regards

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

For what its worth, it works for me in XL97 but it displays for 5 - 6
seconds on the first run. I am used to macros not firing as quiclkly on
first use but the message box displays almost instantly and then stays on
screen too long. Subsequent runs work as advertised.

Regards

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

This came up a few weeks/months ago. There was a poster who reported

the

same
results as you. (Well, I went to google to look and can't find anything--so
maybe I'm not remembering correctly.)

It worked for me in win98 and xl2002.

Ivan Moala posted a link to do it using scripting and API's in this thread:

http://groups.google.com/groups?threadm=0r57c.31314$wg.19972@okepread01

He pointed at:
http://www.xcelfiles.com/Excel03.html

===
And you shouldn't need those references as long as createobject() is used.

all
you want. help

me

with.

1) Is it possible to write into code, so that a message box will appear
for 2 seconds, say, and then the macro will continue without the user
having to click "OK' in the message box?

2) Is it possible to have an input box, where the user clicks a "Yes"
button or a "No" button and the macro proceeds accordingly?

3) Is it possible to change the font size of text within an input box
or a message box?

Thanks for any help

free

 

[Dave Peterson]

One more thing to try.

After you paste the function into the code module, do this:
Debug|compile VBAProject

See if that helps, too.

(but 5 seconds is a lonnnnnnng time!)

I use win98, xl2002 on my 400mhz pc and it "feels" like about 2 seconds.

Thinking about it some more perhaps I should have added more info. I was
using a Samsung laptop running Windows XP home. I will try it at work
tomorrow out of interest.

Regards

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

For what its worth, it works for me in XL97 but it displays for 5 - 6
seconds on the first run. I am used to macros not firing as quiclkly on
first use but the message box displays almost instantly and then stays on
screen too long. Subsequent runs work as advertised.

Regards

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

This came up a few weeks/months ago. There was a poster who reported

the

same
results as you. (Well, I went to google to look and can't find anything--so
maybe I'm not remembering correctly.)

It worked for me in win98 and xl2002.

Ivan Moala posted a link to do it using scripting and API's in this thread:

http://groups.google.com/groups?threadm=0r57c.31314$wg.19972@okepread01

He pointed at:
http://www.xcelfiles.com/Excel03.html

===
And you shouldn't need those references as long as createobject() is used.

Gord Dibben wrote:

Dave

Tried the selfcloser code, but will not close unless acknowledged.

What simple thing am I missing?

I checked the Windows Script Host Object Model along with WSHControllerLibrary
in Tools>References.

Excel 2002 and 2003

Gord

wrote:

1. You could use this code from Jim Rech:

Sub SelfClosingMsgBox()
CreateObject("WScript.Shell").Popup "Hello", 2, _
"This closes itself in 2 seconds"
End Sub

2. yep, but use a msgbox.
dim resp as long
resp = msgbox(prompt:="ok or cancel",buttons:=vbokcancel)
if resp = vbok then
'ok
else
'cancel
end if

3. Not built into excel. Maybe you could build a UserForm and do

all
you want.

freekrill wrote:

I have three queries about boxes which I hope that some one can

help

me
with.

1) Is it possible to write into code, so that a message box will appear
for 2 seconds, say, and then the macro will continue without the user
having to click "OK' in the message box?

2) Is it possible to have an input box, where the user clicks a "Yes"
button or a "No" button and the macro proceeds accordingly?

3) Is it possible to change the font size of text within an input box
or a message box?

Thanks for any help

free

 

[Sandy Mann]

Yes it works as advertised at work on Win XP and XL 2002 - and now at home
on my laptop!

It may be related to a problem on my laptop that I have noticed.
Sometimes - but not everytime - when I start XL after opening Outlook
Express, XL itself takes a lonnnnnnng time to open.

Regards

Sandy

 

[Dave Peterson]

Don't forget to clean up your windows temp folder every so often.

 

[Gord Dibben]

Dave

Updated Windows Scripting Host(overwrote what I had with same version).

Still nada. MsgBox will not close unless acknowledged.

2002 and 2003 versions.

Not critical, just interesting.

Gord

 

[Dave Peterson]

How about the API version?

Dave

Updated Windows Scripting Host(overwrote what I had with same version).

Still nada. MsgBox will not close unless acknowledged.

2002 and 2003 versions.

Not critical, just interesting.

Gord

 

[Gord Dibben]

Not tried yet.

Will do soon and report back.

Gord