Object Variable Problem

SaraO · Mar 15th 2016

I'm using a userform to add customers to a Customer Database. The Customer Database is in a separate workbook. The code seems to be working up to the "myData.Save" line. There is get an error message saying "Object variable or With block variable not set".

I can't seem to fix this issue.

Here is the whole dose:

Code

Private Sub CommandButton1_Click()
Dim myData As Workbook
Application.ScreenUpdating = False
Set myData = Workbooks.Open("V:[URL="file://\\General\Customers.xlsx"]\General\Customers.xlsx[/URL]")
Worksheets("Customers").Select
Worksheets("Customers").Range("A1").Select
RowCount = Worksheets("Customers").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Customers").Range("A1")
TextBox1 = .Range("B1").Value + 1
.Offset(RowCount, 1) = TextBox1.Value
.Offset(RowCount, 2) = txtName.Value
.Offset(RowCount, 3) = txtAddress.Value
.Offset(RowCount, 4) = txtPhone.Value
.Offset(RowCount, 5) = txtEmail.Value
.Offset(RowCount, 6) = txtDiscount.Value
.Offset(RowCount, 7) = txtPSTRate.Value
.Offset(RowCount, 8) = txtPSTNumber.Value
.Offset(RowCount, 9) = txtPrintCopies.Value
End With
myData.Save
myData.Close
Application.ScreenUpdating = True
End Sub

Display More

Wigi · Mar 16th 2016

Re: Object Variable Problem

For example:

Code

Private Sub CommandButton1_Click()


    Dim myData As Workbook
    
    Application.ScreenUpdating = False
    
    Set myData = Workbooks.Open("V:\General\Customers.xlsx")
    With myData
        With .Worksheets("Customers")
            .Range("A" & .Parent.Rows.Count).End(xlUp).Offset(1).Resize(, 9).Value = _
                Array(.Range("B1").Value + 1, txtName.Value, txtAddress.Value, txtPhone.Value, txtEmail.Value, txtDiscount.Value, txtPSTRate.Value, txtPSTNumber.Value, txtPrintCopies.Value)
    
        End With
        .Save
        .Saved = True
        .Close 0
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Display More

Object Variable Problem

Participate now!

Share