1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Run-Time error '91': Object variable or With block variable

Discussion in 'Business Applications' started by mrapert, Sep 7, 2016.

Thread Status:
Not open for further replies.
  1. mrapert

    mrapert Thread Starter

    Joined:
    Sep 7, 2016
    Messages:
    1
    I have a user form with 2 textboxes and 3 command buttons. I have 3 worksheets in this workbook. the main worksheet is where the userform is located and the other two worksheets contain data. It's a check in/out macro that i'm going for here. When the user enters their id in textbox1, the macro should check in worksheet2 to verify that the employee id is in fact valid. When the user enters in a serial number in textbox2, the macro should check worksheet3 to verify that the serial number is valid AND that it has not already been checked out.
    Any help would be GREATLY appreciated! I've been stumped on this for a week now!

    I've copied and pasted my code below. Where I'm receiving the error is highlighted in red text.

    Private Sub Label1_Click()

    End Sub

    Private Sub cmdclear_Click()
    'save workbook
    ActiveWorkbook.Save
    'Blank text boxes
    txtemp.Value = ""
    txtscan.Value = ""
    'Disable in and out buttons until there is data in there
    cmdout.Enabled = False
    cmdin.Enabled = False
    End Sub

    Private Sub cmdin_Click()

    Dim rng1 As Range
    Dim rng2 As Range

    Set rng1 = Range("AB2:AB215")
    Set rng2 = Range("AC2:AC88")

    If rng1 = txtemp.Value And rng2 = txtscan.Value Then
    Worksheets("Scanner Inventory").Activate
    eRow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
    Cells(eRow, 1).Value = txtemp.Text
    Cells(eRow, 2).Value = txtscan.Text
    'Time Stamp
    eRow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
    Cells(eRow, 3).Value = Now()
    'Disable in and out buttons until there is data in there
    cmdout.Enabled = False
    cmdin.Enabled = False
    'message box for conformation
    MsgBox "Scanner returned to Inventory, please return to the cabinet.", vbExclamation, "INFO"
    'clears the textboxs
    txtemp = ""
    txtscan = ""
    'Disable in and out buttons until there is data in there
    cmdout.Enabled = False
    cmdin.Enabled = False
    'save workbook
    ActiveWorkbook.Save
    Else
    MsgBox "Employee ID or scanner serial number not found, please try again.", vbExclamation, "INFO"
    'clears the textboxs
    txtemp.Value = ""
    txtscan.Value = ""
    'Disable in and out buttons until there is data in there
    cmdout.Enabled = False
    cmdin.Enabled = False
    'save workbook
    ActiveWorkbook.Save
    End If

    End Sub

    Private Sub cmdout_Click()

    Dim rng1 As Range
    Dim rng2 As Range
    Dim emp As ValueChange
    Dim scan As ValueChange

    rng1 = Worksheets("Employees").Range("B2:B215")
    rng2 = Worksheets("Scanners").Range("A2:A88")

    emp = txtemp.Value
    scan = txtscan.Value

    If rng1 = emp Then
    Worksheets("Scanner Inventory").Activate
    eRow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
    Cells(eRow, 1).Value = txtemp.Text
    Cells(eRow, 2).Value = txtscan.Text
    'Time Stamp
    eRow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
    Cells(eRow, 3).Value = Now()
    'Disable in and out buttons until there is data in there
    cmdout.Enabled = False
    cmdin.Enabled = False
    'message box for conformation
    MsgBox "Scanner returned to Inventory, please return to the cabinet.", vbExclamation, "INFO"
    'clears the textboxs
    txtemp = ""
    txtscan = ""
    'Disable in and out buttons until there is data in there
    cmdout.Enabled = False
    cmdin.Enabled = False
    'save workbook
    ActiveWorkbook.Save
    Else
    MsgBox "Employee ID or scanner serial number not found, please try again.", vbExclamation, "INFO"
    'clears the textboxs
    txtemp.Value = ""
    txtscan.Value = ""
    'Disable in and out buttons until there is data in there
    cmdout.Enabled = False
    cmdin.Enabled = False
    'save workbook
    ActiveWorkbook.Save

    If rng2 = scan Then
    Worksheets("Scanner Inventory").Activate
    eRow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
    Cells(eRow, 1).Value = txtemp.Text
    Cells(eRow, 2).Value = txtscan.Text
    'Time Stamp
    eRow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
    Cells(eRow, 3).Value = Now()
    'Disable in and out buttons until there is data in there
    cmdout.Enabled = False
    cmdin.Enabled = False
    'message box for conformation
    MsgBox "Scanner returned to Inventory, please return to the cabinet.", vbExclamation, "INFO"
    'clears the textboxs
    txtemp = ""
    txtscan = ""
    'Disable in and out buttons until there is data in there
    cmdout.Enabled = False
    cmdin.Enabled = False
    'save workbook
    ActiveWorkbook.Save
    Else
    MsgBox "Employee ID or scanner serial number not found, please try again.", vbExclamation, "INFO"
    'clears the textboxs
    txtemp.Value = ""
    txtscan.Value = ""
    'Disable in and out buttons until there is data in there
    cmdout.Enabled = False
    cmdin.Enabled = False
    'save workbook
    ActiveWorkbook.Save
    End If
    End If

    End Sub
    Private Sub UserForm_Initialize()
    txtemp.SetFocus
    cmdout.Enabled = False
    cmdin.Enabled = False
    'hides application and only shows user form
    Application.Visible = False
    End Sub
    Private Sub UserForm_Terminate()
    Application.Visible = True
    End Sub
    Private Sub txtemp_Change()
    'Enables buttons after text is in boxes
    cmdout.Enabled = True
    txtemp.MaxLength = 8
    txtscan.MaxLength = 13
    End Sub
    Private Sub txtscan_Change()
    cmdin.Enabled = True
    txtemp.MaxLength = 8
    txtscan.MaxLength = 13
    End Sub
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You have to "Set" the range

    Code:
    Set rng1 = Worksheets("Employees").Range("B2:B215")
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1177679

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice