Code Problem Saving Data To Another Workbook

[Mooseman60]

I have the following code to enter data in a userform and save it to a separate workbook called Defined Names Lists.xls to a worksheet in that workbook file called JobNames

I am entering a Job Name & Job No but keep getting the following error for the line of code below Run-time Error 13 Type mismatch

[vba]
Set wb = Workbooks("Defined Name Lists.xls").Worksheets("JobName").Range("A2").Value = txtJobName.Text
[/vba]



[vba]
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Workbook
Set wb = Workbooks("Defined Name Lists.xls").Worksheets("JobName").Range("A2").Value = txtJobName.Text
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a JobName
If Trim(Me.txtJobName.Value) = "" Then
Me.txtJobName.SetFocus
MsgBox "Please enter a Job Name"
Exit Sub

End If
'check for a JobNo
If Trim(Me.txtJobNo.Value) = "" Then
Me.txtJobNo.SetFocus
MsgBox "Please enter a Job No"
Exit Sub

End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtJobName.Value
ws.Cells(iRow, 2).Value = Me.txtJobNo.Value
If Me.txtJobNo.Value <> "" Then
Dim wbOutput As Workbook
Set wbOutput = Workbooks.Add
With wbOutput
.Title = "Job Number "
.Subject = "Job Number created " & Date
.Author = "Script generated"
.SaveAs Filename:=mydir & "\" & Me.txtJobNo.Value
End With
End If

'clear the data
Me.txtJobName.Value = ""
Me.txtJobNo.Value = ""
Me.txtJobNo.SetFocus
Exit Sub

End Sub

[/vba]

[Ryan Remole]

[VBA]
Set wb = Workbooks("Defined Name Lists.xls")
wb.Activate
Worksheets("JobName").Range("A2").Value = txtJobName.Text
[/VBA]

[Mooseman60]

[vba]
Set wb = Workbooks("Defined Name Lists.xls")
wb.Activate
Worksheets("JobName").Range("A2").Value = txtJobName.Text
[/vba]

Ryan

That solved that problemn now it coes up with error 91 Object variable or with block variable not set


on this line of code
[vba]
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtJobName.Value
ws.Cells(iRow, 2).Value = Me.txtJobNo.Value
[/vba]

[mdmackillop]

I guess you are not using Option Explicit, otherwise the fundemental errors would become apparent

[VBA]Dim iRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("Defined Name Lists.xls")
Set ws = wb.Worksheets("JobName")
ws.Range("A2").Value = txtJobName.Text
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
[/VBA]

[Ryan Remole]

Ryan

That solved that problemn now it coes up with error 91 Object variable or with block variable not set


on this line of code
[vba]
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtJobName.Value
ws.Cells(iRow, 2).Value = Me.txtJobNo.Value
[/vba]

I don't think you need to include 'Me' on your controls. 'Value' is optional.

[Mooseman60]

I am still getting variable not set on this line

ReDim Preserve Name(1 To nList)

 
Option Explicit
Const cListFile As String = "Defined Name Lists.xls"    ' the file containing the combobox data
Const cListSheet As String = "JobName"    ' the worksheet containing the list
Const cNameColumn As String = "a"    ' the column containin gthe list data
Const cJobColumn As String = "b"
Const cHasHeader As Boolean = False    ' does the list have a header in row 1
Private Sub UserForm_Initialize()
    ' open the workbook containing the data to load in combox1 read only
    ' and hide it
Dim iRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("Defined Name Lists.xls")
Set ws = wb.Worksheets("JobName")
ws.Range("A2").Value = txtJobName.Text
     Set theWB = Workbooks.Open(Filename:="Defined Name Lists.xls", ReadOnly:=True)
    theWB.Windows(1).Visible = False
   
    
    ' address the combox list
    Dim theSheet As Worksheet
    Set theSheet = theWB.Worksheets(cListSheet)
        ' array size counter
 'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Dim nList As Long
    nList = 0
    
    ' loop through all of the list loading the values into the combobox
    Dim rw As Range
    For Each rw In theSheet.Rows
        ' skip row 1 if there is a header
        If (rw.Row = 1 And cHasHeader) Then
            ' stop on the first blank cell
        ElseIf (rw.Cells(1, 1).Value = "") Then
            Exit For
        Else
     ' move the data from the list workbook to the combox
            cboJobName.AddItem rw.Cells(1, 1).Value
          ' retain the job name and job no
            nList = nList
            ReDim Preserve Name(1 To nList)
            ReDim Preserve Job(1 To nList)
            Names(nList) = rw.Cells(1, cNameColumn).Value
            Jobs(nList) = rw.Cells(1, cJobColumn).Value
        End If
    Next rw
       
    ' close the workbook and release storage
    theWB.Close SaveChanges:=False
    Set theWB = Nothing
    Set theSheet = Nothing
    
  
End Sub

[mdmackillop]

Two thoughts,
Should Name be Names
I don't see nList being incremented so you are dimming (1 to 0)

[Mooseman60]

It now comes up with error subscript out of range any suggestions i have attached my workbook

This the current code

 
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("Defined Name Lists.xls")
Set ws = wb.Worksheets("JobName")
ws.Range("A2").Value = txtJobName.Text
 'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  
'check for a JobName
If Trim(Me.txtJobName.Value) = "" Then
  Me.txtJobName.SetFocus
  MsgBox "Please enter a Job Name"
  Exit Sub
  
   End If
 'check for a JobNo
If Trim(Me.txtJobNo.Value) = "" Then
  Me.txtJobNo.SetFocus
  MsgBox "Please enter a Job No"
  Exit Sub
  
  End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtJobName.Value
ws.Cells(iRow, 2).Value = Me.txtJobNo.Value
If Me.txtJobNo.Value <> "" Then
   Dim wbOutput As Workbook
   Set wbOutput = Workbooks.Add
    With wbOutput
        .Title = "Job Number "
        .Subject = "Job Number created " & Date
        .Author = "Script generated"
        .SaveAs Filename:=mydir & "\" & Me.txtJobNo.Value
    End With
End If

'clear the data
Me.txtJobName.Value = ""
Me.txtJobNo.Value = ""
Me.txtJobNo.SetFocus
Exit Sub

End Sub

[mdmackillop]

On which line?
Unfortunately your code can't run without the referenced workbook.

[Mooseman60]

On which line?
Unfortunately your code can't run without the referenced workbook.

I am a novice with VBA so what do I need to do to reference the workbook

[mdmackillop]

You need to post this workbook as well

[Mooseman60]

You need to post this workbook as well

[mdmackillop]

I meant this one.
Defined Name Lists.xls