Solved: Take User form data to new worksheet

[MRichmond]

Good day to you all,

I have an issue I am looking for help with and you guys have never let me down yet so here goes.

I have a workbook that uses a user form to enable staff members to log complaints in uniform manner, and this works exactly as it should and no problem there.

However, now my boss would like to be able to select a record (using the user form) and when he has chosen the one he wants, he want to be able to email that record to one of a few possible people based on the type of issue (ie it could be our purchasing dept, or the customer or our HSEQ dept). Each of these different targets would want different info.

So i'm thinking, if the complaint is resolved, there would be option buttons appear. When an option is selected a hidden formatted worksheet appears and the relevant data from the user form is transferred onto this worksheet. This worksheet is then available to the user to enter data not available on the user form. It is then saved as a new workbook (onto desktop?), opens Outlook and attaches the new workbook, which user will manually send.

Make sense? I hope so. I am attaching a sample workbook to give you an idea.

Thanks in advance for the help

[Rob342]

MRichmond

I personally, would think about adding the missing data to the form when selected this can then be written back to the selected formatted sheet.
You could then select that sheet create a backup copy & e mail it at the same time.

I have some code already for the copy & e mail part, let me know which way you want to go?

Rob

[Bob Phillips]

I would also change that form to a multipage tabbed form, one 2with customer details, one with complaint details, one with resolution details. It is far too fussy at the moment, this would be much cleaner, and you could have a hidden tab along the lines that Rob342 suggests, showing it when the complaint is resolved.

[MRichmond]

Thanks Rob & XLD,
This is why I love coming to this site.

I never thought of a multi tab form. I was having the guys add the extra off the form as I didnt want the form to get too busy/messy.

This solution cures both.

Multi tab it is, all data on the form, copy & email.

If you could post your copy and email code Rob, that would be great.

[Bob Phillips]

The difficulty with the multipage form is that it is (far too) easy for the users to not check all of the tabs to ensure that they have fully and properly entered the data. You have to make sure that you do full validation of that in your code (for instance, they cannot enter the resolution details if they haven't entered the complaint details). That is no different than all on a single form, but it is more likley to happen with a multipage control IMO. The only good place I can think of to check all of this is an OK/Save button that checks it and won't exit if the data is not complete to that stage and valid.

[MRichmond]

Thanks XLD, that makes perfect sense.

How about not presenting the resolution tab, until the complaint tab has been filled in, is that easy to code?

[Bob Phillips]

Yes, something along the lines of

[vba]

If Me.MultiPage1.Pages(1).txtResolution.Text = "" Then

MsgBox "ERROR"
Else

Me.MultiPage1.Pages(2).Visible = True
Me.MultiPage1.Value = 2
End If
[/vba]

The problem (for you) is determining where to insert this code, after the textbox is changed, and maybe others, or some button. you might also need to test in the form activate event to test whether the form starts with it shown, as I am assuming the complaint details might be entered at a different time to the resolution details.

BTW, not the page number property starts at 0, not 1.

[Rob342]

MR

I have modded this code so you dont have too many changes to make, pls read the comments & alter to suit yours as req. the next step will be to combine both operations into 1, but without seeing you final code & how its working makes it difficult for me to know where to combine it.
Do you want to try this first ok
see Example attached

You can also look at this site www.rondebruin.nl/ for more ideas on emails
Rob

[MRichmond]

Thanks for the help Rob, but it's not quite what i'm looking for.

From the look of it, your code copies the entire sheet my data is housed on (although I cant quite get it to work), and will email that.

What I am actually looking for is (see new sample workbook):

User selects resolved complaint (Rows 2,3 & 4 on the data tab are the only resolved complaints) and it is just the data for that Row that is emailed.
e.g. User selects log no 2 on the userform (which equates to row 3 on the "data" worksheet), he then clicks on the email button (for Type 2) (see column B). This then populates the template worksheet "Issue Cust Type 2" and emails this sheet as an attachment using the email address in column F. This does not need to be saved once sent

Although I can live without the individual templates, I can only have the one row of data being sent.

[Bob Phillips]

In your form

[vba]

Private Sub cmdEmail_Click()
Call eMailComplaint(Me.txtLogNo.Text, 1)
End Sub

Private Sub cmdEmailType2_Click()
Call eMailComplaint(Me.txtLogNo.Text, , 2)
End Sub

Private Sub CmdEmailType3_Click()
Call eMailComplaint(Me.txtLogNo.Text, 3)
End Sub[/vba]

in a standard module

[vba]

Public Function eMailComplaint(Id As Long, ComplaintType As Long)
Dim wb As Workbook
Dim OutApp As Object
Dim OutMail As Object
Dim tempFilePath As String
Dim tempFileName As String

Application.ScreenUpdating = False

tempFilePath = CreateObject("WScript.Shell").SpecialFolders(16)
tempFileName = "Complaint no. " & Id & ".xls"

ActiveWorkbook.Worksheets("Issue Cust Type " & ComplaintType).Copy

ActiveWorkbook.SaveAs Filename:=tempFilePath & Application.PathSeparator & tempFileName
ActiveWorkbook.Close

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)
With OutMail

.To = ActiveWorkbook.Worksheets("Data").Cells(Id + 1, "F").Value
.Subject = "Complaint #" & Id
.Attachments.Add tempFilePath & Application.PathSeparator & tempFileName
.Display
End With

Set OutMail = Nothing

Kill tempFilePath & Application.PathSeparator & tempFileName

Set OutApp = Nothing

Application.ScreenUpdating = True
End Function[/vba]

I have modified Issue Cust Type 1 to facilitate this as a starter, you will need to do 2 and 3 for completion.

[MRichmond]

Thanks XLD, just what I was looking for.

Yet again you ride to the rescue.

Thanks again

[MRichmond]

Hi XLD,

I have been doing some testing, and it seems that it doesnt matter what record I select I only get issue 1 on teh response form (although it is numbered correctly on the email).

Any idea what i may be doing wrong?

[MRichmond]

upon further investigation, it appears that the value in cell A1, isn't being updated to be the logno, so I'm getting logno 1 all the time because that has been entered in issue type 1 (when I use other customer types I just sheet with no data).

[Bob Phillips]

Sorry for that error.

Change this code

[vba]

ActiveWorkbook.Worksheets("Issue Cust Type " & ComplaintType).Copy
[/vba]
to

[vba]

With ActiveWorkbook.Worksheets("Issue Cust Type " & ComplaintType)

.Range("A1").Value = Id
.Copy
End With
[/vba]