Macro delete ComboBox if LinkedCell = #REF!

Will85

Board Regular
Joined
Apr 26, 2012
Messages
182
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
oops - deleted by yongle
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
This works for active-x comboboxes
(icon to insert Form Control combobox is greyed out these days)

Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
 
Last edited:

Will85

Board Regular
Joined
Apr 26, 2012
Messages
182

ADVERTISEMENT

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

Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Code in post#3 works for active-x comboboxes

(correction was only on note re Form Control Comboboxes ;) )
 
Last edited:

Will85

Board Regular
Joined
Apr 26, 2012
Messages
182

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Hopefully this does what you want

Code:
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
thanks for feedback (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,311
Messages
5,635,487
Members
416,860
Latest member
coen078

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top