Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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 CellFillColor" 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 CellFillColor" in a variable 2 do my existing macro 3 change "Format CellFillColor" 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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" wrote in message ... 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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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 "Claus Busch" wrote in message ... 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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
On Mon, 25 Feb 2013 14:44:09 -0600, "JCO" wrote:
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
On Mon, 25 Feb 2013 20:43:41 -0500, GS wrote:
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 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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 ... On Mon, 25 Feb 2013 20:43:41 -0500, GS wrote: 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 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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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 ... On Mon, 25 Feb 2013 14:44:09 -0600, "JCO" wrote: 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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 data type.<g 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Hyperlinks Without Changing background shading
' 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 bank ledger spreadsheet background fill shading | Excel Worksheet Functions | |||
How do I fix the background shading by position? | Excel Discussion (Misc queries) | |||
Excel Background Shading | Excel Discussion (Misc queries) | |||
Restoring Excel gridlines after removing background shading? | Excel Discussion (Misc queries) | |||
Background Cell shading colors | Excel Programming |