Message Box?

[davidgottlieb]

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?

[softwareguy74]

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

[davidgottlieb]

I am a beginner. Could you give me some sample code.

Thank YOu

[Harmough]

You could also use a progressbar to show the progress (!) while saving the file.

Dave.

[davidgottlieb]

Could you tell me how to do that??? I am using VBA

[Dim]

'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

Gl,
D!m

[davidgottlieb]

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?

[davidgottlieb]

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?

Public Sub Command1_Click()
    Form2.Show 1
    Call MySavingfunction
    Unload Form2
End Sub

[davidgottlieb]

Ok. I tried that and it came up with this error:

Wrong number of arguments or invalid property assignment.

This is my code:

Private Sub btnSave_Click()
frmProgress.Show 1
Call btnSaveEmp
Unload frmProgress
End Sub

Is that wrong?

[davidgottlieb]

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.

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

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?

Thanks Guys

[davidgottlieb]

Megatron? Somebody? Please help me?? Please?? Just tell me where I can put that code with in the code above. Pleaes?

[softwareguy74]

I believe the following is what you want:

Insert the following code:

frmFileSaving.Show vbModal

right before your code which reads:

   ActiveWorkbook.SaveAs filename:= _

You have it twice, so put it in both places..

Then, insert the following code:

Unload frmFileSaving

right after your code which reads:

   , CreateBackup:=False

You would preferably want to make the pop up form without a border..

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

[Joacim Andersson]

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:

UserForm1.Show vbModeless

But as I understand your code your frmReferral form is still visible when you do the save.
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

[davidgottlieb]

I'm not sure I know what you mean. Could you explain a little better? Thanks.

[Joacim Andersson]

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?

[davidgottlieb]

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

[davidgottlieb]

But see I tried that Friday and it won't start saving until you close the box.

[Joacim Andersson]

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.

[davidgottlieb]

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.

'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

Then in my frmProgress action I have this:

Private Sub frmProgress_Activate()
            ActiveWorkbook.SaveAs filename:= _
            filename, FileFormat:= _
            xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
            , CreateBackup:=False

End Sub

What am I doing wrong?

[Joacim Andersson]

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

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

And before you call frmProgress.Show you add the following line:

frmProgress.FileName = filename

Good luck!