Excel should feature a highlighted row/column cursor that scrolls

I would like to highlight a row or column with my cursor and have it scroll
as I move my cursor so that I can see what items are in the same row/column
that my cursor is in.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

maybe....

you may want to try Chip Pearson's Rowliner:
http://www.cpearson.com/excel/RowLiner.htm


Indiana Jay wrote:
>
> I would like to highlight a row or column with my cursor and have it scroll
> as I move my cursor so that I can see what items are in the same row/column
> that my cursor is in.
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...lic.excel.misc

--

Dave Peterson

This has been previously posted here http://tinyurl.com/bhhnr

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Indiana Jay" <Indiana [email protected]> wrote in message
news:[email protected]...
> I would like to highlight a row or column with my cursor and have it
scroll
> as I move my cursor so that I can see what items are in the same
row/column
> that my cursor is in.
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow
this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
>
http://www.microsoft.com/office/comm...id=50c21b19-ae
38-4a89-a0a9-b17ffcf73a30&dg=microsoft.public.excel.misc

I don't want some workaround macro, I want the feature built in.

"Bob Phillips" wrote:

> This has been previously posted here http://tinyurl.com/bhhnr
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Indiana Jay" <Indiana [email protected]> wrote in message
> news:[email protected]...
> > I would like to highlight a row or column with my cursor and have it
> scroll
> > as I move my cursor so that I can see what items are in the same
> row/column
> > that my cursor is in.
> >
> > ----------------
> > This post is a suggestion for Microsoft, and Microsoft responds to the
> > suggestions with the most votes. To vote for this suggestion, click the "I
> > Agree" button in the message pane. If you do not see the button, follow
> this
> > link to open the suggestion in the Microsoft Web-based Newsreader and then
> > click "I Agree" in the message pane.
> >
> >
> http://www.microsoft.com/office/comm...id=50c21b19-ae
> 38-4a89-a0a9-b17ffcf73a30&dg=microsoft.public.excel.misc
>
>
>

Well I hope you get your wish and sorry for trying to help.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Indiana Jay" <[email protected]> wrote in message
news:[email protected]...
> I don't want some workaround macro, I want the feature built in.
>
> "Bob Phillips" wrote:
>
> > This has been previously posted here http://tinyurl.com/bhhnr
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "Indiana Jay" <Indiana [email protected]> wrote in message
> > news:[email protected]...
> > > I would like to highlight a row or column with my cursor and have it
> > scroll
> > > as I move my cursor so that I can see what items are in the same
> > row/column
> > > that my cursor is in.
> > >
> > > ----------------
> > > This post is a suggestion for Microsoft, and Microsoft responds to the
> > > suggestions with the most votes. To vote for this suggestion, click
the "I
> > > Agree" button in the message pane. If you do not see the button,
follow
> > this
> > > link to open the suggestion in the Microsoft Web-based Newsreader and
then
> > > click "I Agree" in the message pane.
> > >
> > >
> >
http://www.microsoft.com/office/comm...id=50c21b19-ae
> > 38-4a89-a0a9-b17ffcf73a30&dg=microsoft.public.excel.misc
> >
> >
> >

Thanks Bob for helping, I didn't mean for my response to sound ungrateful,
it's just that my intent was to suggest to Microsoft that this would be a
very useful feature for all to have.

I believe many people would use this feature, who have to work with large
amounts of data that spreads across a page, and can be difficult to follow
across with the ***** eye.

I would like to turn on my cursor highlighting bar the same way I freeze
panes, or split screens. In fact, know that I think of it, the "highlighted
cursor bar" feature would be appropriately added to the Window menu.

All the best,

Jay



"Bob Phillips" wrote:

> Well I hope you get your wish and sorry for trying to help.
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Indiana Jay" <[email protected]> wrote in message
> news:[email protected]...
> > I don't want some workaround macro, I want the feature built in.
> >
> > "Bob Phillips" wrote:
> >
> > > This has been previously posted here http://tinyurl.com/bhhnr
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from the email address if mailing direct)
> > >
> > > "Indiana Jay" <Indiana [email protected]> wrote in message
> > > news:[email protected]...
> > > > I would like to highlight a row or column with my cursor and have it
> > > scroll
> > > > as I move my cursor so that I can see what items are in the same
> > > row/column
> > > > that my cursor is in.
> > > >
> > > > ----------------
> > > > This post is a suggestion for Microsoft, and Microsoft responds to the
> > > > suggestions with the most votes. To vote for this suggestion, click
> the "I
> > > > Agree" button in the message pane. If you do not see the button,
> follow
> > > this
> > > > link to open the suggestion in the Microsoft Web-based Newsreader and
> then
> > > > click "I Agree" in the message pane.
> > > >
> > > >
> > >
> http://www.microsoft.com/office/comm...id=50c21b19-ae
> > > 38-4a89-a0a9-b17ffcf73a30&dg=microsoft.public.excel.misc
> > >
> > >
> > >
>
>
>

Dear Bob,

I tried this at work this morning and it was excellent! I really appreciate
the tip. However, as I do not use macros much and am not familiar with event
codes, when I went to print the highlight showed on the printout. ):

Is there a way to turn this off for printing purposes?

Thanks,

Jay






"Bob Phillips" wrote:

> Well I hope you get your wish and sorry for trying to help.
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Indiana Jay" <[email protected]> wrote in message
> news:[email protected]...
> > I don't want some workaround macro, I want the feature built in.
> >
> > "Bob Phillips" wrote:
> >
> > > This has been previously posted here http://tinyurl.com/bhhnr
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from the email address if mailing direct)
> > >
> > > "Indiana Jay" <Indiana [email protected]> wrote in message
> > > news:[email protected]...
> > > > I would like to highlight a row or column with my cursor and have it
> > > scroll
> > > > as I move my cursor so that I can see what items are in the same
> > > row/column
> > > > that my cursor is in.
> > > >
> > > > ----------------
> > > > This post is a suggestion for Microsoft, and Microsoft responds to the
> > > > suggestions with the most votes. To vote for this suggestion, click
> the "I
> > > > Agree" button in the message pane. If you do not see the button,
> follow
> > > this
> > > > link to open the suggestion in the Microsoft Web-based Newsreader and
> then
> > > > click "I Agree" in the message pane.
> > > >
> > > >
> > >
> http://www.microsoft.com/office/comm...id=50c21b19-ae
> > > 38-4a89-a0a9-b17ffcf73a30&dg=microsoft.public.excel.misc
> > >
> > >
> > >
>
>
>

Jay,

We see the notice at the foot that says that it is a recommendation for MS
(and presumably they do take notice of these?), but we tend to assume that
although the poster is putting forward a suggestion, they would still like a
solution, at least until it does become a built-in.

I absolutely agree it would be useful, as proven by Chip developing his
add-in, and the number of times I have offered my solution to posters, but I
am not so sure is up there in MS's priorities.

Regards

Bob

"Indiana Jay" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob for helping, I didn't mean for my response to sound ungrateful,
> it's just that my intent was to suggest to Microsoft that this would be a
> very useful feature for all to have.
>
> I believe many people would use this feature, who have to work with large
> amounts of data that spreads across a page, and can be difficult to follow
> across with the ***** eye.
>
> I would like to turn on my cursor highlighting bar the same way I freeze
> panes, or split screens. In fact, know that I think of it, the
"highlighted
> cursor bar" feature would be appropriately added to the Window menu.
>
> All the best,
>
> Jay
>
>
>
> "Bob Phillips" wrote:
>
> > Well I hope you get your wish and sorry for trying to help.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "Indiana Jay" <[email protected]> wrote in message
> > news:[email protected]...
> > > I don't want some workaround macro, I want the feature built in.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > This has been previously posted here http://tinyurl.com/bhhnr
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > > "Indiana Jay" <Indiana [email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > I would like to highlight a row or column with my cursor and have
it
> > > > scroll
> > > > > as I move my cursor so that I can see what items are in the same
> > > > row/column
> > > > > that my cursor is in.
> > > > >
> > > > > ----------------
> > > > > This post is a suggestion for Microsoft, and Microsoft responds to
the
> > > > > suggestions with the most votes. To vote for this suggestion,
click
> > the "I
> > > > > Agree" button in the message pane. If you do not see the button,
> > follow
> > > > this
> > > > > link to open the suggestion in the Microsoft Web-based Newsreader
and
> > then
> > > > > click "I Agree" in the message pane.
> > > > >
> > > > >
> > > >
> >
http://www.microsoft.com/office/comm...id=50c21b19-ae
> > > > 38-4a89-a0a9-b17ffcf73a30&dg=microsoft.public.excel.misc
> > > >
> > > >
> > > >
> >
> >
> >

Yes that should be possible.

Change the highlighting code to this

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
ThisWorkbook.Names.Add "'" & Me.Name & "'!_HiLite", True

Cells.FormatConditions.Delete
With Target
With .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 = 20
End With
End With
With .EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
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.


and add this printing code

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim hilite As Boolean
With ActiveSheet
hilite = False
On Error Resume Next
hilite = Evaluate(.Names(ActiveSheet.Name & _
"!__Hilite").RefersTo)
On Error GoTo 0
If hilite Then
.FormatConditions.Delete
End If
End With
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Indiana Jay" <[email protected]> wrote in message
news:[email protected]...
> Dear Bob,
>
> I tried this at work this morning and it was excellent! I really
appreciate
> the tip. However, as I do not use macros much and am not familiar with
event
> codes, when I went to print the highlight showed on the printout. ):
>
> Is there a way to turn this off for printing purposes?
>
> Thanks,
>
> Jay
>
>
>
>
>
>
> "Bob Phillips" wrote:
>
> > Well I hope you get your wish and sorry for trying to help.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "Indiana Jay" <[email protected]> wrote in message
> > news:[email protected]...
> > > I don't want some workaround macro, I want the feature built in.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > This has been previously posted here http://tinyurl.com/bhhnr
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > > "Indiana Jay" <Indiana [email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > I would like to highlight a row or column with my cursor and have
it
> > > > scroll
> > > > > as I move my cursor so that I can see what items are in the same
> > > > row/column
> > > > > that my cursor is in.
> > > > >
> > > > > ----------------
> > > > > This post is a suggestion for Microsoft, and Microsoft responds to
the
> > > > > suggestions with the most votes. To vote for this suggestion,
click
> > the "I
> > > > > Agree" button in the message pane. If you do not see the button,
> > follow
> > > > this
> > > > > link to open the suggestion in the Microsoft Web-based Newsreader
and
> > then
> > > > > click "I Agree" in the message pane.
> > > > >
> > > > >
> > > >
> >
http://www.microsoft.com/office/comm...id=50c21b19-ae
> > > > 38-4a89-a0a9-b17ffcf73a30&dg=microsoft.public.excel.misc
> > > >
> > > >
> > > >
> >
> >
> >

Bob,

If I can presume upon you one more time, and you have been so good.
Sorry, but I am new to this area of visual basic event codes.

When I pasted in the original code, I saved it, exited, and the highlight
bar showed up. I did not name it, and I do not know how to turn the feature
off. So,

1) I'm not sure where I would put the "printing code". I am assuming I
append it to the same code I pasted? And do I activate it as well?

2) Can I just "De-Activate" the first code so that I can use the worksheet
normally without the highlighted bar when I want to? Otherwise, the highlight
is always on once I activated it. I do not always want to see it, but I am
unsure what the event code needs to De-Activate. When I select that option it
seems to give me some sort of template that is looking for a new subroutine?
--------------------------------------------------------------
Private Sub Worksheet_Deactivate()

End Sub
--------------------------------------------------------------

Thanks for your patience with me,

Jay

P.S. wouldn't this be a great option to turn on and off, like "gridlines" in
the Tools/Options "View" tab, and Window Options area



"Bob Phillips" wrote:

> Yes that should be possible.
>
> Change the highlighting code to this
>
> '----------------------------------------------------------------
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> '----------------------------------------------------------------
> ThisWorkbook.Names.Add "'" & Me.Name & "'!_HiLite", True
>
> Cells.FormatConditions.Delete
> With Target
> With .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 = 20
> End With
> End With
> With .EntireColumn
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> With .FormatConditions(1)
> With .Borders(xlLeft)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = 5
> End With
> With .Borders(xlRight)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = 5
> End With
> .Interior.ColorIndex = 20
> End With
> End With
>
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> .FormatConditions(1).Interior.ColorIndex = 36
> 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.
>
>
> and add this printing code
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> Dim hilite As Boolean
> With ActiveSheet
> hilite = False
> On Error Resume Next
> hilite = Evaluate(.Names(ActiveSheet.Name & _
> "!__Hilite").RefersTo)
> On Error GoTo 0
> If hilite Then
> .FormatConditions.Delete
> End If
> End With
> End Sub
>
>
> This is workbook event code.
> To input this code, right click on the Excel icon on the worksheet
> (or next to the File menu if you maximise your workbooks),
> select View Code from the menu, and paste the code
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Indiana Jay" <[email protected]> wrote in message
> news:[email protected]...
> > Dear Bob,
> >
> > I tried this at work this morning and it was excellent! I really
> appreciate
> > the tip. However, as I do not use macros much and am not familiar with
> event
> > codes, when I went to print the highlight showed on the printout. ):
> >
> > Is there a way to turn this off for printing purposes?
> >
> > Thanks,
> >
> > Jay
> >
> >
> >
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Well I hope you get your wish and sorry for trying to help.
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from the email address if mailing direct)
> > >
> > > "Indiana Jay" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I don't want some workaround macro, I want the feature built in.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > This has been previously posted here http://tinyurl.com/bhhnr
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > > "Indiana Jay" <Indiana [email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > I would like to highlight a row or column with my cursor and have
> it
> > > > > scroll
> > > > > > as I move my cursor so that I can see what items are in the same
> > > > > row/column
> > > > > > that my cursor is in.
> > > > > >
> > > > > > ----------------
> > > > > > This post is a suggestion for Microsoft, and Microsoft responds to
> the
> > > > > > suggestions with the most votes. To vote for this suggestion,
> click
> > > the "I
> > > > > > Agree" button in the message pane. If you do not see the button,
> > > follow
> > > > > this
> > > > > > link to open the suggestion in the Microsoft Web-based Newsreader
> and
> > > then
> > > > > > click "I Agree" in the message pane.
> > > > > >
> > > > > >
> > > > >
> > >
> http://www.microsoft.com/office/comm...id=50c21b19-ae
> > > > > 38-4a89-a0a9-b17ffcf73a30&dg=microsoft.public.excel.misc
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Jay,

Comments inline.

"Indiana Jay" <[email protected]> wrote in message
news:[email protected]...

> When I pasted in the original code, I saved it, exited, and the highlight
> bar showed up. I did not name it, and I do not know how to turn the
feature
> off. So,
>
> 1) I'm not sure where I would put the "printing code". I am assuming I
> append it to the same code I pasted? And do I activate it as well?


I added a note at the end on where to put that de-activate code. I'll repeat
it here

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code


> 2) Can I just "De-Activate" the first code so that I can use the worksheet
> normally without the highlighted bar when I want to? Otherwise, the
highlight
> is always on once I activated it. I do not always want to see it, but I am
> unsure what the event code needs to De-Activate. When I select that option
it
> seems to give me some sort of template that is looking for a new
subroutine?

I like your idea of a button. Send me your workbook and I will create it for
you.

Bob,

I'm not sure I can send this workbook with financial data from the company
I'm doing contract accounting work for in Boston, but isn't there a way to
De-Activate the code by reversing the steps the code does when it is
activated?

This is really what I need. And I now understand that the printing codce
belongs with the workbook, not the worksheet. I did not read it carefully
enough.

By the way, what do you do for work? You seem to know this stuff pretty well.

Thanks,

Jay

"Bob Phillips" wrote:

> Jay,
>
> Comments inline.
>
> "Indiana Jay" <[email protected]> wrote in message
> news:[email protected]...
>
> > When I pasted in the original code, I saved it, exited, and the highlight
> > bar showed up. I did not name it, and I do not know how to turn the
> feature
> > off. So,
> >
> > 1) I'm not sure where I would put the "printing code". I am assuming I
> > append it to the same code I pasted? And do I activate it as well?
>
>
> I added a note at the end on where to put that de-activate code. I'll repeat
> it here
>
> This is workbook event code.
> To input this code, right click on the Excel icon on the worksheet
> (or next to the File menu if you maximise your workbooks),
> select View Code from the menu, and paste the code
>
>
> > 2) Can I just "De-Activate" the first code so that I can use the worksheet
> > normally without the highlighted bar when I want to? Otherwise, the
> highlight
> > is always on once I activated it. I do not always want to see it, but I am
> > unsure what the event code needs to De-Activate. When I select that option
> it
> > seems to give me some sort of template that is looking for a new
> subroutine?
>
> I like your idea of a button. Send me your workbook and I will create it for
> you.
>
>
>

"Indiana Jay" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> I'm not sure I can send this workbook with financial data from the company
> I'm doing contract accounting work for in Boston, but isn't there a way to
> De-Activate the code by reversing the steps the code does when it is
> activated?


That is the principle of how you would do it. But your idea of a toggle is
eminently sensible, and I think the way to go. Could you not strip the
confidential data out, even if it mean the data worksheets. I don't need to
see the data, just have your workbook to add the toggle.


> This is really what I need. And I now understand that the printing codce
> belongs with the workbook, not the worksheet. I did not read it carefully
> enough.


That''s good news.


> By the way, what do you do for work? You seem to know this stuff pretty
well.


I'm in IT, I'm a design consultant. I do a bit of Excel, Office, VBA, VB
work, but it is not my primary occupation.

Bob,

Thanks for your help. And I like the idea of a toggle for Microsoft, but I
like better for me right now the ability to paste in the code as I need for
multiple workbooks.

I was thinking I could paste in both the activate and de-activate codes
together on each sheet I wanted to use the feature on. Otherwise, I can only
have this toggle button for the sheet you do it to.

Does that make sense? This way, it seems very straightforward and I can
share the feature with others. And we can use it only on the worksheets we
want to, and by simply de-activating get the sheets back to their untouched,
native form.

It must be pretty simple (for you, not for me) to un-do your macro with one
that puts everything back to the default setting. Is there a way to get back
to the default settings with a "de-activate" code for now?

You're too good to me,

Jay

--------------------------------------------------------------------------------------------------------


"Bob Phillips" wrote:

>
> "Indiana Jay" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> >
> > I'm not sure I can send this workbook with financial data from the company
> > I'm doing contract accounting work for in Boston, but isn't there a way to
> > De-Activate the code by reversing the steps the code does when it is
> > activated?
>
>
> That is the principle of how you would do it. But your idea of a toggle is
> eminently sensible, and I think the way to go. Could you not strip the
> confidential data out, even if it mean the data worksheets. I don't need to
> see the data, just have your workbook to add the toggle.
>
>
> > This is really what I need. And I now understand that the printing codce
> > belongs with the workbook, not the worksheet. I did not read it carefully
> > enough.
>
>
> That''s good news.
>
>
> > By the way, what do you do for work? You seem to know this stuff pretty
> well.
>
>
> I'm in IT, I'm a design consultant. I do a bit of Excel, Office, VBA, VB
> work, but it is not my primary occupation.
>
>
>

Jay,

Okay, how about this?

This solution provides a toolbar to switch highlighting on and off for every
sheet in the workbook. It allows setting highlighting, and then setting row
and column highlighting individually. The button tooltiptext shows whether
it is set or not, so you can easily check (although it is quite obvious with
the colours <G>)

There is quite a bit of code. The first bit is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Hiliter").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()

On Error Resume Next
Application.CommandBars("Hiliter").Delete
On Error GoTo 0

With Application.CommandBars
With .Add(Name:="Hiliter", temporary:=True)

With .Controls.Add(Type:=msoControlButton)
.Caption = "Hiliter"
.Style = msoButtonCaption
End With

Set ocHiliter = .Controls.Add(Type:=msoControlButton)
With ocHiliter
.BeginGroup = True
.FaceId = 20
.Tag = "Hiliter"
.OnAction = "setHiliter"
End With

Set ocHiliterRow = .Controls.Add(Type:=msoControlButton)
With ocHiliterRow
.FaceId = 1652
.Tag = "Row"
.OnAction = "setHiliter"
End With

Set ocHiliterCol = .Controls.Add(Type:=msoControlButton)
With ocHiliterCol
.FaceId = 1650
.Tag = "Column"
.OnAction = "setHiliter"
End With

.Visible = True

End With

End With

CheckHiliterNames

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
CheckHiliterNames
Hilite Sh, ActiveCell
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

Hilite Sh, Target

End Sub


The next bit goes in a standard code module

Option Explicit
Option Private Module


Public fHiliter As Boolean
Public fRowHiliter As Boolean
Public fColHiliter As Boolean

Public ocHiliter As CommandBarControl
Public ocHiliterRow As CommandBarControl
Public ocHiliterCol As CommandBarControl


Private Sub SetHiliter()

With ThisWorkbook

Select Case Application.CommandBars.ActionControl.Tag

Case "Hiliter":
fHiliter = Not fHiliter
.Names.Add Name:=.ActiveSheet.Name & _
"!__Hilite", RefersTo:=fHiliter
.Names.Add Name:=.ActiveSheet.Name & _
"!__HiliteRow", RefersTo:=fHiliter
.Names.Add Name:=.ActiveSheet.Name & _
"!__HiliteCol", RefersTo:=fHiliter

Case "Row":
fRowHiliter = Not fRowHiliter
.Names.Add Name:=.ActiveSheet.Name & _
"!__HiliteRow", RefersTo:=fRowHiliter

Case "Column":
fColHiliter = Not fColHiliter
.Names.Add Name:=.ActiveSheet.Name & _
"!__HiliteCol", RefersTo:=fColHiliter

End Select

End With

CheckHiliterNames
Hilite ActiveSheet, ActiveCell

End Sub

Public Sub Hilite(ByVal Sh As Object, ByVal Target As Range)

Sh.Cells.FormatConditions.Delete

If fHiliter Then


With Target

If fRowHiliter Then

With .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 = 20
End With
End With

End If 'fRowHiliter

If fColHiliter Then

With .EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With

End If 'fColHiliter

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36

End With

End If
End Sub

Public Sub CheckHiliterNames()
Dim sButtonSuffix As String

With ThisWorkbook

On Error Resume Next
fHiliter = Evaluate(.Names(.ActiveSheet.Name & _
"!__Hilite").RefersTo)
If Err.Number <> 0 Then
.Names.Add Name:=.ActiveSheet.Name & "!__Hilite",
RefersTo:=fHiliter
.Names.Add Name:=.ActiveSheet.Name & "!__HiliteRow",
RefersTo:=fHiliter
.Names.Add Name:=.ActiveSheet.Name & "!__HiliteCol",
RefersTo:=fHiliter
End If
On Error GoTo 0
sButtonSuffix = IIf(fHiliter, "Set", "Not set")
ocHiliter.Caption = "Toggle highlighting - " & sButtonSuffix

On Error Resume Next
fRowHiliter = Evaluate(.Names(.ActiveSheet.Name & _
"!__HiliteRow").RefersTo)
If Err.Number <> 0 Then
.Names.Add Name:=.ActiveSheet.Name & "!__HiliteRow",
RefersTo:=fRowHiliter
End If
On Error GoTo 0
sButtonSuffix = IIf(fRowHiliter, "Set", "Not set")
ocHiliterRow.Caption = "Row Hiliter - " & sButtonSuffix

On Error Resume Next
fColHiliter = Evaluate(.Names(.ActiveSheet.Name & _
"!__HiliteCol").RefersTo)
If Err.Number <> 0 Then
.Names.Add Name:=.ActiveSheet.Name & "!__HiliteCol",
RefersTo:=fColHiliter
End If
On Error GoTo 0
sButtonSuffix = IIf(fColHiliter, "Set", "Not set")
ocHiliterCol.Caption = "Column Hiliter - " & sButtonSuffix

.Names(.ActiveSheet.Name & "!__Hilite").Visible = False
.Names(.ActiveSheet.Name & "!__HiliteRow").Visible = False
.Names(.ActiveSheet.Name & "!__HiliteCol").Visible = False

End With

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Indiana Jay" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Thanks for your help. And I like the idea of a toggle for Microsoft, but I
> like better for me right now the ability to paste in the code as I need
for
> multiple workbooks.
>
> I was thinking I could paste in both the activate and de-activate codes
> together on each sheet I wanted to use the feature on. Otherwise, I can
only
> have this toggle button for the sheet you do it to.
>
> Does that make sense? This way, it seems very straightforward and I can
> share the feature with others. And we can use it only on the worksheets we
> want to, and by simply de-activating get the sheets back to their
untouched,
> native form.
>
> It must be pretty simple (for you, not for me) to un-do your macro with
one
> that puts everything back to the default setting. Is there a way to get
back
> to the default settings with a "de-activate" code for now?
>
> You're too good to me,
>
> Jay
>
> --------------------------------------------------------------------------
------------------------------
>
>
> "Bob Phillips" wrote:
>
> >
> > "Indiana Jay" <[email protected]> wrote in message
> > news:[email protected]...
> > > Bob,
> > >
> > > I'm not sure I can send this workbook with financial data from the
company
> > > I'm doing contract accounting work for in Boston, but isn't there a
way to
> > > De-Activate the code by reversing the steps the code does when it is
> > > activated?
> >
> >
> > That is the principle of how you would do it. But your idea of a toggle
is
> > eminently sensible, and I think the way to go. Could you not strip the
> > confidential data out, even if it mean the data worksheets. I don't need
to
> > see the data, just have your workbook to add the toggle.
> >
> >
> > > This is really what I need. And I now understand that the printing
codce
> > > belongs with the workbook, not the worksheet. I did not read it
carefully
> > > enough.
> >
> >
> > That''s good news.
> >
> >
> > > By the way, what do you do for work? You seem to know this stuff
pretty
> > well.
> >
> >
> > I'm in IT, I'm a design consultant. I do a bit of Excel, Office, VBA, VB
> > work, but it is not my primary occupation.
> >
> >
> >

