Macro to turn on/off rowliner?

[Ryk]

I found this bit and just love it, but really would like it to be
disabled on startup, and have off/on button I can clickit off to
copy/paste etc (this stops copy/paste when used). Also need off button
to return row to old format.
Any ideas how I can do all this, or if it can be done at all?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS As Long = 256
Const cnHIGHLIGHTCOLOR As Long = 36 'default lt. yellow
Static rOld As Range
Static nColorIndices(1 To cnNUMCOLS) As Long
Dim i As Long
If Not rOld Is Nothing Then 'Restore color indices
With rOld.Cells
If .Row = ActiveCell.Row Then Exit Sub 'same row, don't
restore
For i = 1 To cnNUMCOLS
.Item(i).Interior.ColorIndex = nColorIndices(i)
Next i
End With
End If
Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS)
With rOld
For i = 1 To cnNUMCOLS
nColorIndices(i) = .Item(i).Interior.ColorIndex
Next i
.Interior.ColorIndex = cnHIGHLIGHTCOLOR
End With
End Sub


Thanks

Dave (AKA Ryk)

 

[Carim]

Hi Ryk,

May be you could have in Workbook_Open() a test or Msgbox to ask user
if Rowliner is needed or not ...

HTH
Cheers
Carim

 

[Ryk]

Hi Ryk,

May be you could have in Workbook_Open() a test or Msgbox to ask user
if Rowliner is needed or not ...

HTH
Cheers
Carim

If I understood how too do that hehe, can I get a bit of info? Not the
best at excel.

Dave

 

[Carim]

Ryk,

Following code should help ...

Private Sub Workbook_Open()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to use Row Highlighter ?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Choice"
Help = "DEMO.HLP"
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
MyString = "Yes"
Range("A1").Value = 1 ' Adjust to your needs
Else
MyString = "No"
Range("A1").Value = 0
End If
End Sub

This code is to be copied in the module ThisWorkbook (after all your
sheets modules)

In addition, you have to add a test into your sheet module at the very
begining :

If Range("A1").Value = 0 Then
Exit Sub
Else
....
End If ' do not forget to add end if just before End Sub

HTH
Cheers
Carim

 

[Carim]

Ryk,

Just tested code ... in order for the second bit of code to work
properly, you should rather replace previous suggestion by following :

If Range("A1").Value = 0 Then
Cells(1, 1).Resize(1, cnNUMCOLS).Interior.ColorIndex = xlNone
Exit Sub
Else
.....
End If
End Sub


HTH
Cheers
Carim

 

[Ryk]

Not sure why Carim but it gives me an error and highlights the A1 in
the second bit of code you added, do I need name it sub something? The
rows I'd be highlighting are 6-1006 and B6 would always have info in
it, my A1 is blank, I tried change it to B6 but no effect.

Thanks

Dave

 

[Carim]

Hi Dave,

If you have picked the cell B6 to be your test cell ,

( i.e. the cell in which you or the event macro inputs 0 or 1 which in
turn allows or disallows your row highlighter macro ) , then your
should have, in your Selection_Change module :

Cells (6,2) instead of Cells (1,1) in your code ...

HTH
Cheers
Carim