Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Indiana Jay
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Excel should feature a highlighted row/column cursor that scrolls

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Excel should feature a highlighted row/column cursor that scro

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" wrote in message
...
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 wrote in message
...
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







  #6   Report Post  
Posted to microsoft.public.excel.misc
Indiana Jay
 
Posts: n/a
Default Excel should feature a highlighted row/column cursor that scro

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 naked 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" wrote in message
...
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 wrote in message
...
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






  #7   Report Post  
Posted to microsoft.public.excel.misc
Indiana Jay
 
Posts: n/a
Default HELP!: Excel should feature a highlighted row/column cursor

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" wrote in message
...
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 wrote in message
...
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






  #8   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Excel should feature a highlighted row/column cursor that scro

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" wrote in message
...
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 naked 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" wrote in message
...
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 wrote in

message
...
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








  #9   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default HELP!: Excel should feature a highlighted row/column cursor

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" wrote in message
...
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" wrote in message
...
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 wrote in

message
...
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








  #10   Report Post  
Posted to microsoft.public.excel.misc
Indiana Jay
 
Posts: n/a
Default ONCE MORE!: Excel should feature a highlighted row/column cursor

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" wrote in message
...
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" wrote in message
...
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 wrote in

message
...
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











  #11   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default ONCE MORE!: Excel should feature a highlighted row/column cursor

Jay,

Comments inline.

"Indiana Jay" wrote in message
...

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.


  #12   Report Post  
Posted to microsoft.public.excel.misc
Indiana Jay
 
Posts: n/a
Default DE-ACTIVATE: Excel should feature a highlighted row/column curs

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" wrote in message
...

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.



  #13   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default DE-ACTIVATE: Excel should feature a highlighted row/column curs


"Indiana Jay" wrote in message
...
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.


  #14   Report Post  
Posted to microsoft.public.excel.misc
Indiana Jay
 
Posts: n/a
Default DE-ACTIVATE: Excel should feature a highlighted row/column cur

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" wrote in message
...
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.



  #15   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default DE-ACTIVATE: Excel should feature a highlighted row/column cur

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" wrote in message
...
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" wrote in message
...
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.







  #16   Report Post  
Posted to microsoft.public.excel.misc
Indiana Jay
 
Posts: n/a
Default Simple De-Activate

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" wrote in message
...
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" wrote in message
...
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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
unhide menu bar in excel - just disappeared Sean Setting up and Configuration of Excel 12 April 4th 23 10:19 AM
Pivottable feature(?) in Excel 2003 SP-1 Greg Goodman Excel Discussion (Misc queries) 10 July 31st 06 01:20 PM
Highlight 1 cell in Excel & move mouse-stays highlighted - stop? Patriot Excel Discussion (Misc queries) 2 January 16th 06 11:49 PM
MS Excel should have MS word feature. Usman Setting up and Configuration of Excel 2 November 24th 05 10:01 PM
Excel Installing feature Ams Excel Discussion (Misc queries) 1 December 30th 04 10:29 PM


All times are GMT +1. The time now is 05:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"