I have a button on a user form that when clicked it saves the file. Is it possible, while the file is in the process of saving, to have message box pop up that says "Please wait, saving file."? If so, how?
Printable View
I have a button on a user form that when clicked it saves the file. Is it possible, while the file is in the process of saving, to have message box pop up that says "Please wait, saving file."? If so, how?
Well, I would suggest that you don't use a message box because it will stop any process that is going on once that message box opens and until the user clicks ok..
What I would suggest is that at the beginning of your code which saves the file, show a small form that is vbModal which has a label on it that says "Please wait, saving file...". Then, at the end of the code which saves your file, close the form. That way, the small form will pop up at the start of the file save and close at the end of the file save...
Hope that helps..
Dan
I am a beginner. Could you give me some sample code.
Thank YOu
You could also use a progressbar to show the progress (!) while saving the file.
Dave.
Could you tell me how to do that??? I am using VBA
Gl,Code:'add another form to the project (Project/Form)
'Then under the command1_click() place this
Public Sub Command1_Click()
Form2.Show (VbModal)
'Do your opening sequence here
Unload Form2
End Sub
D!m
It's coming up with an error:
Ambiguous Name Detected: btnSave
Under that save button I have a private sub running some code too. That is probably why...What can I do?
Ok. I am confused. Could someone explain this to me. I must not be following this guy right. All I want to know is if I can have some kind of box or message come up while my file is saving like, "Please wait, saving file...". How can I do that. I am a huge newbee so could you explain in detail?
Why not just call your saving routine?
Code:Public Sub Command1_Click()
Form2.Show 1
Call MySavingfunction
Unload Form2
End Sub
Ok. I tried that and it came up with this error:
Wrong number of arguments or invalid property assignment.
This is my code:
Is that wrong?Code:Private Sub btnSave_Click()
frmProgress.Show 1
Call btnSaveEmp
Unload frmProgress
End Sub
Ok. When the save button is clicked it goes through the userform and checks to see if any thing is blank. If there is, a message box comes up and tells them to correct it. All of this is in the same sub. I just want the box to come up when the file is saving. Take a look at my code. Maybe you will understand better.
What this does is looks for errors in the form, asks you if you have a folder to save it in, if not, it creates a folder, and saves the form. Where can I put the code to have the box appear while the file is saving?Code:Sub btnSaveEmp()
Calculate
Sheets("List Feeds - Control Sources").Visible = True
Sheets("List Feeds - Control Sources").Select
Range("AK2").Select
Selection.copy
Sheets("List Feeds - Control Sources").Visible = False
Sheets("Referral Form").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Calculate
frmReferral.cbxEmployeeName.Enabled = False
frmReferral.ccbxSeries6.Enabled = False
frmReferral.ccbxSeries7.Enabled = False
frmReferral.ccbxInsurance.Enabled = False
frmReferral.ccbxNone.Enabled = False
frmReferral.ctxtFirstName.Enabled = False
frmReferral.ctxtLastName.Enabled = False
frmReferral.ctxtHomePhone.Enabled = False
frmReferral.ctxtWorkPhone.Enabled = False
frmReferral.ctxtBestCall.Enabled = False
frmReferral.ctxtComments.Enabled = False
frmReferral.cbxReferredTo.Enabled = False
frmReferral.ctxtReferralDate.Enabled = False
frmReferral.ccbxQualifiedYes.Enabled = False
frmReferral.ccbxQualifiedNo.Enabled = False
frmReferral.ccbxSaleNo.Enabled = False
frmReferral.ccbxSaleYes.Enabled = False
frmReferral.ccbxMutualFund.Enabled = False
frmReferral.ccbxVariableAnnuity.Enabled = False
frmReferral.ccbxFixedLife.Enabled = False
frmReferral.ccbxOther.Enabled = False
frmReferral.ctxtRepCode.Enabled = False
frmReferral.ctxtPosition.Enabled = False
frmReferral.ctxtAppointment.Enabled = False
frmReferral.ctxtOther.Enabled = False
frmReferral.ctxtCustomerAccount.Enabled = False
frmReferral.ctxtCustomerSocial.Enabled = False
'Employee Name Box Blank
If Sheets("List Feeds - Control Sources").Range("I2").Value = "" Then
MsgBox "Please enter your name in the Employee Name Box."
frmReferral.cbxEmployeeName.Enabled = True
Exit Sub
End If
If Sheets("List Feeds - Control Sources").Range("K2").Value = False And Sheets("List Feeds - Control Sources").Range("L2").Value = False And Sheets("List Feeds - Control Sources").Range("M2").Value = False And Sheets("List Feeds - Control Sources").Range("N2").Value = False Then
'Licensing Blank
MsgBox "Please mark your licensing."
frmReferral.ccbxSeries6.Enabled = True
frmReferral.ccbxSeries7.Enabled = True
frmReferral.ccbxInsurance.Enabled = True
frmReferral.ccbxNone.Enabled = True
frmReferral.ccbxSeries6.SetFocus
Exit Sub
End If
If IsEmpty(Sheets("List Feeds - Control Sources").Range("O2").Value) Then
'Customer First Name Blank
MsgBox "Please enter the customer's first name."
frmReferral.ctxtFirstName.Enabled = True
frmReferral.ctxtFirstName.SetFocus
Exit Sub
End If
If IsEmpty(Sheets("List Feeds - Control Sources").Range("P2").Value) Then
'Customer Last Name Blank
MsgBox "Please enter the customer's last name."
frmReferral.ctxtLastName.Enabled = True
frmReferral.ctxtLastName.SetFocus
Exit Sub
End If
If IsEmpty(Sheets("List Feeds - Control Sources").Range("Q2").Value) And IsEmpty(Sheets("List Feeds - Control Sources").Range("R2").Value) Then
'Customer Home/Work Number Blank
MsgBox "Please enter either the customer's home or work phone number."
frmReferral.ctxtHomePhone.Enabled = True
frmReferral.ctxtWorkPhone.Enabled = True
frmReferral.ctxtHomePhone.SetFocus
Exit Sub
End If
If Sheets("List Feeds - Control Sources").Range("U2").Value = "" Then
'PIO/Licensed Rep box blank.
MsgBox "Please enter the PIO / Licensed Representative's name in the box."
frmReferral.cbxReferredTo.Enabled = True
frmReferral.cbxReferredTo.SetFocus
Exit Sub
End If
If IsEmpty(Sheets("List Feeds - Control Sources").Range("V2").Value) Then
'Referral Date is blank
MsgBox "Please enter the Date of Referral"
frmReferral.ctxtReferralDate.Enabled = True
frmReferral.ctxtReferralDate.SetFocus
Exit Sub
End If
'Beginning of save code
Dim ctrlno As String
Dim filename As String
On Error Resume Next
ctrlno = TextBox1.Text
MsgboxResult = MsgBox("Do you already have a referral folder created on your computer?", vbYesNo, "Retail Investment Sales Referral Form")
If MsgboxResult = vbYes Then
existingfolder = InputBox("Please enter the name of the folder in which your referral files are contained:", "Retail Investment Sales Referral Form")
If existingfolder = "" Then
Exit Sub
End If
existingfoldercheck = "c:\Windows\Desktop\" & existingfolder
If Dir(existingfoldercheck, vbDirectory) <> "" Then
filename = existingfoldercheck & "\" & ctrlno & ".xls"
If Dir(filename, vbNormal) = filename Then
MsgBox "This referral already exists, please check the number and try again."
Exit Sub
Else
ActiveWorkbook.SaveAs filename:= _
filename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End If
Else
Response2 = MsgBox("The folder you entered does not exist. Would you like to create it?", vbYesNo, "Retail Investment Sales Referral Form")
If Response2 = vbYes Then
NewDir = existingfoldercheck
'ChDir "C:\"
MkDir (NewDir)
NewDirMsg = "Your referrals will be saved in:" & NewDir
MsgBox NewDirMsg
filename = NewDir & "\" & ctrlno & ".xls"
ActiveWorkbook.SaveAs filename:= _
filename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End If
End If
Else
'If MsgboxResult = vbNo Then
Dim InputBoxResult As String
InputBoxResult = InputBox("Please enter a name for the folder to which you will be saving your referrals in:")
If InputBoxResult = "" Then
Exit Sub
End If
existingfoldercheck2 = "c:\Windows\Desktop\" & InputBoxResult
If Dir(existingfoldercheck2, vbDirectory) <> "" Then
Exit Sub
Else
NewDir = "C:\Windows\Desktop\" & InputBoxResult
'ChDir "C:\"
MkDir (NewDir)
NewDirMsg = "Your referrals will be saved in:" & NewDir
MsgBox NewDirMsg
filename = NewDir & "\" & ctrlno & ".xls"
ActiveWorkbook.SaveAs filename:= _
filename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End If
End If
'End of Save Code
MsgBox "Your Referral Is Complete. Thank You!!!!Please e-mail the form to David Gottlieb and CC the PIO or Licensed Rep. you referred the customer to."
Dim msg, Style, Title, Response, MyString
msg = "Would You Like To Print?" 'Define Message.
Style = vbYesNo
Title = "Retail Investment Sales Referral Form" 'Define Title.
Response = MsgBox(msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" '
Range("A2:R76").Select
Selection.Interior.ColorIndex = 2
Range("A7:N7").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
MsgBox "Thank You!!! Now that you are finished, hit the Send button. This will send the email to the the PIO/Licensed Rep. and David Gottlieb."
ActiveCell.Offset(12, 7).Range("A1").Select
Range("A2:R76").Select
Selection.Interior.ColorIndex = 33
Range("C14").Select
Else 'User chose No.
MyString = "No" '
MsgBox "You are finished!! Hit the Send button. This will e-mail the form to David Gottlieb and the PIO/Licensed Rep. you referred the customer to."
End If
Calculate
frmReferral.btnSend.Enabled = True
End Sub
Thanks Guys
Megatron? Somebody? Please help me?? Please?? Just tell me where I can put that code with in the code above. Pleaes?
I believe the following is what you want:
Insert the following code:
right before your code which reads:Code:frmFileSaving.Show vbModal
You have it twice, so put it in both places..Code:ActiveWorkbook.SaveAs filename:= _
Then, insert the following code:
Code:Unload frmFileSaving
right after your code which reads:
You would preferably want to make the pop up form without a border..Code:, CreateBackup:=False
Did that help you? By the way, does it really take that long to save the file? Most files saves are pretty quick and therefore do not need any notification to the user.. Just wondering... Let me know if the above code didn't work and what error messages you get..
Dan
You can't show the form modal!
Because if you do your code will stop running until the form is unloaded by the user.
You could show the form modeless though.ยด
But that depends on the version of Excel you use.
It works perfectly in Excel 2000 but it will raise an error in Excel 9x because in Office 9x all UserForms are showed modal by default.
To show a form modeless in Excel 2000 do the following:
But as I understand your code your frmReferral form is still visible when you do the save.Code:UserForm1.Show vbModeless
If that is the case you could add a picturebox or a frame to the form.
Then add a label to that picturebox or frame and set its caption to whatever you like.
Set the Visible property to false for the frame and just show it before the actual save.
Best regards
I'm not sure I know what you mean. Could you explain a little better? Thanks.
Add a PictureBox on your frmReferral Form that is sized to fit the whole form.
Make it so it's on top of all other controls so when you set it to visible the user can only see the picturebox and not any other controls.
Then add a label with the message you want to show the user to the PictureBox.
Now set the Visible property of the picturebox to False as default.
Change the Visible property right before you do the save (also call DoEvents so the form has time to update).
Change it again when the save is done.
Now your form also works as a kind of a msgbox (whitout any buttons).
I hope that clearified things for you. If not drop me an e-mail.
Maybe you can have this...
when you click on the save....
(1) show a form that will stay always at the top of others.... that is code for that... so, you don't need to use vbmodel... and put the message on the always top form..
(2) then save your file....
(3) after saving, hide the always top form..
Hope will do, by the way, what program you use? excel 2000?
Unfortunately I used Excel 97. It works ok it just has alot of bugs. Is that the only way to do it? Is there not a way to have a small little box come up to say that it's saving?
Thw way i show you CAN be used in excel97 also, just have it always on top of other.. you can make LOOKS like a message box... because it is not using vbmodel so, you don't worry about it stops your progress... you can have a text box on teh small form, and have OK on it... you know, just make it LOOKS like message box...
Hope it will help
But see I tried that Friday and it won't start saving until you close the box.
Well, if you put the actuall save code in a different UserForm that same form could show the message do the save and unload it self.
Ok. I tried that and it won't save. The box comes up but it won't save. Look at my code: Where it says frmProgress.Show is where the box is supposed to work.
Then in my frmProgress action I have this:Code:'Beginning of save code
Dim ctrlno As String
Dim filename As String
On Error Resume Next
ctrlno = TextBox1.Text
MsgboxResult = MsgBox("Do you already have a referral folder created on your computer?", vbYesNo, "Retail Investment Sales Referral Form")
If MsgboxResult = vbYes Then
existingfolder = InputBox("Please enter the name of the folder in which your referral files are contained:", "Retail Investment Sales Referral Form")
If existingfolder = "" Then
Exit Sub
End If
existingfoldercheck = "c:\Windows\Desktop\" & existingfolder
If Dir(existingfoldercheck, vbDirectory) <> "" Then
filename = existingfoldercheck & "\" & ctrlno & ".xls"
If Dir(filename, vbNormal) = filename Then
MsgBox "This referral already exists, please check the number and try again."
Exit Sub
Else
'ActiveWorkbook.SaveAs filename:= _
'filename, FileFormat:= _
'xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
', CreateBackup:=False
frmProgress.Show
End If
Else
Response2 = MsgBox("The folder you entered does not exist. Would you like to create it?", vbYesNo, "Retail Investment Sales Referral Form")
If Response2 = vbYes Then
NewDir = existingfoldercheck
'ChDir "C:\"
MkDir (NewDir)
NewDirMsg = "Your referrals will be saved in:" & NewDir
MsgBox NewDirMsg
filename = NewDir & "\" & ctrlno & ".xls"
frmProgress.Show
'ActiveWorkbook.SaveAs filename:= _
' filename, FileFormat:= _
' xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
' , CreateBackup:=False
End If
End If
Else
'If MsgboxResult = vbNo Then
Dim InputBoxResult As String
InputBoxResult = InputBox("Please enter a name for the folder to which you will be saving your referrals in:")
If InputBoxResult = "" Then
Exit Sub
End If
existingfoldercheck2 = "c:\Windows\Desktop\" & InputBoxResult
If Dir(existingfoldercheck2, vbDirectory) <> "" Then
Exit Sub
Else
NewDir = "C:\Windows\Desktop\" & InputBoxResult
'ChDir "C:\"
MkDir (NewDir)
NewDirMsg = "Your referrals will be saved in:" & NewDir
MsgBox NewDirMsg
filename = NewDir & "\" & ctrlno & ".xls"
frmProgress.Show
' ActiveWorkbook.SaveAs filename:= _
' filename, FileFormat:= _
' xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
' , CreateBackup:=False
End If
End If
'End of Save Code
What am I doing wrong?Code:Private Sub frmProgress_Activate()
ActiveWorkbook.SaveAs filename:= _
filename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
The problem is that the file name isn't passed to the frmProgress form.
One solution would be to add your own procedure to the progress form.
Add the following code to frmProgress
And before you call frmProgress.Show you add the following line:Code:Private m_sFileName As String
Public Property Let FileName(sNew As String)
m_sFileName = sNew
End Property
Private Sub frmProgress_Activate()
ActiveWorkbook.SaveAs _
filename:=m_sFileName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub
Good luck!Code:frmProgress.FileName = filename