How to add programmatically a Worksheet_SelectionChange code snippet

[drlex]

Good day, all.

I'm using a VBA-like program (Cognos Script) to attempt to add a short piece of code to an Excel file. The method works when adding a macro, but not for more general code.

The Code is a Worksheet_SelectionChange event and is constructed in a string using Chr(34) and Chr(13)

   strCode = "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & CHR(13)
   strCode = strCode & "Dim strfilter, rowno" & CHR(13)
   strCode = strCode & "If Not Application.Intersect(Target, Me.Range(" + CHR(34) + "B2:E27"+ CHR(34) + ")) Is Nothing Then" & CHR(13)
   strCode = strCode & "    strfilter = Chr(63 + ActiveCell.Column)" & CHR(13)
   strCode = strCode & "    rowno = CStr(ActiveCell.Row)" & CHR(13)
   strCode = strCode & "    Sheets(Range("& CHR(34) & "A" & CHR(34) & "+ rowno).FormulaR1C1).Select" & CHR(13)
   strCode = strCode & "    ActiveSheet.Columns("& CHR(34) & "L:L" & CHR(34) & ").AutoFilter Field:=1, Criteria1:=strfilter" & CHR(13)
   strCode = strCode & "End If" & CHR(13)
   strCode = strCode & "End Sub" & CHR(13)  
   strCode = strCode & " "

It is added to the open workbook by this code

    Set xlmodule = objExcel.Workbooks(1).VBProject.VBComponents.Add(1) 
    xlmodule.CodeModule.AddFromString strCode
    Set xlmodule = Nothing

I tried adding the following code

    With objExcel.Application.VBE.MainWindow
       .Visible = True
       .Visible = False
    End With

but the workbook does not retain all the code; in examining the workbook by using the 'view code' on the sheet, I can only see

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

on the worksheet.

Can someone point me in the right direction or tell me what I've overlooked?

Many thanks,
lex



soi là, soi carré

 

[Loomah]

hi
never done this myself but you may find your solution here

http://www.cpearson.com/Excel/vbe.aspx

good luck!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[drlex]

Loomah,
Thanks for the response. I had found and used the page you reference to start off, but the script I am using lacks many of the features available in Excel VB.
I've ended up going "meta", in that I have wrapped the code up in a macro, which I add, run and then remove prior to saving the workbook. Messy, but it works.
lex


soi là, soi carré