|
Help with 1 code line
Below code in Sheet2 allows user to return to previous clicked cell in
Sheet1: My Sheet1 code (not shown) Highlights ActiveRow (on Sheet2) setting interior = 3. 4th line of code below is my attempt to remove colorindex when retuning to sheet1, however it "ain't-working". Any help appreciated. TIA, Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Not Intersect(Target, Range("B:B").EntireColumn) Is Nothing Then Cancel = True Application.Goto Reference:=Application.PreviousSelections(1) ActiveSheet.Range("B4").CurrentRegion.Rows.Interio r = xlColorIndexNone <<BOMB!! End If End Sub |
Help with 1 code line
I've added a few checks and disabled events while the thing is running,
(probably not needed, but you do want to err on the safe side. and you may have other event handlers on the sheet or book) Since you run in an object module i've used the Me keyword, with evaluates to the sheet in which the code is running. Also I've changed to sequence of color and goto First reset the color, THEN jump to prevselect Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Not Intersect(Target, Me.Columns(2)) Is Nothing Then Cancel = True With Application .EnableEvents = False Me.Range("B4").CurrentRegion.Interior.ColorIndex = xlColorIndexNone 'or .Entirerow.Interior.ColorIndex = xlColorIndexNone If Not .PreviousSelections(1) Is Nothing Then .Goto Reference:=.PreviousSelections(1) End If .EnableEvents = True End With End If End Sub hth... keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "JMay" wrote: Below code in Sheet2 allows user to return to previous clicked cell in Sheet1: My Sheet1 code (not shown) Highlights ActiveRow (on Sheet2) setting interior = 3. 4th line of code below is my attempt to remove colorindex when retuning to sheet1, however it "ain't-working". Any help appreciated. TIA, Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Not Intersect(Target, Range("B:B").EntireColumn) Is Nothing Then Cancel = True Application.Goto Reference:=Application.PreviousSelections(1) ActiveSheet.Range("B4").CurrentRegion.Rows.Interio r = xlColorIndexNone <<BOMB!! End If End Sub |
Help with 1 code line
Hi JMay,
Without otherwise looking at your event procedure, your problem code line should read: ActiveSheet.Range("B4").CurrentRegion.Rows.Interio r.Color = xlColorIndexNone --- Regards, Norman "JMay" wrote in message news:M6Ewc.10402$ye1.1834@lakeread05... Below code in Sheet2 allows user to return to previous clicked cell in Sheet1: My Sheet1 code (not shown) Highlights ActiveRow (on Sheet2) setting interior = 3. 4th line of code below is my attempt to remove colorindex when retuning to sheet1, however it "ain't-working". Any help appreciated. TIA, Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Not Intersect(Target, Range("B:B").EntireColumn) Is Nothing Then Cancel = True Application.Goto Reference:=Application.PreviousSelections(1) ActiveSheet.Range("B4").CurrentRegion.Rows.Interio r = xlColorIndexNone <<BOMB!! End If End Sub |
Help with 1 code line
The correct property is .Interior.ColorIndex = xlNone
Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "JMay" wrote in message news:M6Ewc.10402$ye1.1834@lakeread05... Below code in Sheet2 allows user to return to previous clicked cell in Sheet1: My Sheet1 code (not shown) Highlights ActiveRow (on Sheet2) setting interior = 3. 4th line of code below is my attempt to remove colorindex when retuning to sheet1, however it "ain't-working". Any help appreciated. TIA, Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Not Intersect(Target, Range("B:B").EntireColumn) Is Nothing Then Cancel = True Application.Goto Reference:=Application.PreviousSelections(1) ActiveSheet.Range("B4").CurrentRegion.Rows.Interio r = xlColorIndexNone <<BOMB!! End If End Sub |
Help with 1 code line
Thanks keepitcool,
used your alternative adding Activecell before .Entirerow.Interior.ColorIndex = xlColorIndexNone WORKS GREAT!! If a user "moves around" using arrow keys (Say 4 or 5 strokes) I think the PreviousSelection(Count) is lost. hummm JMay "keepitcool" wrote in message ... I've added a few checks and disabled events while the thing is running, (probably not needed, but you do want to err on the safe side. and you may have other event handlers on the sheet or book) Since you run in an object module i've used the Me keyword, with evaluates to the sheet in which the code is running. Also I've changed to sequence of color and goto First reset the color, THEN jump to prevselect Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Not Intersect(Target, Me.Columns(2)) Is Nothing Then Cancel = True With Application .EnableEvents = False Me.Range("B4").CurrentRegion.Interior.ColorIndex = xlColorIndexNone 'or .Entirerow.Interior.ColorIndex = xlColorIndexNone If Not .PreviousSelections(1) Is Nothing Then .Goto Reference:=.PreviousSelections(1) End If .EnableEvents = True End With End If End Sub hth... keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "JMay" wrote: Below code in Sheet2 allows user to return to previous clicked cell in Sheet1: My Sheet1 code (not shown) Highlights ActiveRow (on Sheet2) setting interior = 3. 4th line of code below is my attempt to remove colorindex when retuning to sheet1, however it "ain't-working". Any help appreciated. TIA, Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Not Intersect(Target, Range("B:B").EntireColumn) Is Nothing Then Cancel = True Application.Goto Reference:=Application.PreviousSelections(1) ActiveSheet.Range("B4").CurrentRegion.Rows.Interio r = xlColorIndexNone <<BOMB!! End If End Sub |
Help with 1 code line
The previousselections collections is only populated by using a name from
the namebox or using GoTo From Help: Each time you go to a range or cell by using the Name box or the Go To command (Edit menu), or each time a macro calls the Goto method, the previous range is added to this array as element number 1, and the other items in the array are moved down. Just selecting a cell with a mouse does not add the range to this collection. -- Regards, Tom Ogilvy "JMay" wrote in message news:pHFwc.10512$ye1.9297@lakeread05... Thanks keepitcool, used your alternative adding Activecell before .Entirerow.Interior.ColorIndex = xlColorIndexNone WORKS GREAT!! If a user "moves around" using arrow keys (Say 4 or 5 strokes) I think the PreviousSelection(Count) is lost. hummm JMay "keepitcool" wrote in message ... I've added a few checks and disabled events while the thing is running, (probably not needed, but you do want to err on the safe side. and you may have other event handlers on the sheet or book) Since you run in an object module i've used the Me keyword, with evaluates to the sheet in which the code is running. Also I've changed to sequence of color and goto First reset the color, THEN jump to prevselect Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Not Intersect(Target, Me.Columns(2)) Is Nothing Then Cancel = True With Application .EnableEvents = False Me.Range("B4").CurrentRegion.Interior.ColorIndex = xlColorIndexNone 'or .Entirerow.Interior.ColorIndex = xlColorIndexNone If Not .PreviousSelections(1) Is Nothing Then .Goto Reference:=.PreviousSelections(1) End If .EnableEvents = True End With End If End Sub hth... keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "JMay" wrote: Below code in Sheet2 allows user to return to previous clicked cell in Sheet1: My Sheet1 code (not shown) Highlights ActiveRow (on Sheet2) setting interior = 3. 4th line of code below is my attempt to remove colorindex when retuning to sheet1, however it "ain't-working". Any help appreciated. TIA, Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Not Intersect(Target, Range("B:B").EntireColumn) Is Nothing Then Cancel = True Application.Goto Reference:=Application.PreviousSelections(1) ActiveSheet.Range("B4").CurrentRegion.Rows.Interio r = xlColorIndexNone <<BOMB!! End If End Sub |
Help with 1 code line
Thanks Tom...
much appreciated. "Tom Ogilvy" wrote in message ... The previousselections collections is only populated by using a name from the namebox or using GoTo From Help: Each time you go to a range or cell by using the Name box or the Go To command (Edit menu), or each time a macro calls the Goto method, the previous range is added to this array as element number 1, and the other items in the array are moved down. Just selecting a cell with a mouse does not add the range to this collection. -- Regards, Tom Ogilvy "JMay" wrote in message news:pHFwc.10512$ye1.9297@lakeread05... Thanks keepitcool, used your alternative adding Activecell before .Entirerow.Interior.ColorIndex = xlColorIndexNone WORKS GREAT!! If a user "moves around" using arrow keys (Say 4 or 5 strokes) I think the PreviousSelection(Count) is lost. hummm JMay "keepitcool" wrote in message ... I've added a few checks and disabled events while the thing is running, (probably not needed, but you do want to err on the safe side. and you may have other event handlers on the sheet or book) Since you run in an object module i've used the Me keyword, with evaluates to the sheet in which the code is running. Also I've changed to sequence of color and goto First reset the color, THEN jump to prevselect Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) If Not Intersect(Target, Me.Columns(2)) Is Nothing Then Cancel = True With Application .EnableEvents = False Me.Range("B4").CurrentRegion.Interior.ColorIndex = xlColorIndexNone 'or .Entirerow.Interior.ColorIndex = xlColorIndexNone If Not .PreviousSelections(1) Is Nothing Then .Goto Reference:=.PreviousSelections(1) End If .EnableEvents = True End With End If End Sub hth... keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "JMay" wrote: Below code in Sheet2 allows user to return to previous clicked cell in Sheet1: My Sheet1 code (not shown) Highlights ActiveRow (on Sheet2) setting interior = 3. 4th line of code below is my attempt to remove colorindex when retuning to sheet1, however it "ain't-working". Any help appreciated. TIA, Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Not Intersect(Target, Range("B:B").EntireColumn) Is Nothing Then Cancel = True Application.Goto Reference:=Application.PreviousSelections(1) ActiveSheet.Range("B4").CurrentRegion.Rows.Interio r = xlColorIndexNone <<BOMB!! End If End Sub |
| All times are GMT +1. The time now is 05:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com