Macro to turn on/off rowliner?

  • Thread starter Thread starter Ryk
  • Start date Start date
R

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)
 
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
 
Carim said:
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
Click to expand...

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

Dave
 
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
 
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
 
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
 
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
 
Back
Top