Remove Hyperlinks Without Changing background shading

[JCO]

I have many workbooks and sheets that I need to remove the hyperlinks from.
When doing the code below, it removes all formatting.

Sub RemoveHyperlinks()
Selection.Hyperlinks().Delete
End Sub

The issue is that it changes all the fonts boarders and many of the cells
have different color background shadings (fill). So I turned the "Record
Macro" on and was able to modify the Macro by reapplying the font name, font
size, font color, cell boarders.

Sub RemoveHyperlinksOnly()
Selection.Hyperlinks().Delete

' Set new font name
--code--
' Set new font size
--code--
' Set new font color
--code--
' Set boarders
--code--
End Sub


However, the "Format Cell>Fill>Color" of many cells are Green, Red, Yellow &
Pink

I know I can use format painter and do these separately but I have many of
these to do on an ongoing basis. The Excel Workbooks are given to me so I
have to make the changes to 8-12 sheets out of 15 sheets total. I need a
way to add to my existing macro to take a cell and do the following:

Sub RemoveHyperlinksOnly()
1 store existing "Format Cell>Fill>Color" in a variable
2 do my existing macro
3 change "Format Cell>Fill>Color" to the variable that was stored
End Sub

Any help is appreciated. I suspect it is easy to do but I don't deal with
macros much.

 

[Claus Busch]

Hi,

Am Mon, 25 Feb 2013 14:00:40 -0600 schrieb JCO:

I have many workbooks and sheets that I need to remove the hyperlinks from.
When doing the code below, it removes all formatting.

remove only hyperlink address:

Sub Test()
Dim hyp As Hyperlink

With ActiveSheet
For Each hyp In .Hyperlinks
hyp.Address = ""
Next
End With
End Sub


Regards
Claus Busch

 

[JCO]

That does seen simpler, however, it is not removing the hyperlink. It
simply sets the hyperlink to null.
It still looks and acts like a hyperlink. I guess I need to change the font
color and take off the underline, however how do I stop the mouse-over from
still reacting to it as a hyperlink (mouse changes and the balloon shows the
null link).

 

[Claus Busch]

hi,

Am Mon, 25 Feb 2013 14:44:09 -0600 schrieb JCO:

That does seen simpler, however, it is not removing the hyperlink. It
simply sets the hyperlink to null.
It still looks and acts like a hyperlink. I guess I need to change the font
color and take off the underline, however how do I stop the mouse-over from
still reacting to it as a hyperlink (mouse changes and the balloon shows the
null link).

copy the format of the cell e.g. to Z1, delete hyperlink and copy back
the format:

Sub Test()
Dim hyp As Hyperlink
Dim Adr As String

Application.ScreenUpdating = False
With ActiveSheet
For Each hyp In .Hyperlinks
Adr = hyp.Parent.Address
hyp.Parent.Copy
Range("Z1").PasteSpecial xlPasteFormats
hyp.Delete
Range("Z1").Copy
Range(Adr).PasteSpecial xlPasteFormats
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus Busch

 

[JCO]

Not quite working. Can you tell me how to Dim the items below. Example

Dim sFontName As Variant
Dim iFontSize as Integer
Dim sFontColor As Variant
....
code
....
With Selection.Font
.Name = sFontName
.Size = sFontSize
.ColorIndex = sFontColor
End With

 

[GS]

After serious thinking JCO wrote :

Not quite working. Can you tell me how to Dim the items below. Example

Dim sFontName As Variant
Dim iFontSize as Integer
Dim sFontColor As Variant
...
code
...
With Selection.Font
.Name = sFontName
.Size = sFontSize
.ColorIndex = sFontColor
End With

It's a good idea to establish for yourself a *consistent* variable
naming discipline so as to obviate any confusion resulting from a
non-discipline approach. For example, you declared "iFontSize" as type
"integer" but your code uses "sFontSize" as the value to assign to
fontsize.

The very 1st thing you want to do is to set variable declaration as a
requirement in the *Code Settings* section on the *Editor* tab of the
*Options* dialog.

Now you will have the following statement appear at the top of code
windows when you create new code for the 1st time...

Option Explicit

