Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

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?
0
Michael Spellman
Asked:
Michael Spellman
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now