MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

E2000 Events

  Asked By: Michael    Date: Oct 01    Category: MS Office    Views: 1216

I was interested in an earlier question about highlighting the current

I got the code below from the recorder but I can't find an "event" for
*before* the selection changes to wipe out the current highlighting
before highlighting the new selection... Any offers???


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 3
End With
End Sub



5 Answers Found

Answer #1    Answered By: Ula Hashmi     Answered On: Oct 01

I'm not exactly sure what you're asking, but....

You can remove highlighting  using the following line:

Selection.Interior.ColorIndex = xlNone

Answer #2    Answered By: Horace Hernandez     Answered On: Oct 01

what I was looking for definately is a way of removing
selection highlighting, but I want to put this in an event. Like... in
access for example, there is a before update and and after update
event. I'm looking for something similar for changing the selection  so
I can remove highlighting  in the current  selection... then the
selection changes... then I can highlight the new current selection.

Answer #3    Answered By: Hasad Yilmaz     Answered On: Oct 01

Have you looked in Excel help?

BeforeUpdate Event

Occurs before data in a control is changed.


Private Sub object_BeforeUpdate( ByVal Cancel As MSForms.ReturnBoolean)

The BeforeUpdate event  syntax has these parts:

Part Description

object Required. A valid object.

Cancel Required. Event status. False indicates that the control should
handle the event (default). True cancels the update and indicates the
application should handle the event.


The BeforeUpdate event occurs regardless of whether the control is bound
(that is, when the RowSource property specifies a data source for the
control). This event occurs before the AfterUpdate and Exit events  for
the control (and before the Enter event for the next control that
receives focus).

If you set the Cancel argument to True, the focus remains on the control
and neither the AfterUpdate event nor the Exit event occurs.

Answer #4    Answered By: Zakary Thompson     Answered On: Oct 01

That's for a control on a form or sheet
rather than a selection  on a sheet.... but it *is* what I'm looking
for! I've been trawling the net as well but I thought someone here may

Answer #5    Answered By: Konrada Schmidt     Answered On: Oct 01

One way is to clear all of the same type of highlighting  from the
whole sheet everytime the selection  changes, just before you apply
your highlighting.
This code  illustrates this using a different method to highlight the
currently selected cell(s):

Private Sub Worksheet_SelectionChange(ByVal target  As Range)
Cells.Interior.ColorIndex = xlNone
Selection.EntireRow.Interior.ColorIndex = 15
Selection.EntireColumn.Interior.ColorIndex = 15
End Sub

Didn't find what you were looking for? Find more on E2000 Events Or get search suggestion and latest updates.