Adding Worksheet_SelectionChange code by macro
 

I wish to add the following code to SelectionChange of a
worksheet during part of a longer routine that converts a
csv file to an Excel97 s/s:

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
With Target.EntireRow.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range(Worksheets("CheckSht").Range
("SElRow")).EntireRow.Interior.ColorIndex = xlNone
Worksheets("CheckSht").Range("SElRow") = Target.Address
End Sub

After reviewing this forum and Chip Pearsons site I have
come up with:

Public Sub AddcodeToSheet()
Dim WBCodeMod As Object
Dim NameCode As String
Dim LineCount As Integer
Dim CodeLine As Integer

NameCode = Worksheets("Sheet1").CodeName
Set WBCodeMod = ActiveWorkbook.VBProject.VBComponents
(NameCode).CodeModule
LineCount = WBCodeMod.countoflines
CodeLine = WBCodeMod.countoflines + 1

With WBCodeMod
.InsertLines CodeLine, "Private Sub
Worksheet_SelectionChange(ByVal Target As Excel.Range)"
CodeLine = CodeLine + 1
.InsertLines CodeLine, "With Target.EntireRow.Interior"
CodeLine = CodeLine + 1
.InsertLines CodeLine, ".Pattern = xlSolid"
CodeLine = CodeLine + 1
.InsertLines CodeLine, "End With"
CodeLine = CodeLine + 1
.InsertLines CodeLine, _
"Range(Worksheets(""CheckSht"").Range
(""SElRow"")).EntireRow.Interior.ColorIndex = xlNone"
CodeLine = CodeLine + 1
.InsertLines CodeLine, _
"Worksheets(""CheckSht"").Range(""SElRow"") =
Target.Address"
CodeLine = CodeLine + 1
.InsertLines CodeLine, "End Sub"
End With
End Sub

Which may well be recognised as slightly changed code from
other posts.

It runs fine until the first InsertLines and then causes
Excel to throw a fit and terminate.

Can anyone suggest a reason for this, or better code.

All I am trying to do is add a small routine that
highlights the current row and removes the highlighting
from the previously selected row

Adding Worksheet_SelectionChange code by macro
 

Is the project protected?

-TJ

On Tue, 18 May 2004 10:23:57 -0700, Alan Webb wrote:

I wish to add the following code to SelectionChange of a
worksheet during part of a longer routine that converts a
csv file to an Excel97 s/s:

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
With Target.EntireRow.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range(Worksheets("CheckSht").Range
("SElRow")).EntireRow.Interior.ColorIndex = xlNone
Worksheets("CheckSht").Range("SElRow") = Target.Address
End Sub

After reviewing this forum and Chip Pearsons site I have
come up with:

Public Sub AddcodeToSheet()
Dim WBCodeMod As Object
Dim NameCode As String
Dim LineCount As Integer
Dim CodeLine As Integer

NameCode = Worksheets("Sheet1").CodeName
Set WBCodeMod = ActiveWorkbook.VBProject.VBComponents
(NameCode).CodeModule
LineCount = WBCodeMod.countoflines
CodeLine = WBCodeMod.countoflines + 1

With WBCodeMod
.InsertLines CodeLine, "Private Sub
Worksheet_SelectionChange(ByVal Target As Excel.Range)"
CodeLine = CodeLine + 1
.InsertLines CodeLine, "With Target.EntireRow.Interior"
CodeLine = CodeLine + 1
.InsertLines CodeLine, ".Pattern = xlSolid"
CodeLine = CodeLine + 1
.InsertLines CodeLine, "End With"
CodeLine = CodeLine + 1
.InsertLines CodeLine, _
"Range(Worksheets(""CheckSht"").Range
(""SElRow"")).EntireRow.Interior.ColorIndex = xlNone"
CodeLine = CodeLine + 1
.InsertLines CodeLine, _
"Worksheets(""CheckSht"").Range(""SElRow"") =
Target.Address"
CodeLine = CodeLine + 1
.InsertLines CodeLine, "End Sub"
End With
End Sub

Which may well be recognised as slightly changed code from
other posts.

It runs fine until the first InsertLines and then causes
Excel to throw a fit and terminate.

Can anyone suggest a reason for this, or better code.

All I am trying to do is add a small routine that
highlights the current row and removes the highlighting
from the previously selected row

Adding Worksheet_SelectionChange code by macro
 

-----Original Message-----

Is the project protected?

-TJ


Not so far as I am aware.

What I have is an addin that contains all of the code and
is fired from a new menu item added to Tools.

The code is working on a csv file when it hits the
SelectionChange code and it did cross my mind that ir may
be that which is causing it to keel over - ie at the time
it is not an excel workbook, but a text csv file