Runtime 424 "Object required" error

gers1978 · Oct 28th 2015

I have some code and one of the subs contains this line:

Code

Do Until Sheet1.Range("A" & Row).Value = vbNullString

It works flawlessly.

However I now want to run this code on a different sheet, namely one called "Invoice". So I changed the code to:

Code

Do Until Invoice.Range("A" & Row).Value = vbNullString

However now when I run it, I get a runtime 424 error, "Object Required".

If I change it to:

Code

Do Until Worksheets("Invoice").Range("A" & Row).Value = vbNullString

again, it works fine.

Can anyone tell me what's going on, and why sometimes you need to use "Worksheets("<name>")" format and sometimes not?

Thanks!

Also posted here: http://www.mrexcel.com/forum/e…ed-error.html#post4324572

**KjBox** · Oct 28th 2015

Re: Runtime 424 "Object required" error

Sheet1 is the CodeName of Sheets("Sheet1")

If you make Invoice the CodeName of the sheet named "Invoice" then the code that errors will work.

To change the CodeName open the VB Editor, click the "Invoice" worksheet in the Project window then change the first field ("(Name)") in the Properties window and press Enter.

It is better practice to use sheet CodeNames rather than sheet names in VBA because even if the sheet name gets changed or its position in the workbook changes the code will still refer to the correct worksheet. Whereas, if Sheets("Invoice") or Sheets(2) was used in the code then they would need changing if the name of the Invoice sheet was changed or its position in worksheet order changed.

AlexAsks · Jul 11th 2016

Re: Runtime 424 "Object required" error

I get the "Object required" error when trying to run the following clickbutton macro in a different madule.
Otherwise, the button works perfectly fine.

Public Sub Plus_Click()
Dim str As String
'insert a new row below the cell "Below"
NewRow "Below"

'trying to set str as the range name of a specific cell using Offset function
'str = Region_1

str = Range("Below").Offset(-2, 1).name.name
'Name a cell as Region_2
ActiveWorkbook.Names.Add name:="Region_" & Right(str, 1) + 1, RefersTo:=Range("Below").Offset(-1, 1)
End Sub

Can I get some help?

Thanks!

**Luke M** · Jul 11th 2016

Re: Runtime 424 "Object required" error

Hi Alex,

Rather than tack onto an older thread someone else started, please start your own thread. You could copy and paste the above here:
http://www.ozgrid.com/forum/newthread.php?do=newthread&f=8

This will help ensure that each thread has it's own unique question/answer.
I'm closing this thread, as original question was answered last year. Thanks!

Runtime 424 "Object required" error

Participate now!

Share