Can I have a temporary message window appear during code execution

[robs3131]

Hi,

I did some searching through prior posts at this site and also looked
through Help in VBA within Excel but wasn't able to find what I need - which
is I'm looking to have a message window popup when a button is clicked on
that is then brought down by the code just after the code execution
completes. Below is what I was hoping to build on -- thanks in advance for
any help you can provide!

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False

MsgBox "Please wait while code executes. This message will " & vbNewLine & _
"automatically close when execution has completed."

Rows("1:1").RowHeight = 60

With Sheets("Transaction Summary")
If .FilterMode = True Then
.ShowAllData
Else
End If
End With

With Sheets("Open Transactions by Member ID")
If .FilterMode = True Then
.ShowAllData
Else
End If
End With

With Sheets("Member ID Report Master")
If Len(.Range("D2")) <> 0 Then
Module1.closedtrans1
Module2.clearmemidtrans
Else
MsgBox "Sales (Member ID Report) transaction data has not yet been
submitted -" & vbNewLine & _
"Sales data must be first submitted prior to requesting to see open"
& vbNewLine & _
"transactions."
End If
End With

With Sheets("Transaction Summary")
If .FilterMode <> True Then
.Rows("1:1").AutoFilter
Else
End If
End With

With Sheets("Open Transactions by Member ID")
If .FilterMode <> True Then
.Rows("1:1").AutoFilter
Else
End If
End With

Application.ScreenUpdating = True

 

[NickHK]

MsgBox is modal in nature, so will stop execution until dismissed. Use the
StatusBar or design a userform that you can update with a message and unload
when the routine finishes.

NickHK

 

[robs3131]

Thanks Nick.

I figured out how to change the Status Bar, the only issue is that the text
it's too small -- the user may not notice the text.

I also started putting together a userform, but I'm getting stuck on a
couple of items:

1 - I put a text box in the form -- for some reason, even though I set
"WordWrap" to "True", the text does not wrap (I also tried setting the
"Enabled" property of the text box to both "True" and "False", and the
"Enabled property of the Form to "True" and "False" -- I couldn't get to work
for any combination.

2 - The form comes up fine when I call it in my macro, but the form requires
the user to click on the red 'x' for the code to continue. I have code that
hides the form at the end of the macro and that seems to work...it's just the
part of requiring the user to click on the x that I want to automate. Any
ideas? My code is below


Private Sub CommandButton2_Click()

Application.ScreenUpdating = False

With dataprocess 'this is the name of my form
.Show
End With

.........code............

With dataprocess
.Hide
End With

Application.ScreenUpdating = True

End Sub


Thanks!

Robert

 

[Gary Keramidas]

i have a userform i display while a report is being processed
label1 is changed depending on which report is being run



sub report()
'dim statements and anything else needed before the report is created


With UserForm10
.Show vbModeless
.Caption = "Fresh Production Data Report"
.Label1 = "Creating Report By Box .........Please Wait"
.BackColor = &H800000
.Label1.ForeColor = &HFFCC99
End With
DoEvents

'create the report

Unload UserForm10

end sub

 

[Chip Pearson]

You might find my Alerter DLL useful. It allows you to display a message
notification window modelessly. It is much more visible to the user than the
status bar and is not modal like MsgBox is.

http://www.cpearson.com/excel/alert.htm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[robs3131]

Thanks Gary and Chip!

Chip - I went with Gary's solution as it does not require that I download
software into Excel. I assume that any other user of the spreadsheet would
need to also download the software you suggested, is that right?

Thanks!

Robert