A
Alan Webb
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
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