...because the VB IDE will auto-insert this for you. You will, however,
have to add it manually in any code window that already has code.

Now, let's review your declares...

Dim vFontName, vFontSize, vFontColorNdx

...which are all type "Variant" as per the ObjectBrowser description of
these Font properties. Note the prefix I used is "v" to indicate they
are type "Variant". Note also that VBA types these as Variant because
type was not specified.

Now that we have variables to use, we need to assign values to them...

vFontName = "Arial": vFontSize = 10: vFontColorNdx = 34

Note that I appended "Ndx" to vFontColor so it's clear that we're
setting the ColorIndex property as opposed to the Color property.

Now we can assign the values stored in the variables...

With Selection.Font
.Name = vFontName: .Size = vFontSize: .ColorIndex = vFontColorNdx
End With 'Selection.Font


Alternative approach:
You could declare these as constants if they never change...

Const vFontName As Variant = "Arial"
Const vFontSize As Variant = 10
Const vFontColorNdx As Variant = 34

...which saves some extra coding doing it the other way.<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[CellShocked]

That does seen simpler, however, it is not removing the hyperlink. It
simply sets the hyperlink to null.
It still looks and acts like a hyperlink. I guess I need to change the font
color and take off the underline, however how do I stop the mouse-over from
still reacting to it as a hyperlink (mouse changes and the balloon shows the
null link).

Copy and paste the entire column/row, and only paste the formats.

Clear the hyperlinks, then paste the formats back to the original
column/row.

 

[CellShocked]

After serious thinking JCO wrote :

It's a good idea to establish for yourself a *consistent* variable
naming discipline so as to obviate any confusion resulting from a
non-discipline approach. For example, you declared "iFontSize" as type
"integer" but your code uses "sFontSize" as the value to assign to
fontsize.

The very 1st thing you want to do is to set variable declaration as a
requirement in the *Code Settings* section on the *Editor* tab of the
*Options* dialog.

Now you will have the following statement appear at the top of code
windows when you create new code for the 1st time...

Option Explicit

..because the VB IDE will auto-insert this for you. You will, however,
have to add it manually in any code window that already has code.

Now, let's review your declares...

Dim vFontName, vFontSize, vFontColorNdx

..which are all type "Variant" as per the ObjectBrowser description of
these Font properties. Note the prefix I used is "v" to indicate they
are type "Variant". Note also that VBA types these as Variant because
type was not specified.

Now that we have variables to use, we need to assign values to them...

vFontName = "Arial": vFontSize = 10: vFontColorNdx = 34

Note that I appended "Ndx" to vFontColor so it's clear that we're
setting the ColorIndex property as opposed to the Color property.

Now we can assign the values stored in the variables...

With Selection.Font
.Name = vFontName: .Size = vFontSize: .ColorIndex = vFontColorNdx
End With 'Selection.Font


Alternative approach:
You could declare these as constants if they never change...

Const vFontName As Variant = "Arial"
Const vFontSize As Variant = 10
Const vFontColorNdx As Variant = 34

..which saves some extra coding doing it the other way.<g>

Great post on variables.


How do I change the default link font and color, and how do I change
the default "link" hover text box formatting?

Declare it at the start of a workbook development?

 

[GS]

CellShocked used his keyboard to write :

How do I change the default link font and color, and how do I change
the default "link" hover text box formatting?

Declare it at the start of a workbook development?

Edit/modify *Styles*.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[JCO]

That is great and I understand. However, I forgot about the cell background
fill.

Dim vCellBackGroundFill
vCellBackGroundFill = RGB(50, 100, 150)

Also, how did you get the number 34 for for the color index?
My font name is: "Trebuchet MS"
This seems to be an issue for some reason?

is this correct?
Thanks

"CellShocked" wrote in message

After serious thinking JCO wrote :

It's a good idea to establish for yourself a *consistent* variable
naming discipline so as to obviate any confusion resulting from a
non-discipline approach. For example, you declared "iFontSize" as type
"integer" but your code uses "sFontSize" as the value to assign to
fontsize.

The very 1st thing you want to do is to set variable declaration as a
requirement in the *Code Settings* section on the *Editor* tab of the
*Options* dialog.

