message without stopping execution?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,
Is there any method to display a message during a lengthy process to inform
the user on progress of the process without stopping execution as MsgBox
always does?
Stefi
 
Is the problem that you don't want the code interrupted, or that you want
the MsgBox timed, and not depend upon a user responding. If the latter, use

Dim cTime As Long
Dim WSH As Object


Set WSH = CreateObject("WScript.Shell")
cTime = 10 ' 10 secs
Select Case WSH.Popup("Open an Excel file?!", cTime, "Question", _
vbOKCancel)
Case vbOK
MsgBox "You clicked OK"
Case vbCancel
MsgBox "You clicked Cancel"
Case -1
MsgBox "Timed out"
Case Else
End Select
 
Would it be feasable to show the progess on a new sheet and periodically
switch back on the screen updating so that the user can see how the process
is updating.
Or can you use the Application.statusbar = "Message" to keep the user
informed?

Chris
 
The latter was the case, and you gave me a good solution, thank you!
Nevertheless, Chris's suggestion Application.statusbar = "Message" is
simpler and sufficient in my case.
Regards,
Stefi


„Bob Phillips†ezt írta:
 
Dear Matt,

I just now reviewed the topics I was interested in. I hope you come back to
this topic, although quite a long time elapsed since your reply to my
question above (sending e-mail directly to your address failed).
I have found earlier http://www.cpearson.com/excel/Progress.htm page, I made
a test, and I found it not too convenient to use, but I missed the reference
to the "LED" type progress bar you attracted my attention to.
I just now downloaded and tested it (very nice), but I have to ask your help
to clearly understand your messages. You must forgive me for disturbing you
with such things, but my mother tongue is not English (in fact it's
Hungarian), and I don't know the meaning of words "funky" and "geeky", would
you give me some explanation or synonims (I didn't found these words even in
the largest English-Hungarian dictionary). Also I couldn't find out the
meaning of "class j" back link.

Regards,
Stefi

"MattShoreson†ezt írta:
 
geeky - generally refers to someone who is not very athletic and is very
good with technical things such as computers. Bill Gates might be
considered geeky

funky - refers to something or someone that is popular because that
something or someone exhibits behavior or appearance that enjoys popular
acclaim. This is often associated with music and musicians. The term is
somewhat dated.

In the context:
"the LED class j back link on page is pretty funky in a geeky type of way."

Matt means the use of the LED is pretty impressive if you are impressed by
techical approaches/implementation.
 
Thanks, Tom, I never thought that I can get such precise answers to
linguistic questions via an Excel forum!
However! I tested the LED type Progressbar demo, and it worked well, but
when I tried to transplant it into my own code, it did nothing. I got no
error messages, the code run smoothly, but nothing appeared in the status
bar. I copied the class module into my workbook, applied the
Set sb = New clsProgressBar ' create a new progress bar
sb.Show "Please wait", vbNullString, 0 ' display the progress bar
....
for
sb.PercentComplete = i ' update the progress bar
next
....
Set sb = Nothing ' remove the progress bar

lines in my code, what can be wrong?

Do you get a warning if a new message comes to an old topic?

Regards,
Stefi

„Tom Ogilvy†ezt írta:
 
Tom,

'Funky' was used in more of a retro sense (I dont feel it is dated.)
I could of used 'book' - but much preferred funky in this instance.

NB: it also means smelly.

Cheers,
Matt
 
Stefi,

I have transplanted the progress bar cls, the simple progress bar an
the wait seconds procedure to a new workbook.

It works fine. I would suggest that maybe something is conflictin
with the display status bar.

Is it possible to post some of the code
 
Here is a simpler implementation posted by Michael Pierron

Posted by Michael Pierron (May 28, 2004)
Microsoft.public.excel.programming

Private Declare Function FindWindow& Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName$, ByVal lpWindowName$)
Private Declare Function CreateWindowEX& Lib "user32" Alias _
"CreateWindowExA" (ByVal dwExStyle&, ByVal lpClassName$ _
, ByVal lpWindowName$, ByVal dwStyle&, ByVal x&, ByVal y& _
, ByVal nWidth&, ByVal nHeight&, ByVal hWndParent& _
, ByVal hMenu&, ByVal hInstance&, lpParam As Any)
Private Declare Function DestroyWindow& Lib "user32" (ByVal hWnd&)
Private Declare Function SendMessage& Lib "user32" Alias _
"SendMessageA" (ByVal hWnd&, ByVal wMsg&, ByVal wParam&, lParam As Any)
Private Declare Function GetClientRect& Lib "user32" _
(ByVal hWnd&, lpRect As RECT)
Private Declare Function FindWindowEx& Lib "user32" Alias _
"FindWindowExA" (ByVal hWnd1&, ByVal hWnd2&, ByVal lpsz1$, ByVal lpsz2$)

Private Type RECT
cl As Long
ct As Long
cr As Long
cb As Long
End Type

Sub PBarDraw()
Dim BarState As Boolean
Dim hWnd&, pbhWnd&, y&, h&, i&, R As RECT
hWnd = FindWindow(vbNullString, Application.Caption)
hWnd = FindWindowEx(hWnd, ByVal 0&, "EXCEL4", vbNullString)
GetClientRect hWnd, R
h = (R.cb - R.ct) - 6: y = R.ct + 3
pbhWnd = CreateWindowEX(0, "msctls_progress32", "" _
, &H50000000, 35, y, 185, h, hWnd, 0&, 0&, 0&)
SendMessage pbhWnd, &H409, 0, ByVal RGB(0, 0, 125)
BarState = Application.DisplayStatusBar
Application.DisplayStatusBar = True
For i = 1 To 50000
DoEvents
Application.StatusBar = Format(i / 50000, "0%")
SendMessage pbhWnd, &H402, Val(Application.StatusBar), 0
Next i
DestroyWindow pbhWnd
Application.StatusBar = False
Application.DisplayStatusBar = BarState
End Sub
 
Dear Matt,
I also tried the method in a new workbook and it worked. Obviously there is
something in my code conflicting with the display of the status bar, but I
couldn't find out what's that. Of course I would post the code, but its too
large to paste it here, in fact it consists of a workbook containing the
macros and some other workbooks to be processed that are required to run the
macros. Tell me, please, where and what to send you! I tried to send you an
e-mail to the address found in your profile
([email protected]) but it was
rejected.

Regards,
Stefi


„MattShoreson†ezt írta:
 
Hi Matt,
I also tried the method in a new workbook and it worked. My code is too long
to copy it here, please tell me where to send it! I tried to send you an
e-mail to the address found in your profile
([email protected]) but it was
rejected.

Regards,
Stefi


„MattShoreson†ezt írta:
 
Matt,
Unfortunately e-mails sent to the address in your profile still come back
with error message:

550 5.1.2 <[email protected]>...
Host unknown (Name server: excelforum-nospam.com: host not found)

I hope you got the message sent to (e-mail address removed)

Regards,
Stefi


„MattShoreson†ezt írta:
 
Hi Tom,
This works in the environment in which CPearsons' solution doesn't, god
knows why.
Thanks,
Stefi


„Tom Ogilvy†ezt írta:
 
Check your email I have replied to your mail account.

LED progress bar struggles when being initiated from a userform. It'
fine when launuched from the worksheet
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using a modeless form to show status 2
Showing a progress message 2
Can I get a popup MsgBox without stopping/pausing VBA program? 2
Info Message without a response needed 2
How do I display a (non-modal) progress message? 4
Changed or Updated property? 4
Stop Command Question 4
SaveAs without closing original workbook 6

Back
Top