User form data entry into two workbooks

davidkhbgh

New Member
Joined
Jan 2, 2006
Messages
40
I need data from a userform in workbook A to enter into both workbook A and workbook B. This code works for workbook A but does not populate data into workbook B. What have I got wrong?

Code:
Private Sub btnEnterProductionData_Click()

    Dim dteStartDate As Date
    Dim strCalender As String
    Dim strShift As String
    Dim dblGTNum As Double
    Dim dblItemNum As Double
    Dim strCustOrderNum As String
    Dim dteStartTime As Date
    Dim strOperator As String
    Dim strCoord As String
    dteStartDate = CustomerData.DateEntry.Value
    strCalender = CustomerData.CalenderList.Value
    strShift = CustomerData.ShiftList.Value
    dblGTNum = CustomerData.OrderNumber.Value
    dblItemNum = CustomerData.ItemNumber.Value
    strCustOrderNum = CustomerData.CustPONum.Value
    dteStartTime = CustomerData.TimeEntry.Value
    strOperator = CustomerData.OperatorList.Value
    strCoord = CustomerData.CoordinatorList.Value
    
            'check for calender entry
    If Trim(Me.CalenderList.Value) = "" Then
        Me.CalenderList.SetFocus
        MsgBox "Please enter Calender", vbCritical, "Calender"
    Exit Sub
    End If
        'check for coordinator entry
    If Trim(Me.CoordinatorList.Value) = "" Then
        Me.CoordinatorList.SetFocus
        MsgBox "Please enter first floor Shift Coordinator", vbCritical, "Shift Coordinator"
    Exit Sub
    End If
        'check for operator entry
    If Trim(Me.OperatorList.Value) = "" Then
        Me.OperatorList.SetFocus
        MsgBox "Please enter Calender Operator", vbCritical, "Calender Operator"
    Exit Sub
    End If
        'check for shift entry
    If Trim(Me.ShiftList.Value) = "" Then
        Me.ShiftList.SetFocus
        MsgBox "Please enter shift", vbCritical, "Shift"
    Exit Sub
    End If
        'check for order number entry
    If Trim(Me.OrderNumber.Value) = "" Then
        Me.OrderNumber.SetFocus
        MsgBox "Please enter order number", vbCritical, "Order Number"
    Exit Sub
    End If
        'check order number for length
    If Len(OrderNumber.Value) <> 5 Then
        Me.OrderNumber.SetFocus
        MsgBox "Confirm order number is 5 digits", vbCritical, "Order Number"
    Exit Sub
    End If
            'check for item number entry
        If Trim(Me.ItemNumber.Value) = "" Then
        Me.ItemNumber.SetFocus
        MsgBox "Please enter item number", vbCritical, "Item Number"
    Exit Sub
    End If
            'check for item number length
    If Len(ItemNumber.Value) <> 6 And Len(ItemNumber.Value) <> 7 Then
        Me.ItemNumber.SetFocus
        MsgBox "Confirm item number is 6 digits or 6 digits followed by one letter", vbCritical, "Item Number"
    Exit Sub
    End If
  
    Windows("Tally Sheet ver7.2.xls").Activate
        ActiveWorkbook.Sheets("TallySheet").Activate
            'set start time
            Sheet1.Range("C6").Value = dteStartTime
            'set item number
            Sheet1.Range("C4").Value = dblItemNum
            'set order number
            Sheet1.Range("C3").Value = dblGTNum
            'set shift
            Sheet1.Range("E3").Value = strShift
            'set Customer PO Number
            Sheet3.Range("D12").Value = strCustOrderNum
            'set coordinator
            Sheet1.Range("E5").Value = strCoord
            'set operator
            Sheet1.Range("C5").Value = strOperator
            'set date
            Sheet1.Range("E2").Value = dteStartDate

            '**************************************************
            
    Windows("Tally Sheet Master.xls").Activate
        ActiveWorkbook.Sheets("ProductionRecord").Select
            'set start time
            Sheet1.Range("C6").Value = dteStartTime
            'set item number
            Sheet1.Range("C4").Value = dblItemNum
            'set order number
            Sheet1.Range("C3").Value = dblGTNum
            'set shift
            Sheet1.Range("E3").Value = strShift
            'set Customer PO Number
            Sheet3.Range("D12").Value = strCustOrderNum
            'set coordinator
            Sheet1.Range("E5").Value = strCoord
            'set operator
            Sheet1.Range("C5").Value = strOperator
            'set date
            Sheet1.Range("E2").Value = dteStartDate
        
        '**************************************************
    Unload Me

Thanks for the help.

Dave
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

hayden

Board Regular
Joined
Sep 23, 2005
Messages
159
Windows("Tally Sheet ver7.2.xls").Activate
ActiveWorkbook.Sheets("TallySheet").Activate

That is the frist one.

The second one is this

Windows("Tally Sheet Master.xls").Activate
ActiveWorkbook.Sheets("ProductionRecord").Select

Maybe change it from .slect to .activate and see if that helps.

I am pretty new to VBA so if it doesn't help let us know and I am sure someone else can help you out.

Hayden
 

davidkhbgh

New Member
Joined
Jan 2, 2006
Messages
40
I tried both select and activate and both worked in the same manner. No data in workbook B.

Dave
 

hayden

Board Regular
Joined
Sep 23, 2005
Messages
159
Hummm,
Ok what about this:
Code:
Workbooks("Tally Sheet Master.xls").Sheets("ProductionRecord").Range("C6").Value = dteStartTime

Only change all of them to that. I would step through it wtih f8 and see if you can figure out where specifically it is breaking.

Hayden

(Oh and by the way it might just be workbook and not workbooks I don't remember off the top of my head).
 

Forum statistics

Threads
1,136,515
Messages
5,676,302
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top