|
|
|
|
Do you mean Excel 97 and up?
--
HTH
Bob Phillips
"Jim May" wrote in message
news:hgyre.50319$Fv.19841@lakeread01...
Perfect Bob,
Will all this (as far as you know) work on Access 97 and up?
Jim
"Bob Phillips" wrote in message
...
Wrap-around again Jim. use this
Standard code module
Public Sub SetupHilite()
Dim hilite As Boolean
With ActiveWorkbook
hilite = False
On Error Resume Next
hilite = Evaluate(.Names(ActiveSheet.Name & _
"!__Hilite").RefersTo)
On Error GoTo 0
ActiveSheet.Cells.FormatConditions.Delete
.Names.Add Name:=ActiveSheet.Name & "!__Hilite", _
RefersTo:=Not hilite
.Names(ActiveSheet.Name & "!__Hilite").Visible = False
End With
End Sub
--
HTH
Bob Phillips
"Jim May" wrote in message
news:%Vqre.49277$Fv.43119@lakeread01...
Thanks for the code:
When I click on the Icon (after loading everything) the code "bombs"
and
highlights the word hilite, within ...:=Not hilite.Names(... below..
"!__Hilite", RefersTo:=Not hilite.Names(ActiveSheet.Name &
"!__Hilite").Visible = False
End With
End Sub
"Bob Phillips" wrote in message
...
Okay Jim,
There are 3 parts to this.
The first part is just a simple piece of code that creates a toolbar
button
(I am appending it to the format toolbar).
The second part is to setup application events, and trap the
application
sheet selectionchange event and do our highlighting.
These first two parts are both included in the ThisWorkbook module,
and
should be included in Personal.xls.
The 3rd part is the macro that is run when the button is clicked,
and
either
turns highlighting on the active sheet on or off (by use of a
worksheet
scope name). SO cliciking once sets highlighting on that page, a
second
time
removes it (it could be shift-click to remove if preferred).This
goes
in
a
standard code module, also in Personal.xls.
BTW, in my version that I installed with some colleagues, we had row
and
column highlighting, but only for 5 cells either side. Would you
like
this
version?
ThisWorkbook
Option Explicit
Public WithEvents App As Application
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal
Target
As
Range)
Dim hilite As Boolean
hilite = False
On Error Resume Next
hilite = Evaluate(Sh.Parent.Names(Sh.Name &
"!__Hilite").RefersTo)
On Error GoTo 0
If hilite Then
Sh.Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression,
Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = 3
.Font.Bold = True
End With
End With
End If
End Sub
Private Sub Workbook_Open()
Dim oCtl As CommandBarControl
Set App = Application
On Error Resume Next
Application.CommandBars("Formatting").Controls("Hi lite").Delete
On Error GoTo 0
With Application.CommandBars("Formatting")
Set oCtl = .Controls.Add(Type:=msoControlButton,
Temporary:=True)
oCtl.Caption = "Hilite"
oCtl.Style = msoButtonIconAndCaption
oCtl.FaceId = 340
oCtl.OnAction = "SetupHilite"
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Formatting").Controls("Hi lite").Delete
On Error GoTo 0
End Sub
Standard code module
Public Sub SetupHilite()
Dim hilite As Boolean
With ActiveWorkbook
hilite = False
On Error Resume Next
hilite = Evaluate(.Names(ActiveSheet.Name &
"!__Hilite").RefersTo)
On Error GoTo 0
ActiveSheet.Cells.FormatConditions.Delete
.Names.Add Name:=ActiveSheet.Name & "!__Hilite",
RefersTo:=Not
hilite
.Names(ActiveSheet.Name & "!__Hilite").Visible = False
End With
End Sub
--
HTH
Bob Phillips
"Jim May" wrote in message
news:8nnre.49257$Fv.13329@lakeread01...
Do you mean that you would like to have it apply to the
activesheet
on
demand? YES
and what about if we already have that
event. SHOULD NOT EVER
What I would do is add application events, and setup a sheet
dependent
name
that is set/unset by the button. HOW IS THIS DONE?
Jim
"Bob Phillips" wrote in message
...
Jim,
Do you mean that you would like to have it apply to the
activesheet
on
demand? Problem here is that it is event code so we need to
setup
that
event, remove it on say shift toggle, and what about if we
already
have
that
event.
What I would do is add application events, and setup a sheet
dependent
name
that is set/unset by the button.
Sound good?
--
HTH
Bob Phillips
"Jim May" wrote in message
news:qGere.49221$Fv.38665@lakeread01...
Cool Bob,,
I'd like to have this macro available (ON CALL) to apply to
numerous
workbooks/worksheets I work with. How would I go about doing
this?
Could I put a new button on my toolbar to run the macro;
and have some code in my personal.xls; maybe some type
of toggle (On/Off) feature...?? of course applying to the
Activesheet..
TIA,
Jim
"Bob Phillips" wrote in message
...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression,
Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = 3
.Font.Bold = True
End With
End With
End Sub
'This is worksheet event code, which means that it needs to
be
'placed in the appropriate worksheet code module, not a
standard
'code module. To do this, right-click on the sheet tab,
select
'the View Code option from the menu, and paste the code in.
--
HTH
Bob Phillips
"gwenturpin"
wrote
in message
...
I am wanting to change the default highlighting when you
hover
over
a
cell/row.
E.g. I will be working on a large list of contact details
when
for
instance I click on a surname I would like the whole row
to
jump
out
e.g be in a white box with bold letters coloured red.
Any ideas????
--
gwenturpin
------------------------------------------------------------------------
gwenturpin's Profile:
http://www.excelforum.com/member.php...o&userid=24239
View this thread:
http://www.excelforum.com/showthread...hreadid=378497
|