Can I have the Excel Data Entry Form open on file open, then email worksheet when the form is closed?

I would like to have the Excel produced Data Entry Form open when the file is open, then once the user has entered the data and clicked on close, have the worksheet emailed to a designated email address naming the attachment using the current user's logonID.
Can I edit the data entry form that Excel creates?
Michael SpellmanSupervisory Operations Support SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
you can add below code into the "ThisWorkbook" Object

it will fire sending email, once user wants to close workbook.

try it in a backup copy.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim strDate As String
    ActiveSheet.Copy
    strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
    ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
        & " " & strDate & ".xlsx"
    ActiveWorkbook.SendMail "jamesbond@uk.gov", _
        "Subject_line"
    ActiveWorkbook.ChangeFileAccess xlReadOnly
    Kill ActiveWorkbook.FullName
    ActiveWorkbook.Close False
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael SpellmanSupervisory Operations Support SpecialistAuthor Commented:
This is the code that I used.  It opens the Data Entry Form.  (I'd like to get rid of the warning dialog box that appears - Excel cannot determine which row...etc).  Then when user closes the data entry form, it emails the active worksheet to me.

I would like to hide the workbook and just have the data entry form appear.  It would also be nice if I could edit the data entry form.  I suspect that I would have to switch to a userform, but dont know.  Is that correct?
ENS-AltForm.xlsm
0
ProfessorJimJamCommented:
:)  by accident i opened the file and it sent two emails to one of the emails in the macro. so if you get those emails, please ignore them.  i will look at your file later today in detail and see what i could possible come up with.
0
Michael SpellmanSupervisory Operations Support SpecialistAuthor Commented:
Thanks for the help.  If you get a chance to comment on the other aspects, let me know.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.