The below code is used on the "Documents (Attach") button using the "Letters" userform.
The idea is if a template letter is created there will only be one of them and it will be pasted into a specific cell (omitted from this code), but if a case handler creates a free form letter, or a letter from a customer is received it can be saved and then entered into the database.
The idea of the code is meant to find a documents location and paste it into the "Database" tab. If it fits the select case criteria it pastes it into a defined row (left out of this book), if it doesn't it is pasted into a free cell from cell CH (I think it's somewhere around there) onwards.
I have been playing with this for over a week and still can't quite get it right, any help would be greatly appreciated.
Document attached to see how it works.
Private Sub CommandButton2_Click()
Dim UserId As String
Dim CaseId As String
Dim SearchString As String
UserId = Letters.User.Value
CaseId = Letters.CaseNo.Value
SearchString = UserId & CaseId
Select Case Description.Value
Case "Customer Complaint Acknowledgement", "Complaint to Bank", "8 Week Breach to Bank", "Final Response Received", "Referal to FOS", "FOS Decision", "Payment Due", "Payment Overdue - Chaser 1", "Payment Overdue - Chaser 2", "Case Closed"
Description.Value = "Templates"
End Select
Letters.Browse.Copy
If Letters.Browse.Text <> "" And Letters.Description.Text = "" Then
MsgBox "Complete Document Description"
End If
' Search Case Reference Number column from database based on two values in Userform
'
If Letters.Browse.Text <> "Templates" And Letters.Description.Text <> "" And Sheets("Database").Cells(WorksheetFunction.Match(SearchString, Sheets("Database").Range("B:B"), 0), 86).Value = "" Then
On Error Resume Next
Sheets("Database").Cells(WorksheetFunction.Match(SearchString, Sheets("Database").Range("B:B"), 0), 86).Value = Letters.Browse.Value
Sheets("Database").Cells(WorksheetFunction.Match(SearchString, Sheets("Database").Range("B:B"), 0), 87).Value = Letters.Description.Value
On Error GoTo 0
ElseIf Letters.Browse.Text <> "Templates" And Letters.Description.Text <> "" And Sheets("Database").Cells(WorksheetFunction.Match(SearchString, Sheets("Database").Range("B:B"), 0), 86).Value <> "" Then
Do Until IsEmpty = False
ActiveCell.Offset(0, 3).Select
Loop
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
Letters.Hide
End Sub
Bookmarks