0

I'm trying to create a form using Excel and VBA. I get an error saying "Run-time error 91: Object Variable or With block variable not set when I try to submit the form. When I debug the error is occurring at line 7 iRow= ws.Cell... Any help is very much appreciated! Below is the code for clicking the OK button: 

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

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

'check for a Name number
If Trim(Me.TextBox_Name.Value) = "" Then
Me.TextBox_Name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox_Name.Value
ws.Cells(iRow, 2).Value = Me.TextBox_Desc.Value
ws.Cells(iRow, 3).Value = Me.Frame_Group.Value
ws.Cells(iRow, 4).Value = Me.ComboBox_Location.Value
ws.Cells(iRow, 5).Value = Me.Frame_Time.Value
ws.Cells(iRow, 6).Value = Me.Frame_Life.Value

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.TextBox_Name.Value = ""
Me.TextBox_Desc.Value = ""
Me.Frame_Group.Value = ""
Me.ComboBox_Location.Value = ""
Me.Frame_Time.Value = ""
Me.Frame_Life.Value = ""
Me.TextBox_Name.SetFocus
End Sub
Shevliaskovic
  • 1,562
  • 4
  • 26
  • 43
user2976972
  • 33
  • 2
  • 6

1 Answers1

0
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

replace your line with these code Its works :)

Eshan Chattaraj
  • 368
  • 1
  • 6
  • 19