Bob,

I know I have pestered you too much already, and for that I am sorry. but
I'm unsure why you are not able to give me a "simple" de-activate script
which I could paste in each worksheet with the original script.

In my naivete I keep hoping to see a script as short as the original, which
you would instruct me to paste only into the worksheets I want to use it in.

Then, I would have two small lines of code which I could easily paste into
whatever sheets I want to use it in--and by the way, I took out the last few
lines so that I only use the highlighted row portion, and leave off the
column and cell highlighting.

What do you think? Is there a simple script that matches the original on
line by line and just returns the cursor to the default setting when I choose
the "de-activate" option?

I will try the full-fledged workbook solution on my own at home.

All the best,

Jay



"Bob Phillips" wrote:

> Jay,
>
> Okay, how about this?
>
> This solution provides a toolbar to switch highlighting on and off for every
> sheet in the workbook. It allows setting highlighting, and then setting row
> and column highlighting individually. The button tooltiptext shows whether
> it is set or not, so you can easily check (although it is quite obvious with
> the colours <G>)
>
> There is quite a bit of code. The first bit is workbook event code.
> To input this code, right click on the Excel icon on the worksheet
> (or next to the File menu if you maximise your workbooks),
> select View Code from the menu, and paste the code
>
> Option Explicit
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> On Error Resume Next
> Application.CommandBars("Hiliter").Delete
> On Error GoTo 0
> End Sub
>
> Private Sub Workbook_Open()
>
> On Error Resume Next
> Application.CommandBars("Hiliter").Delete
> On Error GoTo 0
>
> With Application.CommandBars
> With .Add(Name:="Hiliter", temporary:=True)
>
> With .Controls.Add(Type:=msoControlButton)
> .Caption = "Hiliter"
> .Style = msoButtonCaption
> End With
>
> Set ocHiliter = .Controls.Add(Type:=msoControlButton)
> With ocHiliter
> .BeginGroup = True
> .FaceId = 20
> .Tag = "Hiliter"
> .OnAction = "setHiliter"
> End With
>
> Set ocHiliterRow = .Controls.Add(Type:=msoControlButton)
> With ocHiliterRow
> .FaceId = 1652
> .Tag = "Row"
> .OnAction = "setHiliter"
> End With
>
> Set ocHiliterCol = .Controls.Add(Type:=msoControlButton)
> With ocHiliterCol
> .FaceId = 1650
> .Tag = "Column"
> .OnAction = "setHiliter"
> End With
>
> .Visible = True
>
> End With
>
> End With
>
> CheckHiliterNames
>
> End Sub
>
> Private Sub Workbook_SheetActivate(ByVal Sh As Object)
> CheckHiliterNames
> Hilite Sh, ActiveCell
> End Sub
>
> Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
> As Range)
>
> Hilite Sh, Target
>
> End Sub
>
>
> The next bit goes in a standard code module
>
> Option Explicit
> Option Private Module
>
>
> Public fHiliter As Boolean
> Public fRowHiliter As Boolean
> Public fColHiliter As Boolean
>
> Public ocHiliter As CommandBarControl
> Public ocHiliterRow As CommandBarControl
> Public ocHiliterCol As CommandBarControl
>
>
> Private Sub SetHiliter()
>
> With ThisWorkbook
>
> Select Case Application.CommandBars.ActionControl.Tag
>
> Case "Hiliter":
> fHiliter = Not fHiliter
> .Names.Add Name:=.ActiveSheet.Name & _
> "!__Hilite", RefersTo:=fHiliter
> .Names.Add Name:=.ActiveSheet.Name & _
> "!__HiliteRow", RefersTo:=fHiliter
> .Names.Add Name:=.ActiveSheet.Name & _
> "!__HiliteCol", RefersTo:=fHiliter
>
> Case "Row":
> fRowHiliter = Not fRowHiliter
> .Names.Add Name:=.ActiveSheet.Name & _
> "!__HiliteRow", RefersTo:=fRowHiliter
>
> Case "Column":
> fColHiliter = Not fColHiliter
> .Names.Add Name:=.ActiveSheet.Name & _
> "!__HiliteCol", RefersTo:=fColHiliter
>
> End Select
>
> End With
>
> CheckHiliterNames
> Hilite ActiveSheet, ActiveCell
>
> End Sub
>
> Public Sub Hilite(ByVal Sh As Object, ByVal Target As Range)
>
> Sh.Cells.FormatConditions.Delete
>
> If fHiliter Then
>
>
> With Target
>
> If fRowHiliter Then
>
> With .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 = 20
> End With
> End With
>
> End If 'fRowHiliter
>
> If fColHiliter Then
>
> With .EntireColumn
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlExpression,
> Formula1:="TRUE"
> With .FormatConditions(1)
> With .Borders(xlLeft)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = 5
> End With
> With .Borders(xlRight)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = 5
> End With
> .Interior.ColorIndex = 20
> End With
> End With
>
> End If 'fColHiliter
>
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> .FormatConditions(1).Interior.ColorIndex = 36
>
> End With
>
> End If
> End Sub
>
> Public Sub CheckHiliterNames()
> Dim sButtonSuffix As String
>
> With ThisWorkbook
>
> On Error Resume Next
> fHiliter = Evaluate(.Names(.ActiveSheet.Name & _
> "!__Hilite").RefersTo)
> If Err.Number <> 0 Then
> .Names.Add Name:=.ActiveSheet.Name & "!__Hilite",
> RefersTo:=fHiliter
> .Names.Add Name:=.ActiveSheet.Name & "!__HiliteRow",
> RefersTo:=fHiliter
> .Names.Add Name:=.ActiveSheet.Name & "!__HiliteCol",
> RefersTo:=fHiliter
> End If
> On Error GoTo 0
> sButtonSuffix = IIf(fHiliter, "Set", "Not set")
> ocHiliter.Caption = "Toggle highlighting - " & sButtonSuffix
>
> On Error Resume Next
> fRowHiliter = Evaluate(.Names(.ActiveSheet.Name & _
> "!__HiliteRow").RefersTo)
> If Err.Number <> 0 Then
> .Names.Add Name:=.ActiveSheet.Name & "!__HiliteRow",
> RefersTo:=fRowHiliter
> End If
> On Error GoTo 0
> sButtonSuffix = IIf(fRowHiliter, "Set", "Not set")
> ocHiliterRow.Caption = "Row Hiliter - " & sButtonSuffix
>
> On Error Resume Next
> fColHiliter = Evaluate(.Names(.ActiveSheet.Name & _
> "!__HiliteCol").RefersTo)
> If Err.Number <> 0 Then
> .Names.Add Name:=.ActiveSheet.Name & "!__HiliteCol",
> RefersTo:=fColHiliter
> End If
> On Error GoTo 0
> sButtonSuffix = IIf(fColHiliter, "Set", "Not set")
> ocHiliterCol.Caption = "Column Hiliter - " & sButtonSuffix
>
> .Names(.ActiveSheet.Name & "!__Hilite").Visible = False
> .Names(.ActiveSheet.Name & "!__HiliteRow").Visible = False
> .Names(.ActiveSheet.Name & "!__HiliteCol").Visible = False
>
> End With
>
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Indiana Jay" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> >
> > Thanks for your help. And I like the idea of a toggle for Microsoft, but I
> > like better for me right now the ability to paste in the code as I need
> for
> > multiple workbooks.
> >
> > I was thinking I could paste in both the activate and de-activate codes
> > together on each sheet I wanted to use the feature on. Otherwise, I can
> only
> > have this toggle button for the sheet you do it to.
> >
> > Does that make sense? This way, it seems very straightforward and I can
> > share the feature with others. And we can use it only on the worksheets we
> > want to, and by simply de-activating get the sheets back to their
> untouched,
> > native form.
> >
> > It must be pretty simple (for you, not for me) to un-do your macro with
> one
> > that puts everything back to the default setting. Is there a way to get
> back
> > to the default settings with a "de-activate" code for now?
> >
> > You're too good to me,
> >
> > Jay
> >
> > --------------------------------------------------------------------------
> ------------------------------
> >
> >
> > "Bob Phillips" wrote:
> >
> > >
> > > "Indiana Jay" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Bob,
> > > >
> > > > I'm not sure I can send this workbook with financial data from the
> company
> > > > I'm doing contract accounting work for in Boston, but isn't there a
> way to
> > > > De-Activate the code by reversing the steps the code does when it is
> > > > activated?
> > >