Object Var or With Block not Set VBA

Question

Im tyring to write a VBA code to Copy the active row to a new sheet. Then Pull up a user form with a text box entry and list entry. When entered it will update the cells accordingly and update the date as well.

I am currently getting the Run-time error '91': Object varaible or with block variable not set. I can't Figure out where This error is coming from if you can help and explain it to me I would appreciate it greatly!

There are two files one to call the userform which is where i am getting the error.

Sub NewCange()

 ChangeUserForm.Show


End Sub

and the userform code

Private Sub UserForm_Initialize()


    Dim i As Integer, M As Integer, Result As Integer
    Dim N As String
    Dim MasterRange As Range, CopyRange As Range, NextRow As Range
    Dim ActiveRow As Long




  'Selects active row copies it and puts in empty row in the copy sheet


     ActiveRow = ActiveCell.Row

            With Worksheets("Master")
                   .Activate
                   .Cells(ActiveRow, 1).Select
                   .Cells(ActiveRow, 1).EntireRow.Copy
            End With

            With Worksheets("Records")
                   .Activate
                   .Cells(ActiveRow, 1).Select
                Rows(ActiveRow).Select
        End With

         ActiveSheet.Paste      
Destination:=Worksheets("Records").Cells(ActiveRow, 1)



'Below here is the inserting new change into excel sheet




    Set MasterRange = Range(ActiveCell, ActiveCell)

     Worksheets("Master").Activate

    LocalListBox.Clear

'List of Locations for assets
With LocalListBox

    .AddItem "TB01 "
    .AddItem "TB02 "
    .AddItem "TB03 "
    .AddItem "TB04 "
    .AddItem "TB05 "
    .AddItem "TB06 "
    .AddItem "TB07 "
    .AddItem "TB07XP "
    .AddItem "TB08 "
    .AddItem "TB09 "
    .AddItem "TB10 "
    .AddItem "TB11 "
    .AddItem "TB12 "
    .AddItem "TB13 "
    .AddItem "TB14 "
    .AddItem "TB15 "
    .AddItem "TB16 "
    .AddItem "TB17 "
    .AddItem "TB17XP "
    .AddItem "TB18 "
    .AddItem "TB19 "
    .AddItem "TB20 "
    .AddItem "TB21 "
    .AddItem "TB23 "
    .AddItem "TB24 "
    .AddItem "TB25 "
    .AddItem "TB26 "
    .AddItem "TB27 "
    .AddItem "TB27XP "
    .AddItem "TB28 "
    .AddItem "TB29 "
    .AddItem "TB30 "
    .AddItem "TB31 "
    .AddItem "TB32 "
    .AddItem "CAB3 "
    .AddItem "CAB4 "
    .AddItem "CAB5 "
    .AddItem "CAB6 "
    .AddItem "CAB7 "
    .AddItem "CAB8 "
    .AddItem "CAB9 "
    .AddItem "CAB10 "
    .AddItem "CAB12 "
    .AddItem "CAB16 "
    .AddItem "CAB17 "
    .AddItem "CAB18 "
    .AddItem "CAB19 "
End With


   Set NameRange = ActiveCell

MasterRange = LocalListBox.Value
MasterRange.Offset(0, 2) = NameTextBox.Value
MasterRange.Offset(0, 1) = Date

Unload Me


End Sub

Yes but when I would step through it would go all the way through the user form then give me the error — Pwoods, Jul 31 '15 at 21:45

Siddharth Rout · Answer 1 · 2015-07-31T21:57:27.497

1

Run-time error '91': Object varaible or with block variable not set.

You are getting that error because you are trying to unload something which is just initializing.

So basically you can't do this

Private Sub UserForm_Initialize()
    Unload Me
End Sub

Remove Unload Me and put it in a button click. This way you will also let the user select a value from the listbox LocalListBox :)

In fact move this block of code in a Button Click

Private Sub CommandButton1_Click()
    MasterRange = LocalListBox.Value
    MasterRange.Offset(0, 2) = NameTextBox.Value
    MasterRange.Offset(0, 1) = Date

    Unload Me
End Sub

and declare Dim MasterRange As Range at the top of the userform instead in UserForm_Initialize()

edited Jul 31 '15 at 21:57

answered Jul 31 '15 at 21:29

Siddharth Rout

147,039
17
206
250

Trying to use the selection made from the LocalListBox and put it into the 2nd cell in the active row. I tried changing it to what you had recommended but it didn't work. I just tried this as well and still was producing an error. – Pwoods Jul 31 '15 at 21:40
MasterRange.Offset(0, 1) = LocalListBox.Value MasterRange.Offset(0, 3) = NameTextBox.Value MasterRange.Offset(0, 2) = Date – Pwoods Jul 31 '15 at 21:41
sorry for the mess above i couldnt get the code in my edit – Pwoods Jul 31 '15 at 21:42

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

It looks like you're missing a period in a With statement:

With Worksheets("Records")
               .Activate
               .Cells(ActiveRow, 1).Select
            Rows(ActiveRow).Select
    End With

should be

   With Worksheets("Records")
           .Activate
           .Cells(ActiveRow, 1).Select
        .Rows(ActiveRow).Select
End With

Also, to find exactly where your error is, use F8 to "walk through" the macro. Also, it is highly recommended to avoid using .Select in VBA.

edited May 23 '17 at 11:51

Community

1
1

answered Jul 31 '15 at 21:29

BruceWayne

22,923
15
65
110

@SiddharthRout - I realize it's not, like 2 seconds after your post though. :P But he should still change it, because otherwise it's possible the macro will grab the wrong row. – BruceWayne Jul 31 '15 at 21:30
That's ok.. Happens with me sometimes as well :) – Siddharth Rout Jul 31 '15 at 21:30
Thanks for pointing that out I did need to add that in either way – Pwoods Jul 31 '15 at 21:42

Object Var or With Block not Set VBA

2 Answers2