Now you will have the following statement appear at the top of code
windows when you create new code for the 1st time...

Option Explicit

..because the VB IDE will auto-insert this for you. You will, however,
have to add it manually in any code window that already has code.

Now, let's review your declares...

Dim vFontName, vFontSize, vFontColorNdx

..which are all type "Variant" as per the ObjectBrowser description of
these Font properties. Note the prefix I used is "v" to indicate they
are type "Variant". Note also that VBA types these as Variant because
type was not specified.

Now that we have variables to use, we need to assign values to them...

vFontName = "Arial": vFontSize = 10: vFontColorNdx = 34

Note that I appended "Ndx" to vFontColor so it's clear that we're
setting the ColorIndex property as opposed to the Color property.

Now we can assign the values stored in the variables...

With Selection.Font
.Name = vFontName: .Size = vFontSize: .ColorIndex = vFontColorNdx
End With 'Selection.Font


Alternative approach:
You could declare these as constants if they never change...

Const vFontName As Variant = "Arial"
Const vFontSize As Variant = 10
Const vFontColorNdx As Variant = 34

..which saves some extra coding doing it the other way.<g>

Great post on variables.


How do I change the default link font and color, and how do I change
the default "link" hover text box formatting?

Declare it at the start of a workbook development?

 

[JCO]

if you start by coping the cell format, you will get the wrong format
because it will be blue and underlined. So you have to copy the existing
format, make changes to the Font, Size, & Color first. Then remove the
Hyperlink, then copy the NEW format as changed. This is what I need to do.

I can't get anything to accept the vFontName as "Trebuchet MS"
Thanks

"CellShocked" wrote in message

That does seen simpler, however, it is not removing the hyperlink. It
simply sets the hyperlink to null.
It still looks and acts like a hyperlink. I guess I need to change the
font
color and take off the underline, however how do I stop the mouse-over from
still reacting to it as a hyperlink (mouse changes and the balloon shows
the
null link).

Copy and paste the entire column/row, and only paste the formats.

Clear the hyperlinks, then paste the formats back to the original
column/row.

 

[GS]

JCO presented the following explanation :

That is great and I understand. However, I forgot about the cell background
fill.

Dim vCellBackGroundFill
vCellBackGroundFill = RGB(50, 100, 150)

Also, how did you get the number 34 for for the color index?

Standard colors have indexes based on the color picker. This is
different than RGB color assignments. I don't think '34' is what you
want; it was just an example to illustrate variable naming to reflect

My font name is: "Trebuchet MS"
This seems to be an issue for some reason?

I have no idea about that. You can't specify fonts that don't exist
(ie: correctly installed in Windows)! This...

Selection.Font.Name = "Trebuchet MS"

OR
Const sFontname As String = "Trebuchet MS"
Selection.Font.Name = sFontname

...works for me!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Also working...

Const vFontname As Variant = "Trebuchet MS"
Selection.Font.Name = vFontname

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[JCO]

Yes it works. My mistake because I had the following also and it overwrote
the font name
With Selection Font
....
....
.ThemeColor = xlThemeColorLight1
.ThemeFont = xlThemeFontMinor

After commenting the last two line out, it began to work fine.

Things I still need to do:
1. Store the existing cell background fill?
Do work
2. Restore the fill to the cell background?
Appreciate the help

"GS" wrote in message
Also working...

Const vFontname As Variant = "Trebuchet MS"
Selection.Font.Name = vFontname

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[JCO]

' Change Cell Background Color
'
I figured out how to restore the background fill color as shown below but by
doing this, it inadvertently removes the gridlines. You can set the
background fill to "No Color" to get this back, but that defeats the whole
purpose of restoring the background fill color in the first place. Any help
is appreciated.

'other variables here
' .........
Dim vCellBackgroundFill As Variant 'store color
Dim vCellBackgroundNoFill As Variant 'background grid

vCellBackgroundFill = Selection.Interior.Color

' Do other work here ....

With Selection.Interior
.Color = vCellBackgroundFill
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Thanks

"GS" wrote in message
Also working...

Const vFontname As Variant = "Trebuchet MS"
Selection.Font.Name = vFontname

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion