Run time Error 91 object variable or with block variable not set when showing userform

Question

I have problem with closing one userform and going to next. UserForm3 after clicking command button should be closed and UserForm4 should be shown. Unfortunately I get "Run time Error 91 object variable or with block variable not set". I've dug deep into internet and I am pretty sure that problem is with Userform4, although code for UserForm3 is highlighted as bugged. Basicly I want UserForm4 to be displayed and have all the textboxes filled with data from sheet "Log", based on choice from Combobox from UserForm3. Choice from UserForm3 is saved to cell E1 on "Log" Sheet.

Code from UserForm3

Private Sub CommandButton1_Click()

Sheets("Log").Range("E1") = ComboBox2.Text
Unload Me
UserForm4.Show  <- ERROR DISPLAYED HERE

End Sub

In UserForm4 I want to find value from E1 in cells below and later on fill textboxes in Userform4 with data from the row, in which E1 value was found.

Code for UserForm4

Private Sub UserForm_Initialize()

Dim Name As String
Dim rng As Range
Dim LastRow As Long
Dim wart As Worksheet

wart = Sheets("Log").Range("E1")
LastRow = ws.Range("B3" & Rows.Count).End(xlUp).Row + 1

Name = Sheets("Log").Range("E1")
UserForm4.TextBox8.Text = Name

nazw = Application.WorksheetFunction.VLookup(wart, Sheets("Log").Range("B3:H" & LastRow), 1, False)

UserForm4.TextBox1.Text = ActiveCell.Offset(, 1)
UserForm4.TextBox2.Text = ActiveCell.Offset(, 1)
UserForm4.TextBox3.Text = ActiveCell.Offset(, 1)
UserForm4.TextBox4.Text = ActiveCell.Offset(, 1)
UserForm4.TextBox5.Text = ActiveCell.Offset(, 1)
UserForm4.ComboBox1.Text = ActiveCell.Offset(, 1)
UserForm4.TextBox6.Text = ActiveCell.Offset(, 1)
UserForm4.TextBox7.Text = ActiveCell.Offset(, 1)

End Sub

what is wart = Sheets("Log").Range("E1") ?? Is wart a Worksheet object or Range object ? If it's worksheet, then Set wart = Sheets("Log") . If it's a Range, then Set wart = Sheets("Log").Range("E1"). — Shai Rado
I want "wart" to be the contents of "E1" cell. Later on it should be searched with Vlookup. — Igor
then change your Dim wart As Worksheet to Dim wart As String if "E1" contains a string, or Dim wart As Integer (or Long) if E1" contains a Number. You could also use Dim wart As Variant — Shai Rado
Ok i changed it to Dim wart As String. Now i get "Compile Error: Object required" with Private Sub UserForm_Initialize() highlighted in yellow and Set wart = in blue. — Igor

Shai Rado Shai Rado · Accepted Answer · 2016-12-06T12:33:51

The code below is to avoid to run-time errors mentioned in the code above, it's not debugged for the VLookup function part.

Option Explicit

Private Sub UserForm_Initialize()

Dim Name        As String
Dim LastRow     As Long
Dim wart        As Variant
Dim ws          As Worksheet
Dim nazw        As Long

' set ws to "Log" sheets
Set ws = Sheets("Log")

With ws
    wart = .Range("E1")

    ' method 1: find last row in Column "B" , finds last row even if there empty rows in the middle
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

    ' method 2 to find last row, equivalent to Ctrl + Shift + Down
    ' LastRow = .Range("B3").CurrentRegion.Rows.Count + 1

    ' a little redundant with the line 2 above ?
    Name = .Range("E1")
End With

With Me
    .TextBox8.Text = Name

    ' ****** Need to use Match instead of Vlookup VLookup Section ******
    If Not IsError(Application.Match(wart, ws.Range("B1:B" & LastRow - 1), 0)) Then
        nazw = Application.Match(wart, ws.Range("B1:B" & LastRow - 1), 0)
    Else ' wart record not found in range
        MsgBox "Value in Sheet " & ws.Name & " in Range E1 not found in Column B !", vbInformation
        Exit Sub
    End If

    .TextBox1.Text = ws.Range("B" & nazw).Offset(, 1)
    .TextBox2.Text = ws.Range("B" & nazw).Offset(, 1)
    .TextBox3.Text = ws.Range("B" & nazw).Offset(, 1)
    .TextBox4.Text = ws.Range("B" & nazw).Offset(, 1)
    .TextBox5.Text = ws.Range("B" & nazw).Offset(, 1)
    .ComboBox1.Text = ws.Range("B" & nazw).Offset(, 1)
    .TextBox6.Text = ws.Range("B" & nazw).Offset(, 1)
    .TextBox7.Text = ws.Range("B" & nazw).Offset(, 1)
End With

End Sub

Run time Error 91 object variable or with block variable not set when showing userform

1 Answers