Reference to External Workbook Objects

Hi all

I'm having trouble working this around, i use the following code in one of my Workbook sheets.

Private Sub CommandButton1_Click()

Dim WsTarget As Worksheet
Dim WsSource As Worksheet
Dim CurRng As Range


Windows("Arrival Form.xls").Activate
Set WsSource = Worksheets("Arrival Form")
Windows("Main.xlsm").Activate
Set WsTarget = Worksheets("Bookings")
Set CurRng = WsTarget.Range("A1000").End(xlUp).Offset(1, 0)


CurRng.Offset(0, 0) = TextBox1.Value 'House
CurRng.Offset(0, 2) = Calendar2.Value 'Date of Departure
CurRng.Offset(0, 3) = TextBox2.Value 'Cleaning
CurRng.Offset(0, 8) = ComboBox13.Value 'Supplier
CurRng.Offset(0, 1) = Calendar1.Value 'Date of Arrival
CurRng.Offset(0, 19) = TextBox3.Value 'Special Requests
CurRng.Offset(0, 11) = TextBox4.Value 'Name
CurRng.Offset(0, 12) = TextBox5.Value 'Phone Number
CurRng.Offset(0, 13) = TextBox6.Value 'Mobile Number
CurRng.Offset(0, 14) = TextBox7.Value 'Email Address

'Cleaning'
If TextBox2.Value = "" Then
CurRng.Offset(0, 3) = "?"
End If

'Food'
If ComboBox1.Value = "Yes" Then
    CurRng.Offset(0, 15) = "Sim"
ElseIf ComboBox1 = "No" Then
    CurRng.Offset(0, 15) = "Não"
End If

'High Chair'
If ComboBox2.Value = "Yes" Then
    CurRng.Offset(0, 18) = "Sim"
ElseIf ComboBox2.Value = "No" Then
    CurRng.Offset(0, 18) = "Não"
End If

'Pool Heating'
If ComboBox3.Value = "Yes" Then
    CurRng.Offset(0, 17) = "Sim"
ElseIf ComboBox3.Value = "No" Then
    CurRng.Offset(0, 17) = "Não"
End If

'Cot'
If ComboBox4.Value = "Yes" Then
    CurRng.Offset(0, 16) = "Sim"
ElseIf ComboBox4.Value = "No" Then
    CurRng.Offset(0, 16) = "Não"
End If

'Entered on Calendar'
If ComboBox10.Value = "Yes" Then
    CurRng.Offset(0, 10) = "Sim"
ElseIf ComboBox10.Value = "No" Then
    CurRng.Offset(0, 10) = "Não"
End If

'Clean on arrival'
If ComboBox6.Value = "Yes" Then
  CurRng.Offset(0, 5) = "Sim"
ElseIf ComboBox6.Value = "No" Then
  CurRng.Offset(0, 5) = "Não"
End If

'Clean on Departure'
If ComboBox7.Value = "Yes" Then
  CurRng.Offset(0, 6) = "Sim"
ElseIf ComboBox7.Value = "No" Then
  CurRng.Offset(0, 6) = "Não"
End If

'Wash Laundry on Departure'
If ComboBox9.Value = "Yes" Then
  CurRng.Offset(0, 7) = "Sim"
ElseIf ComboBox7.Value = "No" Then
  CurRng.Offset(0, 7) = "Não"
End If

'Owner, Client, PHD'
If CheckBox7 = True And CheckBox8 = False And CheckBox9 = False Then
    CurRng.Offset(0, 4) = "Cliente"
ElseIf CheckBox7 = False And CheckBox8 = True And CheckBox9 = False Then
    CurRng.Offset(0, 4) = "Dono"
ElseIf CheckBox7 = False And CheckBox8 = False And CheckBox9 = True Then
    CurRng.Offset(0, 4) = "PHD"
End If



End Sub
Private Sub Calendar1_Change()

End Sub
Private Sub Calendar2_Change()

End Sub

Private Sub CheckBox1_Click()

End Sub
Private Sub CheckBox2_Click()

End Sub
Private Sub CheckBox3_Click()

End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub TextBox2_Change()

End Sub

Private Sub TextBox3_Change()

End Sub

Private Sub ComboBox1_Change()

End Sub

Explanation: I dont want the code to go on the Workbook to my clients, i want to have it on my main Workbook and when i press Button1 it will just copy the fields from "Arrival Form.xls" Workbook to my "Main.xlsm" Workbook

What happens after several trys its gives me object error and range error

Any help would be welcome, thanks in advance

Hi Rage what happens if you save the xlsm as xls?

I'm a little lost as why the code is in the clients workbook
Why dont you place the code in a module?
can you please explain a little more?

But how do i use that reference as u say with a Range on this


For example

CurRng.Offset(0, 0) = TextBox1.Value 'House

Can i use ?

Set CurRng = ClientsBook.Sheet1.Range("A1000").End(xlUp).Offset(1, 0)