CODEWITHSUNDEEP
vbaexcelruntime-error
thyde73
thyde73

Reputation: 3

How to fix Runtime Error "91" in Excel

I have created a userform to enter data into worksheets for me. I am trying to code it where it will put the information into a selected worksheet. I have a combobox where I can select the sheet I want it to put the info in. I am trying to set the variable ws to the combobox value and I get a runtime error '91' and it says "object variable or with block not set". The code I am having problems with is below.

Private Sub bttn_Submit_Click()
Dim iRow As Long
Dim ws As Worksheet

'set the variable for the worksheet
ws = ComboBox1.Value

'find first empty row in database
iRow = Columns("A").Find("*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1



'check for a date
If Trim(Me.txt_Date.Value) = "" Then
  Me.txt_Date.SetFocus
  MsgBox "Please enter a Date"
  Exit Sub
End If

'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
'  .Unprotect Password:="password"
  .Cells(iRow, 1).Value = Me.txt_Date.Value
  .Cells(iRow, 2).Value = Me.txt_Invoice.Value
  .Cells(iRow, 3).Value = Me.txt_Mileage.Value
  .Cells(iRow, 5).Value = Me.CheckBox_Oil_Filter
  .Cells(iRow, 7).Value = Me.CheckBox_Air_Filter
  .Cells(iRow, 9).Value = Me.CheckBox_Primary_Fuel_Filter
  .Cells(iRow, 11).Value = Me.CheckBox_Secondary_Fuel_Filter
  .Cells(iRow, 13).Value = Me.CheckBox_Transmission_Filter
  .Cells(iRow, 15).Value = Me.CheckBox_Transmission_Service
  .Cells(iRow, 17).Value = Me.CheckBox_Wiper_Blades
  .Cells(iRow, 19).Value = Me.CheckBox_Rotation_Rotated
  .Cells(iRow, 21).Value = Me.CheckBox_New_Tires
  .Cells(iRow, 23).Value = Me.CheckBox_Differential_Service
  .Cells(iRow, 25).Value = Me.CheckBox_Battery_Replacement
  .Cells(iRow, 27).Value = Me.CheckBox_Belts
  .Cells(iRow, 29).Value = Me.CheckBox_Lubricate_Chassis
  .Cells(iRow, 30).Value = Me.CheckBox_Brake_Fluid
  .Cells(iRow, 31).Value = Me.CheckBox_Coolant_Check
  .Cells(iRow, 32).Value = Me.CheckBox_Power_Steering_Check
  .Cells(iRow, 33).Value = Me.CheckBox_A_Transmission_Check
  .Cells(iRow, 34).Value = Me.CheckBox_Washer_Fluid_Check


'  .Protect Password:="password"
End With

'clear the data
Me.txt_Date.Value = ""
Me.txt_Invoice.Value = ""
Me.txt_Mileage.Value = ""
Me.txt_Date.SetFocus

End Sub

Upvotes: 0

Views: 5508

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12665

I take your piece of code and explain you the inconsistency: 

Dim ws As Worksheet
ws = ComboBox1.Value

The first line of code is declaring ws as a Worksheet object. Objects in VBA need the keyword Set to be set, and of course the data type needs to match the declaration.

On the other hand, in the second statement you're setting ws as ComboBox1.Value, which is a type String. So:

1) The reason why you get the error is that ws is a variable that, because of its declaration, expects to get a Worksheet object but you're instead trying to cast a String in it;

2) What you probably want to do is to have in ws the Worksheet object whose name is the ComboBox1.Value; this, in terms of code, should be written as follows: 

Set ws = ThisWorkbook.Sheets(ComboBox1.Value)

You only need to replace the line ws = ComboBox1.Value with the line above.

Pay attention, in general: if you declare a variable of type X, it will always be able to cast only a type X object, unless you don't declare it at all (very bad practice, you would get errors after).

Upvotes: 1

Related Questions