Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
hi
Private Sub Worksheet_SelectionChange(ByVal Target _ As Excel.Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.ColorIndex = 40 .EntireColumn.Interior.ColorIndex = 36 End With End Sub the above script works only single worksheet not all workbooks despite i made adds-in. Please advise that how can i able the above script to all excel sheets thanks in advacne AE |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
Copy the macro into the worksheet code area of each worksheet.
-- Gary''s Student - gsnu2007k |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
To Esssa and Gary''s Student...
Better would be to remove the code from the current SelectionChange event procedure and put it in the workbook's SheetSelectionChange event instead, then it would apply to every worksheet in the workbook. To Esssa... Take the code out of your current SelectionChange event, double click the ThisWorkbook entry in the Project Window on the left, choose Workbook from the code window's left hand drop down and SheetSelectionChange from its right hand drop down an place the code in there. -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Copy the macro into the worksheet code area of each worksheet. -- Gary''s Student - gsnu2007k |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
good idea
-- Gary''s Student - gsnu2007k "Rick Rothstein" wrote: To Esssa and Gary''s Student... Better would be to remove the code from the current SelectionChange event procedure and put it in the workbook's SheetSelectionChange event instead, then it would apply to every worksheet in the workbook. To Esssa... Take the code out of your current SelectionChange event, double click the ThisWorkbook entry in the Project Window on the left, choose Workbook from the code window's left hand drop down and SheetSelectionChange from its right hand drop down an place the code in there. -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Copy the macro into the worksheet code area of each worksheet. -- Gary''s Student - gsnu2007k |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
Rick
OP wants to have the code in an add-in to make it available for all open workbooks/sheets. Wouldn't that have to be done through Application Event in the add-in? I'm not sure how to achieve that. I've been browsing Chip's site but can't get anything to work with my limited skills. http://www.cpearson.com/excel/AppEvent.aspx Gord On Sat, 6 Sep 2008 11:16:10 -0400, "Rick Rothstein" wrote: To Esssa and Gary''s Student... Better would be to remove the code from the current SelectionChange event procedure and put it in the workbook's SheetSelectionChange event instead, then it would apply to every worksheet in the workbook. To Esssa... Take the code out of your current SelectionChange event, double click the ThisWorkbook entry in the Project Window on the left, choose Workbook from the code window's left hand drop down and SheetSelectionChange from its right hand drop down an place the code in there. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
I agree with you Gord (still not your wife, though <vbg)...
This code would go into the ThisWorkbook module of the addin (that's always opened when excel opens). Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) Target.Parent.Cells.Interior.ColorIndex = xlNone With Target .EntireRow.Interior.ColorIndex = 40 .EntireColumn.Interior.ColorIndex = 36 End With End Sub Personally, I wouldn't use this. It's not robust enough to clean up after itself and I wouldn't want to see any of my nicely formatted (pretty colors!) worksheets be screwed up by this. Instead, I'd try Chip Pearson's addin: http://www.cpearson.com/excel/RowLiner.htm I'm sure he's thought this stuff out way more than I have. Gord Dibben wrote: Rick OP wants to have the code in an add-in to make it available for all open workbooks/sheets. Wouldn't that have to be done through Application Event in the add-in? I'm not sure how to achieve that. I've been browsing Chip's site but can't get anything to work with my limited skills. http://www.cpearson.com/excel/AppEvent.aspx Gord On Sat, 6 Sep 2008 11:16:10 -0400, "Rick Rothstein" wrote: To Esssa and Gary''s Student... Better would be to remove the code from the current SelectionChange event procedure and put it in the workbook's SheetSelectionChange event instead, then it would apply to every worksheet in the workbook. To Esssa... Take the code out of your current SelectionChange event, double click the ThisWorkbook entry in the Project Window on the left, choose Workbook from the code window's left hand drop down and SheetSelectionChange from its right hand drop down an place the code in there. -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
Actually, I didn't read the OP's message... I only reacted to Gary''s
Student's reply to put the code in each worksheet's SelectionChange event (I figured he was responding to the OP's actual question) and realized that the workbook's SheetSelectionChange could be used more efficiently than that. As for doing this through an add-in... I agree with Dave that the OP should use the Chip's tried-and-true RowLiner Add-in instead. -- Rick (MVP - Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Rick OP wants to have the code in an add-in to make it available for all open workbooks/sheets. Wouldn't that have to be done through Application Event in the add-in? I'm not sure how to achieve that. I've been browsing Chip's site but can't get anything to work with my limited skills. http://www.cpearson.com/excel/AppEvent.aspx Gord On Sat, 6 Sep 2008 11:16:10 -0400, "Rick Rothstein" wrote: To Esssa and Gary''s Student... Better would be to remove the code from the current SelectionChange event procedure and put it in the workbook's SheetSelectionChange event instead, then it would apply to every worksheet in the workbook. To Esssa... Take the code out of your current SelectionChange event, double click the ThisWorkbook entry in the Project Window on the left, choose Workbook from the code window's left hand drop down and SheetSelectionChange from its right hand drop down an place the code in there. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
Dave and Rick
My first instinct was to direct OP to the rowliner add-in which is not destructive as the OP code is. The only catch I find with rowliner is it won't run on a protected worksheet. The AppEvent suggestion was for an exercise only. Gord On Sat, 06 Sep 2008 11:08:39 -0500, Dave Peterson wrote: I agree with you Gord (still not your wife, though <vbg)... This code would go into the ThisWorkbook module of the addin (that's always opened when excel opens). Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) Target.Parent.Cells.Interior.ColorIndex = xlNone With Target .EntireRow.Interior.ColorIndex = 40 .EntireColumn.Interior.ColorIndex = 36 End With End Sub Personally, I wouldn't use this. It's not robust enough to clean up after itself and I wouldn't want to see any of my nicely formatted (pretty colors!) worksheets be screwed up by this. Instead, I'd try Chip Pearson's addin: http://www.cpearson.com/excel/RowLiner.htm I'm sure he's thought this stuff out way more than I have. Gord Dibben wrote: Rick OP wants to have the code in an add-in to make it available for all open workbooks/sheets. Wouldn't that have to be done through Application Event in the add-in? I'm not sure how to achieve that. I've been browsing Chip's site but can't get anything to work with my limited skills. http://www.cpearson.com/excel/AppEvent.aspx Gord On Sat, 6 Sep 2008 11:16:10 -0400, "Rick Rothstein" wrote: To Esssa and Gary''s Student... Better would be to remove the code from the current SelectionChange event procedure and put it in the workbook's SheetSelectionChange event instead, then it would apply to every worksheet in the workbook. To Esssa... Take the code out of your current SelectionChange event, double click the ThisWorkbook entry in the Project Window on the left, choose Workbook from the code window's left hand drop down and SheetSelectionChange from its right hand drop down an place the code in there. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
My guess is that the code the OP is currently using won't run on a protected
sheet either, so I would think the RowLiner Add-In solution should still be a viable choice. -- Rick (MVP - Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Dave and Rick My first instinct was to direct OP to the rowliner add-in which is not destructive as the OP code is. The only catch I find with rowliner is it won't run on a protected worksheet. The AppEvent suggestion was for an exercise only. Gord On Sat, 06 Sep 2008 11:08:39 -0500, Dave Peterson wrote: I agree with you Gord (still not your wife, though <vbg)... This code would go into the ThisWorkbook module of the addin (that's always opened when excel opens). Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) Target.Parent.Cells.Interior.ColorIndex = xlNone With Target .EntireRow.Interior.ColorIndex = 40 .EntireColumn.Interior.ColorIndex = 36 End With End Sub Personally, I wouldn't use this. It's not robust enough to clean up after itself and I wouldn't want to see any of my nicely formatted (pretty colors!) worksheets be screwed up by this. Instead, I'd try Chip Pearson's addin: http://www.cpearson.com/excel/RowLiner.htm I'm sure he's thought this stuff out way more than I have. Gord Dibben wrote: Rick OP wants to have the code in an add-in to make it available for all open workbooks/sheets. Wouldn't that have to be done through Application Event in the add-in? I'm not sure how to achieve that. I've been browsing Chip's site but can't get anything to work with my limited skills. http://www.cpearson.com/excel/AppEvent.aspx Gord On Sat, 6 Sep 2008 11:16:10 -0400, "Rick Rothstein" wrote: To Esssa and Gary''s Student... Better would be to remove the code from the current SelectionChange event procedure and put it in the workbook's SheetSelectionChange event instead, then it would apply to every worksheet in the workbook. To Esssa... Take the code out of your current SelectionChange event, double click the ThisWorkbook entry in the Project Window on the left, choose Workbook from the code window's left hand drop down and SheetSelectionChange from its right hand drop down an place the code in there. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
I recommend this for a protected sheet but there is still some
destruction(see below) Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="justme" If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = xlColorIndexNone OldCell.Borders.LineStyle = xlLineStyleNone End If Set OldCell = Target OldCell.Interior.ColorIndex = 6 OldCell.Borders.LineStyle = xlContinuous Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If ActiveSheet.Protect Password:="justme" End Sub Will color the activecell yellow. Note: will wipe out existing background color of activecell cell unless BG color is due to CF Gord On Sat, 6 Sep 2008 13:08:24 -0400, "Rick Rothstein" wrote: My guess is that the code the OP is currently using won't run on a protected sheet either, so I would think the RowLiner Add-In solution should still be a viable choice. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
You can prevent the background color and borders destruction by storing the
relevant properties in their own Static variables and resetting them before updating the OldCell properties. Here is your code modified to do that... Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range Static OldInterior As Long Static OldBorders As Long If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="justme" If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = OldInterior OldCell.Borders.LineStyle = OldBorders End If Set OldCell = Target OldInterior = Target.Interior.ColorIndex OldBorders = Target.Borders.LineStyle OldCell.Interior.ColorIndex = 6 OldCell.Borders.LineStyle = xlContinuous Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If ActiveSheet.Protect Password:="justme" End Sub Also, you might want to add this line... If Target.Count < 1 Then Exit Sub at the beginning of the code to prevent the error that occurs when multiple cells are selected (unless you think it necessary to expand the code to handle coloring and re-coloring multiple cells, which would complicate things a little). -- Rick (MVP - Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... I recommend this for a protected sheet but there is still some destruction(see below) Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="justme" If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = xlColorIndexNone OldCell.Borders.LineStyle = xlLineStyleNone End If Set OldCell = Target OldCell.Interior.ColorIndex = 6 OldCell.Borders.LineStyle = xlContinuous Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If ActiveSheet.Protect Password:="justme" End Sub Will color the activecell yellow. Note: will wipe out existing background color of activecell cell unless BG color is due to CF Gord On Sat, 6 Sep 2008 13:08:24 -0400, "Rick Rothstein" wrote: My guess is that the code the OP is currently using won't run on a protected sheet either, so I would think the RowLiner Add-In solution should still be a viable choice. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Script Not working
Thanks Rick
Appreciate the updates. Gord On Sat, 6 Sep 2008 13:45:54 -0400, "Rick Rothstein" wrote: You can prevent the background color and borders destruction by storing the relevant properties in their own Static variables and resetting them before updating the OldCell properties. Here is your code modified to do that... Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldCell As Range Static OldInterior As Long Static OldBorders As Long If Application.CutCopyMode = 0 Then ActiveSheet.Unprotect Password:="justme" If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = OldInterior OldCell.Borders.LineStyle = OldBorders End If Set OldCell = Target OldInterior = Target.Interior.ColorIndex OldBorders = Target.Borders.LineStyle OldCell.Interior.ColorIndex = 6 OldCell.Borders.LineStyle = xlContinuous Else If OldCell Is Nothing Then Set OldCell = Target Else Set OldCell = Union(OldCell, Target) End If End If ActiveSheet.Protect Password:="justme" End Sub Also, you might want to add this line... If Target.Count < 1 Then Exit Sub at the beginning of the code to prevent the error that occurs when multiple cells are selected (unless you think it necessary to expand the code to handle coloring and re-coloring multiple cells, which would complicate things a little). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with the script | Excel Discussion (Misc queries) | |||
Help ! How do I do this in VB Script | Setting up and Configuration of Excel | |||
help with the VB script | Excel Worksheet Functions | |||
VB script help - please!! | Excel Discussion (Misc queries) | |||
VBA script help..Please !!!! | Excel Discussion (Misc queries) |