CODEWITHSUNDEEP
excelexcel-2010vba
Andy Ardueser
Andy Ardueser

Reputation: 17

"Object required" error VBA

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

Upvotes: 0

Views: 770

Answers (1)

sous2817
sous2817

Reputation: 3960

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

Upvotes: 1

Related Questions