Code assistance please

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

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,
 
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
 
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 said:
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 said:
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,
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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,, --
Click to expand...
how
can
I achieve my goal?


TIA,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top