I'm In Trouble With Saving

[davidgottlieb]

Ok. I got this code from you guys and it works fine on my computer. But, when the form is used on someone elses computer it comes up with an error message. What I did was I inserted a CommonDialog box for the save as. I think it has something to do with the path that it is saving it in. Is there a way to specify the path??? Take a look at my code and tell me what you think.

Private Sub UserForm_Initialize()
CommonDialog1.Filter = "Excel Spreadsheet(*.xls)|*.xls|"
CommonDialog1.FilterIndex = 1
cbxEmployeeName.SetFocus
cbxEmployeeName.SelStart = O
cbxEmployeeName.SelLength = Len(cbxEmployeeName.Text)
End Sub

Private Sub CommandButton7_Click()
CommonDialog1.ShowSave
CommonDialog1.CancelError = False
If CommonDialog1.FileName <> "" Then
ActiveWorkbook.SaveAs FileName:=CommonDialog1.FileName
End If
End Sub

Tell me what you think and if there is anything I can do about this.Remember, I am using VBA.

[DrewDog_21]

two questions

1) what is the error and on what line does it occur, and

2) why do you have the .CancelError set to false? I always use the .CancelError as true and then put a Select Case routine in the error handler, and it never crashes.

[davidgottlieb]

Could you take a look at the form. I put you on my list at driveway.com to view the file. You should have received an email from them. I am now getting an error on someone elses computer that says:

Compile Error in hidden module: formOpen

What do I do??????

[DrewDog_21]

I can't find the button btnShowForm or the cell that I need
to click to open frmReferral. What cell is it in? And it
is on Sheet 1 (Referral Form), right?

Also, some constructive criticism here - you really need to
organize that code better because it is somewhat hard to
read. I don't have much experience in VBA, but if it
supports With - End With statements then you should use
those. Also you should separate your different If
statements with spaces and format them with tabs, so that this:

If Range("O42").Value = True And Range("O44").Value = False And Range("P44").Value = False And Range("O45").Value = False And Range("P45").Value = False Then
MsgBox "Please mark the product you sold to the customer."
frmReferral.ccbxMutualFund.Enabled = True
frmReferral.ccbxVariableAnnuity.Enabled = True
frmReferral.ccbxFixedLife.Enabled = True
frmReferral.ccbxOther.Enabled = True
frmReferral.ctxtOther.Enabled = True
frmReferral.ccbxMutualFund.SetFocus
Exit Sub
End If
If Range("O42").Value = True And IsEmpty(Range("C46").Value) Then
MsgBox "Please enter the customer's account number in the specified area."
frmReferral.ctxtCustomerAccount.Enabled = True
frmReferral.ctxtCustomerAccount.SetFocus
Exit Sub
End If

would actually look like this:

If Range("O42").Value = True And Range("O44").Value = False And Range("P44").Value = False And Range("O45").Value = False And Range("P45").Value = False Then
    MsgBox "Please mark the product you sold to the customer."
    
    With frmReferral
        .ccbxMutualFund.Enabled = True
        .ccbxVariableAnnuity.Enabled = True
        .ccbxFixedLife.Enabled = True
        .ccbxOther.Enabled = True
        .ctxtOther.Enabled = True
        .ccbxMutualFund.SetFocus
    End With
    
    Exit Sub
End If

If Range("O42").Value = True And IsEmpty(Range("C46").Value) Then
    MsgBox "Please enter the customer's account number in the specified area."
    frmReferral.ctxtCustomerAccount.Enabled = True
    frmReferral.ctxtCustomerAccount.SetFocus
    Exit Sub
End If

That makes it easier for people who did not write the code
to read through it.

[DrewDog_21]

Never mind about the button, I found it. I ran it and
saved the sheet, and it looks like it worked okay and
without any errors. I am running Windows NT 4.0 with
office 2000. Have you tested it on a third computer? It
may just be a local problem.

[davidgottlieb]

Could you see what you could do with it? I have been working on this thing for like 5 months and everytime I think I am done, I run in to a problem. I got that error message when you click "Save Form" it is:

Run-time Error '2110':
Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus.

What can I do about that???

[davidgottlieb]

I am also getting this error when I try it on other computers:
Compile Error in hidden module: formOpen

What does that mean and how can I get rid of it???


Thanks

[DrewDog_21]

I can look at it later today or tomorrow, but right now I
have to get cracking on something else. I hereby put a
call to the Gurus to take a look at this thing. Megatron?
HeSaidJoe? Any volunteers please speak up, then David can
authorize you to download the file.

[davidgottlieb]

What are their email addresses so I can authorize them to download the file.

Thank you so much for your help. You are a Saint!!!

[davidgottlieb]

If any one would like to download and look at the file - go to driveway.com and log in as "david5517" and the password is "bastian". Go to the folder - Forms and download the file in there.

Thank you guys.

[davidgottlieb]

Somebody please help me....please!!!

[davidgottlieb]

Please, just go to the web site driveway.com and log in as "david5517" and use password "bastian" to log in. Go to the folder "Forms" and download the file in there. Take a look at it and tell me anything wrong and what I can do to make it better. Please Please Please. I beg you.


Thanks Guys

[davidgottlieb]

Guys, I am doing this for the ENTIRE bank. This means that about 7,000 people will be using it. I have to have this thing out like yesterday. Somebody please take a look at it and help me. I am a beginner and I am running out of ideas. There are problems with the form and I don't know what to do. Go to the web site I mentioned and download it and tell me what I can do please. I am begging you. Thanks.

[davidgottlieb]

Please help me. I am about to lose my job. Please do a VB friend a favor and help me. I beg you