Copy UserForm text box values to another workbook

[tx7399]

Hi everyone,

I have a User Form that uses Find to locate a record based on the OrgId (Col A). The cell values are loaded into the User Form and the user can enter into the text boxes to edit the record. The cmdUpdate click event writes the User Form text box values to the worksheet (Sheet 1) to save the edits.
What I am trying to figure out is how to also write the values from six of the text boxes on the user form to another workbook named "edits_PG". The values need to be written to sheet 1 on the next empty row. Also, workbook "edits_PG" may or may not be open and is located on a network drive.
The text boxes are named: txtOrgId, txtOrgName, txtFirst, txtLast, txtEmail, txteAuditUserId and their values should populate cols A-F. In col G I need to enter the time the update was entered, perhaps by using NOW() formatted to include hrs, min, and seconds? These times will be sorted to insure that only the most recent edit to a record is retained and that only one record for an OrgId is on the sheet (perhaps by using "remove duplicates"?).
I have been able to adapt code written by y'all (I am a vba noobie) to get the basic "View/Edit Record" features working but am struggling to add to the code.
All suggestions will be greatly appreciated.

[mdmackillop]

Posting a sample workbook saves us from trying to replicate what you already have.

[Kenneth Hobs]

I would work with textboxes like this:

Private Sub CommandButton1_Click()
Dim t() As String, i As Integer
t() = Split("txtOrgId,txtOrgName,txtFirst,txtLast,txtEmail,txteAuditUserId", ",")
For i = LBound(t) To UBound(t)
Debug.Print Controls(t(i)).Name
Next i
End Sub

I use this to check if a workbook is open.

Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function

This to see if a worksheet exists.

'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
Dim ws As Worksheet, wb As Workbook
On Error GoTo notExists
If sWorkbook = "" Then
Set wb = ActiveWorkbook
Else
Set wb = Workbooks(sWorkbook) 'sWorkbook must be open already. e.g. ken.xlsm, not x:\ken.xlsm.
End If
Set ws = wb.Worksheets(sWorkSheet)
WorkSheetExists = True
Exit Function
notExists:
WorkSheetExists = False
End Function
You might consider putting the column number to poke the results into by using the TextBox's property, Tag. e.g. If Textbox1.Tag=3 then the column would be "C".

[tx7399]

I have added code to help explain what my question is. Once the UserForm is edited I need to copy text box values to another workbook. My question is also posted at http://www.excelforum.com/excel-programming-vba-macros/1148762-copy-row-to-sheet-2-only-if-a-change-is-made-to-1-or-more-of-5-designated-cells.html


Private Sub cmdUpdate_Click() ' update an existing record

On Error GoTo errHandler
Dim FirstAddress As String
Dim MyArr As Variant
Dim rng As Range
Dim i As Long
Dim Cancel As Boolean

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Search for a Value Or Values in a range
'You can also use more values like this Array("ron", "dave")
MyArr = Array(txtOrgId.Value)

'Search Column or range
With Sheets("Sheet1").Range("A:A")

For i = LBound(MyArr) To UBound(MyArr)

'If you want to find a part of the rng.value then use xlPart

Set rng = .Find(What:=MyArr(i), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
FirstAddress = rng.Address
Do

'update the cell in the column to the right if txtOrgId.Value is found
rng.Offset(0, 1).Value = txtOri.Text
rng.Offset(0, 2).Value = txtCCHId.Value
rng.Offset(0, 3).Value = cboStatus.Text
rng.Offset(0, 4).Value = cbo411.Text
rng.Offset(0, 5).Value = txtLicConExpDate.Text
rng.Offset(0, 6).Value = txtOrgName.Text
rng.Offset(0, 7).Value = txtMailAddress.Text
rng.Offset(0, 8).Value = txtPhysicalAddress.Text
rng.Offset(0, 9).Value = txtCity.Text
rng.Offset(0, 10).Value = txtState.Text
rng.Offset(0, 11).Value = txtZip.Text
rng.Offset(0, 12).Value = cboCounty.Text
rng.Offset(0, 13).Value = txtRegion.Text
rng.Offset(0, 14).Value = txtFirst.Text
rng.Offset(0, 15).Value = txtLast.Text
rng.Offset(0, 16).Value = txtEmail.Text
rng.Offset(0, 17).Value = txtPhone.Text
rng.Offset(0, 18).Value = txtExt.Text
rng.Offset(0, 19).Value = txtFax.Text
rng.Offset(0, 20).Value = txtTrngAttDate.Text
rng.Offset(0, 21).Value = txtWhoAttended.Text
rng.Offset(0, 22).Value = cboTrainer.Text
rng.Offset(0, 23).Value = txtCjisUserId.Text
rng.Offset(0, 24).Value = txtCjisSatTrngSent.Text
rng.Offset(0, 25).Value = txtStatementOfComplianceRecd.Text
rng.Offset(0, 26).Value = txteAuditUserId.Text
rng.Offset(0, 27).Value = txteAuditDate.Text
rng.Offset(0, 28).Value = txtLastAuditDate.Text
rng.Offset(0, 29).Value = txtPrevAuditDate.Text
rng.Offset(0, 30).Value = txtOlderPrevAuditDate.Text
rng.Offset(0, 31).Value = txtNonCompLtrSent.Text
rng.Offset(0, 32).Value = txtNonCompResponseDue.Text
rng.Offset(0, 33).Value = txtNonCompResponseRecd.Text
rng.Offset(0, 34).Value = cboAuditor.Text
rng.Offset(0, 35).Value = txtNotes.Text
rng.Offset(0, 36).Value = txtCjisSatExpDate.Text


'DATA ENTRY VALIDATION
With Me.Controls("txtOrgName")
If .Value = "" Then
MsgBox "Please enter the Org Name. ", vbExclamation, "Invalid Entry"
.SetFocus
Exit Sub
End If
End With

With Me.Controls("txtZip")
If Not .Value = "" Then
If Len(.Value) <> 5 Or Not IsNumeric(.Value) Then 'requires a five digit zip or will allow blank
MsgBox "Missing or invalid zipcode. Enter a five digit zipcode. ", vbExclamation, "Invalid Zipcode Entry"
.SetFocus
Exit Sub
End If
End If
End With

Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> FirstAddress

Else
MsgBox "OrgId not found !" & vbNewLine & "To add a new record:" & vbNewLine & "Please enter all available information then click Add New Record"
Call cmdClearData_Click
cmdAdd.Visible = True
cmdFind.Visible = False
Exit Sub
End If
Next i
End With

''''''''''''''''''''''''''''''''''''''''''''''
'************************************************************************** ******
'???????????????????????????????????????????????????????????????????
'add code here to paste the values of:
'rng.Offset(0, 1).Value = txtOri.Text
'rng.Offset(0, 6).Value = txtOrgName.Text
'rng.Offset(0, 14).Value = txtFirst.Text
'rng.Offset(0, 15).Value = txtLast.Text
'rng.Offset(0, 16).Value = txtEmail.Text
'rng.Offset(0, 26).Value = txteAuditUserId.Text
'from this UserForm into the next empty row of another workbook named "V:\CHIU\CHIU\Audit and Training\edits_PG" which, if not
'open, needs to be opened. I also need to add a timestamp into Col G.
''''''''''''''''''''''''''''''''''''''''''''''''
'************************************************************************** *******
'************************************************************************** *******

With Application
.ScreenUpdating = True
.EnableEvents = True
End With


Worksheets("Sheet1").Activate
MsgBox "Update successful!" & " OrgId:" & " " & txtOrgId.Text

Call cmdClearData_Click

Exit Sub
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description


End Sub