Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should feature a highlighted row/column cursor that scrolls
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
Pivottable feature(?) in Excel 2003 SP-1 | Excel Discussion (Misc queries) | |||
Highlight 1 cell in Excel & move mouse-stays highlighted - stop? | Excel Discussion (Misc queries) | |||
MS Excel should have MS word feature. | Setting up and Configuration of Excel | |||
Excel Installing feature | Excel Discussion (Misc queries) |