Code assistance please
 

Down Within a given code block I have:

With ws
.[a1] = Sheets(1).Name
.[a1].Font.ColorIndex = 2 '<<< *******
.[a1].Hyperlinks.Add _
Anchor:=.Cells(1, 1), _
Address:="", _
SubAddress:="'" & Sheets(1).Name & "'!A1"
End With

Note line 3 '<<< ******* where I'm trying to "White-Out" the hyperlink text
(make invisible) from showing,
Yet after running code the text in a1 is still blue and visible,, -- how can
I achieve my goal?


TIA,

Code assistance please
 

just change the font after you create the hyperlink:

Sub tester9()
Set ws = ActiveSheet
With ws
.[a1] = Sheets(1).Name
.[a1].Hyperlinks.Add _
Anchor:=.Cells(1, 1), _
Address:="", _
SubAddress:="'" & Sheets(1).Name & "'!A1"
.[a1].Font.ColorIndex = 2 '<<< *******

End With

End Sub


--
Regards,
Tom Ogilvy


"JMay" wrote in message
news:iIk_c.38375$wu.7190@okepread04...
Down Within a given code block I have:

With ws
.[a1] = Sheets(1).Name
.[a1].Font.ColorIndex = 2 '<<< *******
.[a1].Hyperlinks.Add _
Anchor:=.Cells(1, 1), _
Address:="", _
SubAddress:="'" & Sheets(1).Name & "'!A1"
End With

Note line 3 '<<< ******* where I'm trying to "White-Out" the hyperlink
text
(make invisible) from showing,
Yet after running code the text in a1 is still blue and visible,, -- how
can
I achieve my goal?


TIA,

Code assistance please
 

Tom,
thanks a heep;
The overall macro is run periodically to account for and include any new
ws's added.
I need also to include in the macro the freezepane feature (on each ws) so
that cell A1 is always visible,
say while you are entering data in say Cell G212..
Using ActiveWindow.FreezePanes=True within the With statement
doesn't seem like it would work since the code isn't actually traveling
through each ws in an ActiveWindow manner, so I've tried the .ScrollArea
property
but without success -- Any addition hints you could suggest here?
Hope you have a great Labor Day weekend.
Jim

"Tom Ogilvy" wrote in message
...
just change the font after you create the hyperlink:

Sub tester9()
Set ws = ActiveSheet
With ws
.[a1] = Sheets(1).Name
.[a1].Hyperlinks.Add _
Anchor:=.Cells(1, 1), _
Address:="", _
SubAddress:="'" & Sheets(1).Name & "'!A1"
.[a1].Font.ColorIndex = 2 '<<< *******

End With

End Sub


--
Regards,
Tom Ogilvy


"JMay" wrote in message
news:iIk_c.38375$wu.7190@okepread04...
Down Within a given code block I have:

With ws
.[a1] = Sheets(1).Name
.[a1].Font.ColorIndex = 2 '<<< *******
.[a1].Hyperlinks.Add _
Anchor:=.Cells(1, 1), _
Address:="", _
SubAddress:="'" & Sheets(1).Name & "'!A1"
End With

Note line 3 '<<< ******* where I'm trying to "White-Out" the hyperlink
text
(make invisible) from showing,
Yet after running code the text in a1 is still blue and visible,, -- how
can
I achieve my goal?


TIA,

Code assistance please
 

You have to activate the sheets to apply freezepanes because it only works
against the activecell

Application.ScreenUpdating = False
for each ws in thisworkbook.worksheets
With ws
if ws.Name < Sheets(1).Name then
.Activate
.range("B2").Select
Activewindow.FreezePanes = True
.[a1] = Sheets(1).Name
.[a1].Hyperlinks.Add _
Anchor:=.Cells(1, 1), _
Address:="", _
SubAddress:="'" & Sheets(1).Name & "'!A1"
.[a1].Font.ColorIndex = 2 '<<< *******
End if
End With
Next
Application.ScreenUpdating = True

have a good labor day weekend as well.
--
Regards,
Tom Ogilvy


"JMay" wrote in message
news:_Pl_c.38376$wu.35607@okepread04...
Tom,
thanks a heep;
The overall macro is run periodically to account for and include any new
ws's added.
I need also to include in the macro the freezepane feature (on each ws) so
that cell A1 is always visible,
say while you are entering data in say Cell G212..
Using ActiveWindow.FreezePanes=True within the With statement
doesn't seem like it would work since the code isn't actually traveling
through each ws in an ActiveWindow manner, so I've tried the .ScrollArea
property
but without success -- Any addition hints you could suggest here?
Hope you have a great Labor Day weekend.
Jim

"Tom Ogilvy" wrote in message
...
just change the font after you create the hyperlink:

Sub tester9()
Set ws = ActiveSheet
With ws
.[a1] = Sheets(1).Name
.[a1].Hyperlinks.Add _
Anchor:=.Cells(1, 1), _
Address:="", _
SubAddress:="'" & Sheets(1).Name & "'!A1"
.[a1].Font.ColorIndex = 2 '<<< *******

End With

End Sub


--
Regards,
Tom Ogilvy


"JMay" wrote in message
news:iIk_c.38375$wu.7190@okepread04...
Down Within a given code block I have:

With ws
.[a1] = Sheets(1).Name
.[a1].Font.ColorIndex = 2 '<<< *******
.[a1].Hyperlinks.Add _
Anchor:=.Cells(1, 1), _
Address:="", _
SubAddress:="'" & Sheets(1).Name & "'!A1"
End With

Note line 3 '<<< ******* where I'm trying to "White-Out" the hyperlink
text
(make invisible) from showing,
Yet after running code the text in a1 is still blue and visible,, --
how
can
I achieve my goal?


TIA,