"Object required" error VBA

Question

I've got a Worksheet titled "Survey". I'm trying to attach checkboxes to all of the cells in column A that are next to answers, and for some reason I'm getting an "object required" error. The 4 lines near the beginning, starting with "Set rng =", are highlighted.

I'm pretty new to VBA, so I'm not sure if this is just a simple syntax issue that I'm not seeing. I've tried searching for the proper format, to no avail. Can anyone help? Here's the code that I've got:

Sub AddCheckBox()

Dim rng As Range
Dim rcell As Range

Set rng = Survey.Range("A7:A10,A13:A17,A21:A25,A28:A33" _
& "A36:A43, A48,A51:A56,A60:A66,A69:A73,A76:A80" _
& "A83:A87, A90:A94, A97:A102, A105:A113, A116:A122, A125:A131" _
& "A134:A141,A145:A149, A152:A158, A161:A165")



DelCheckBox 

For Each rcell In rng
  With ActiveSheet.CheckBoxes.Add(rcell.Left, _
     rcell.Top, rcell.Width, rcell.Height)
     .LinkedCell = rcell.Offset(, 0).Address(External:=True)
     .Interior.ColorIndex = 14   'or  xlNone or xlAutomatic
     .Caption = ""
     .Border.Weight = xlThin
  End With
Next

With Range("A7:A10,A13:A17,A21:A25,A28:A33" _
& "A36:A43, A48,A51:A56,A60:A66,A69:A73,A76:A80" _
& "A83:A87, A90:A94, A97:A102, A105:A113, A116:A122, A125:A131" _
& "A134:A141,A145:A149, A152:A158, A161:A165")
.Rows.RowHeight = 15
End With
End Sub


Sub DelCheckBox()
For Each cell In Range("A1:A166")
    Worksheets("Survey").CheckBoxes.Delete
Next
End Sub

sous2817 · Accepted Answer · 2013-06-19 17:21:23Z

1

You're missing the commas at the end of your lines. Try this:

Set rng = Survey.Range("A7:A10,A13:A17,A21:A25,A28:A33," _
& "A36:A43, A48,A51:A56,A60:A66,A69:A73,A76:A80," _
& "A83:A87, A90:A94, A97:A102, A105:A113, A116:A122, A125:A131," _
& "A134:A141,A145:A149, A152:A158, A161:A165")

Note, you'll have to make the same change where you have the With Range("....") block as well. Also, the above code does not reflect the validity of the rest of what you're trying to do...just that one error.

EDIT to fix issues down the road...

Try this all of this code and see if it does what you're after:

 Sub test()

Dim rng As Range
Dim rcell As Range

Set rng = Sheets("Survey").Range("A7:A10,A13:A17,A21:A25,A28:A33," _
& "A36:A43, A48,A51:A56,A60:A66,A69:A73,A76:A80," _
& "A83:A87, A90:A94, A97:A102, A105:A113, A116:A122, A125:A131," _
& "A134:A141,A145:A149, A152:A158, A161:A165")

DelCheckBox

For Each rcell In rng
  With Sheets("Survey").CheckBoxes.Add(rcell.Left, _
     rcell.Top, rcell.Width, rcell.Height)
     .LinkedCell = rcell.Offset(, 0).Address(External:=True)
     .Interior.ColorIndex = 14   'or  xlNone or xlAutomatic
     .Caption = ""
     .Border.Weight = xlThin
  End With
Next

rng.Rows.RowHeight = 15
End Sub
Sub DelCheckBox()

Sheets("Survey").DrawingObjects.Delete

End Sub

edited Jun 19, 2013 at 17:21

answered Jun 19, 2013 at 16:44

sous2817

3,9602 gold badges37 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

scott Over a year ago

why set rng and then not use it in the with statement? could easily be one line: survey.rng.rows.rowheight = 15

sous2817 Over a year ago

@scott because I just quickly corrected the other issues in the code. Didn't really rewrite the whole thing. It's been corrected now...

Andy Ardueser Over a year ago

I'm still getting the Run-time error '424 Object required after copy/pasting your code into a new module.

sous2817 Over a year ago

Then you're doing it wrong :) Is the sheet named "Survey" (like the actual tab name) or did you go in to the IDE and change the codename of your sheet to Survey?

Andy Ardueser Over a year ago

The tab name is "Survey". EDIT: clarification

|

Collectives™ on Stack Overflow

"Object required" error VBA

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related