insert Worksheet_SelectionChange(ByVal Target As Range) through vba

[scott]

Thanks to Chip Pearsons great website help, I am able to insert a
number of modules and procedures into files which are opened as txt,
processed in a number of ways and saved as xls files.

If the user makes changes in a particular column I want to record
those changes in a hidden column for export into a database.

I have managed to run code which does exactly what I want on
Worksheet_SelectionChange(ByVal Target As Range).

I worked around what I wanted to do by running a procedure on the
before Save event. However I would really like to record the value
changes cell by cell rather than a longer process on the before Save.


SO... I guess I am wondering what I change to insert a
Worksheet_SelectionChange(ByVal Target As Range) procedure.

I know am misunderstanding something.

my line which kills excel is:

Set VBCodeMod =
ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule


This does work when the imported txt file is already open but if it is
called upon opening I send Microsoft messages about the state of my
computer when it choked.

Is this related to the actual sheet name being assigned the file name?
for instance 16135.txt opens and the sheet is called 16135.

And maybe I have misunderstood assigning "Sheet1" in place of
"ThisWorkBook".

As I say, all the procedures work when called and the txt file is
already open. It will add the procedure and do exactly as I want. Just
not upon opening.

And I mostly am trying to understand why.

Thank You all.
Scott

 

[Tim Williams]

Could try adding a DoEvents after opening the file and before referencing
the sheet module.
"Sheet1" does seem to be the default codename whatever the name of the text
file...

Or post a (small) working example which illustrates the problem (I guess
that would be *non* working...)

Tim

 

[scott]

Thanks for the offer of assistance. I will play with DoEvents.

I am inserting a sample of what I do have. I am sure you realize this
is called from another procedure and their are other things going on.
And since I have only learned this by trial and error my code may not
be written very well.

Sub Record_SKUs()

Dim VBCodeMod As CodeModule
Dim LineNum As Long
Set VBCodeMod =
ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Public ad_str, frm_ad" & Chr(13) & _
"" & Chr(13) & _
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" &
Chr(13) & _
"Dim tmpObj, strObj" & Chr(13) & _
"On Error Resume Next" & Chr(13) & _
"strObj = frm_ad" & Chr(13) & _
"If ActiveCell.Column = 7 Then" & Chr(13) & _
"Range(strObj).Offset(0, 7) = Left(ad_str, 3) & ""0""" & Chr(13) & _
"Range(strObj).Offset(0, 8) = Right(ad_str, 5)" & Chr(13) & _
"End If" & Chr(13) & _
"If ActiveCell.Column = 7 And ActiveCell.Row <> 1 Then" & Chr(13) & _
"ad_str = ActiveCell" & Chr(13) & _
"frm_ad = ActiveCell.Address" & Chr(13) & _
"End If" & Chr(13) & _
"End Sub"
End With
End Sub