Check condition of cell value before populating combobo list

[ipbr21054]

Hi,
I have the code supplied in use but would like to use it after a cell condition "if the correct word" is met.

The code populates a drop down list on a combobox.
The values at present are all used & are placed in combobox list.
I would like to now use only values that are NOT hyperlinked.
I couldnt see when searching if a hyperlink was applied etc so maybe the code could look at it being underlined or its blue font color the excel gives it as a workaround ?

Example
Values 1-8 ARE NOT hyperlinked BUT values 9 & 10 ARE so the code will only use values 1-8 for population.

Example code to use
If value is hyperlinked then ????

or

If value is underlined then ????

or

If value is excel blue font color then ??

Thanks

    With GetObject("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm") 'THIS POPULATES THE DROPDOWN FOR THE CUSTOMERS NAMES
        Set s = .Sheets("POSTAGE")
        Set b2 = s.Cells(s.Range("B" & s.UsedRange.Rows.Count + 1).End(xlUp).Row, "B")
        Set b1 = b2.Offset(-10, 0) 'THE MINUS VALUE IS HOW MANY NAMES TO BE SHOWN IN DROP DOWN LIST
        ComboBox1.List = s.Range(b1, b2).Value
       .Close 0
End With

 

[NoSparks]

Declare cel as range so you don't get a variable not defind error

and replace this

        ComboBox1.List = s.Range(b1, b2).Value

with this for no hyperlink

        For Each cel In s.Range(b1, b2)
            If cel.Hyperlinks.Count = 0 Then ComboBox1.AddItem cel.Value
        Next cel

 

[ipbr21054]

Perfect thanks just what i needed,works well

Have a nice day