Macro delete ComboBox if LinkedCell = #REF!

[Will85]

I need a macro that will go through my active sheet and delete all comboboxes that have a linked cell value of #REF !.

Thank you in advance

 

[Yongle]

oops - deleted by yongle

 

[Yongle]

This works for active-x comboboxes
(icon to insert Form Control combobox is greyed out these days)

Sub LoopComboBoxes()
    Dim OleObj As OLEObject, cel As Range
    For Each OleObj In ActiveSheet.OLEObjects
        If OleObj.progID = "Forms.ComboBox.1" Then
            On Error Resume Next
            Set cel = Range(OleObj.LinkedCell)
            If IsError(cel) Then
                If cel.Value = CVErr(xlErrRef) Then OleObj.Delete
            End If
            Set cel = Nothing
        End If
    Next OleObj
End Sub

And some useful bedtime reading http://www.cpearson.com/excel/ReturningErrors.aspx

 

[Yongle]

correction to above post - it is the ComboBox Edit button that is greyed out

NON active-x comboboxes

This may work for Form Control combo boxes
- I never use these but they appear to be named "Drop Down 1" etc by default
- check to see how they are named (by right-clicking on them) and amend the condition if necessary

Sub LoopShapes()
    Dim shp As Shape, cel As Range
    For Each shp In ActiveSheet.Shapes
        On Error Resume Next
        If Left(shp.Name, 9) = "[COLOR=#000080]Drop Down[/COLOR]" Then
            Set cel = Range(shp.ControlFormat.LinkedCell)
            If IsError(cel) Then
                If cel.Value = CVErr(xlErrRef) Then shp.Delete
            End If
            Set cel = Nothing
        End If
    Next
End Sub

 

[Will85]

correction to above post - it is the ComboBox Edit button that is greyed out

NON active-x comboboxes

This may work for Form Control combo boxes
- I never use these but they appear to be named "Drop Down 1" etc by default
- check to see how they are named (by right-clicking on them) and amend the condition if necessary

Sub LoopShapes()
    Dim shp As Shape, cel As Range
    For Each shp In ActiveSheet.Shapes
        On Error Resume Next
        If Left(shp.Name, 9) = "[COLOR=#000080]Drop Down[/COLOR]" Then
            Set cel = Range(shp.ControlFormat.LinkedCell)
            If IsError(cel) Then
                If cel.Value = CVErr(xlErrRef) Then shp.Delete
            End If
            Set cel = Nothing
        End If
    Next
End Sub

Unfortunatley it did not work. My comboboxes are active x. I thought the shp.name should be 8 maybe?

 

[Yongle]

Code in post#3 works for active-x comboboxes

(correction was only on note re Form Control Comboboxes )

 

[Will85]

Code in post#3 works for active-x comboboxes

(correction was only on note re Form Control Comboboxes )

Still doesnt work.

I created a new workbook. Put 1,2,3 as values in Cells A1, A2, A3. Created an activex combobox and set the list fill range to A1:A3 as a test.

I set the linked cell to A25, and exited design mode. Confirmed that when making a selection in the combobox the linked cell works. Then I deleted row 25. Entered design mode, checked the combobox linked cell and saw that it correctly displays #REF !

I ran your macro, and nothing happened. The combobox is still displayed on the worksheet.

Thank you for any additional help!

 

[Yongle]

Hopefully this does what you want

Sub LoopComboBoxes()
    Dim OleObj As OLEObject
    For Each OleObj In ActiveSheet.OLEObjects
        With OleObj
            If .progID = "Forms.ComboBox.1" And .LinkedCell = "#REF!" Then OleObj.Delete
        End With
    Next OleObj
End Sub

 

[Will85]

This appears to be exactly what I need. thank you!

 

[Yongle]

thanks for feedback