Re: How unlink a whole column?

On Oct 13, 10:50=A0pm, ke...@[EMAIL PROTECTED]
 wrote:
> Bob, I have plenty of free space. Don't fixate on any particular file.
> Will all excel files, this is situation.
> You said there is not edit hyperlnk box, did you see screenshot. What I
a=
m only one to have a edit hyperlink box?
>
> You say there is only insert one? Where is the remove button?
> Send a screenshot with insert hyperlink with a remove button

Greetings,

I have the same experience on Excel 2004 on Mac OS X 10.5.5, that is
not getting the remove hyperlinks for multiple cells.  Since my
workflow at work includes linking items to sup****ting do***entation, I
created the following VB scripts to remove the hyperlinks.  Being a VB
script, this will not work with Excel 2008, but it might give you
enough to write an AppleScript that does the same thing.

There is a fair amount of fixing the formatting once the hyperlink is
removed (based on the formatting I use), but this should hopefully
help.  I don't know that I've tried this with an entire column
selected, maybe it works, if not just select cell 1 to n or use "all
sheets"

Sub RemoveAllHyperlinks()
    theResult =3D "Cancel"
    On Error Resume Next
    theResult =3D MacScript("display dialog ""Remove Hyperlinks from the
current Selection or ALL SHEETS of the active workbook?"" buttons
{""Cancel"", ""Selection"", ""ALL SHEETS""} default button 2 with icon
2")
    On Error GoTo 0
    If theResult =3D "Cancel" Then Exit Sub
    If theResult =3D "button returned:Selection" Then
        thisSheet =3D ActiveWorkbook.ActiveSheet.Name
        For Each h In Selection.Hyperlinks
            Call RemoveAllHyperlinks_Do(h, thisSheet)
        Next h
    ElseIf theResult =3D "button returned:ALL SHEETS" Then
      For Each sht In ActiveWorkbook.Sheets 'for every sheet in WB
            thisSheet =3D sht.Name
            For Each h In sht.Hyperlinks
                Call RemoveAllHyperlinks_Do(h, thisSheet)
            Next h
        Next sht
    End If
End Sub

Sub RemoveAllHyperlinks_Do(h, thisSheet)
                'Get the formatting to reapply because removing
hyperlink messes-up formatting besides color & underlining
                thisRange =3D h.Range.Address()
                theFontFace =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Font.FontStyle
                theNumFormat =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).NumberFormat
                theAlignment =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).HorizontalAlignment
                'theBorderFormat =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders.LineStyle
                theTopBorder =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlTop).LineStyle
                If theTopBorder <> xlLineStyleNone Then
theTopBorderWeight =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlTop).Weight
                theBottomBorder =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlBottom).LineSty=
le
                If theBottomBorder <> xlLineStyleNone Then
theBottomBorderWeight =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlBottom).Weight
                theLeftBorder =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlLeft).LineStyle
                If theLeftBorder <> xlLineStyleNone Then
theLeftBorderWeight =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlLeft).Weight
                theRightBorder =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlRight).LineStyl=
e
                If theRightBorder <> xlLineStyleNone Then
theRightBorderWeight =3D
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlRight).Weight
                h.Delete
=20
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Font.FontStyle =3D
theFontFace
=20
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).NumberFormat =3D
theNumFormat
=20
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).HorizontalAlignment
=3D theAlignment
                If theTopBorder <> xlLineStyleNone Then
=20
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlTop).LineStyle
=3D theTopBorder
=20
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlTop).Weight
=3D theTopBorderWeight
                End If
                If theBottomBorder <> xlLineStyleNone Then
=20
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlBottom).LineSty=
le
=3D theBottomBorder
=20
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlBottom).Weight
=3D theBottomBorderWeight
                End If
                If theLeftBorder <> xlLineStyleNone Then
=20
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlLeft).LineStyle
=3D theLeftBorder
=20
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlLeft).Weight
=3D theLeftBorderWeight
                End If
                If theRightBorder <> xlLineStyleNone Then
=20
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlRight).LineStyl=
e
=3D theRightBorder
=20
ActiveWorkbook.Sheets(thisSheet).Range(thisRange).Borders(xlRight).Weight
=3D theRightBorderWeight
                End If
End Sub