MsgBox without buttons

[Guest]

As my macro begins to execute, I need to display a pop-up window that simply
says "One moment please..." When the macro is near the end of its execution,
I then need to remove the pop-up window.

Unless there is a better way to accomplish this, I was thinking of using a
MsgBox, except I don't want (or need) the MsgBox to contain any buttons. But
I don't know how to do this.

Any help would be greatly appreciated. Thanks.

 

[Guest]

No can do on the message box... My preference for this kind of thing is to
have a sheet with "Processing... One moment please." in big bold letters.
Switch to this sheet at the beginning of your macro and back to your original
sheet at the end. Doing this you can get fancy with the colours, add any
extra text you might want and even add some check boxes that get checked as
specific sections of the macro complete to give the user the feeling that
something productive is going on...

 

[Guest]

I don't know of any MsgBox way that has no buttons or won't awat closing
before proceeding. One way developers usually handle it is to create a
UserForm as the popup dialog. The VBA code shows the userform. The
userform_Activate code calls further code to run. When the code is done, it
Unloads the form.

My preference is to use the StatusBar due to it's non-invasiveness. One of
the downsides to using a form is it can get in the way of stepping through
your code because of its modality.

 

[Guest]

Jim,
Thanks for the suggestion. I was really hoping to be able to display some
sort of a pop-up box, but your method is one that I didn't think of.
Thanks again,
Bob

 

[John in Wembley]

As my macro begins to execute, I need to display a pop-up window that simply

just make a small form, close it after a few seconds , I do this as
splash screen on the opening of some of my sheets...

 

[stevebriz]

Another way is to put a message in the applications status bar


eg:

  Application.StatusBar = "Please wait while performing ..."

 

[Guest]

John,
Thanks for the suggestion! I will give it a try.
Bob

 

[Guest]

Thanks for the suggestion! I will give it a try.
Bob

 

[Gary Keramidas]

i make a userform with only a label on it, i center it and make it as wide as
the form.
then when my first macro starts i use something like this:

With UserForm1
.Caption = "Processing Scanner Data For " &
Format(Worksheets("Date").Range("B2"), "MMMM dd yyyy")
.Show
End With
DoEvents

then, when the 2nd macro i called:

UserForm1.Label1.Caption = "Adding Decipher Sheet Information"
DoEvents

when the 3rd macro is called:

UserForm1.Label1.Caption = "Summarizing Data By Line Number"
DoEvents


and when the code is done:

Unload UserForm1

 

[Gary Keramidas]

when i use a form to display status and need to step through the code, i just
type unload userform1 in the immediate window. most of my clients don't know
what the status bar is for, so they never look at it.

just my way of doing it.

 

[Guest]

Don't get me wrong, I like the userform idea because it's obvious and kind
of cool.

However, I had the very unpleasant task of inheriting a humongous project
from a developer who never got it to work.

There were tens of thousands of lines of code in it, and no code-commenting.

The developer tied *everything* into a modal userform with incrementing
status lines, emulating the look and feel of Windows.

Great, but first of all, the project didn't work. Worse, if I made the form
non-modal, or tried to disable it, NONE of the code would work. It was
completely dependent on the form.

I have never experienced a client that didn't know what a status bar was
for. Office apps use them all the time. I agree they are not as obvious as
forms, but there is certainly much less overhead in statusbars then
userforms.

Just my take. (and I am not inexperienced).

 

[Gary Keramidas]

yep, it's always worse with someone else's code.

 

[Guest]

Gary,

To practice, I created a single UserForm with only a Label in it. I left
the default colors for the UserForm and Label alone. But when I execute the
code to display the UserForm, all I see is a white box with a blue border and
a blue caption area. I don't see any text!

Any clue as to what I either did wrong or missed?

Thanks,
Bob

 

[Gary Keramidas]

post the code you have,

--


Gary

Gary,

To practice, I created a single UserForm with only a Label in it. I left
the default colors for the UserForm and Label alone. But when I execute the
code to display the UserForm, all I see is a white box with a blue border and
a blue caption area. I don't see any text!

Any clue as to what I either did wrong or missed?

Thanks,
Bob

 

[stevebriz]

Bob,

I have run into this before. It is due to the refresh of you screen.

The processor is busy looking after the code you are executing and the screen doesn't get refreshed



you can try using:

  Userform1.repaint

 

[ChrisP]

I'm having the same issue with the box popping up but only the boarder shows,
the middle is all white. Here is my code:

Private Sub Workbook_Open()

Sheets("Summary").Visible = xlSheetVisible

If ThisWorkbook.ReadOnly > True Then
Call ShowDialog
End If

Application.ScreenUpdating = False

Sheets("2008").Visible = xlSheetVisible
Sheets("Invoices").Visible = xlSheetVisible
Sheets("MasterList").Visible = xlSheetHidden
Sheets("Enable Macros").Visible = xlSheetHidden


Application.ScreenUpdating = True

End Sub
Sub ShowDialog()
Pleasewait.Show
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Enable Macros").Visible = xlSheetVisible
Sheets("Summary").Visible = xlSheetHidden
Sheets("2008").Visible = xlSheetHidden
Sheets("Invoices").Visible = xlSheetHidden

End Sub

For the userbox:
Private Sub UserForm_Activate()

Dim wbk As Workbook

Application.DisplayAlerts = False

Set wbk = Workbooks.Open(Filename:="MasterList.xls", _
ReadOnly:=True, UpdateLinks:=True, _
Password:="xxxxx")
With wbk
.Close
End With

Set wbk = Nothing

Pleasewait.Hide

End Sub

I created the userbox with "Please wait while actuals are being updated" in
the center, but that isn't showing.

Thanks,
Chris