asked on

Change the code in Worksheet_SelectionChange using other code

I have been able to set up a new Excel workbook using VBA in Word (2010) and load up from Word to Excel an ordinary Module and Class Module that I want to run in Excel.

I want to have the Worksheet_SelectionChange event handler run a refresh macro whenever values are changed in the spreadsheet but I can't find how to export the code from Word.

Can I do so or can I have something in the other code i pass across from Word that will do so as this is hard coded into the Word file?

Another post I'm about to make concerns the operation of the Worksheet_SelectionChange with class modules for the same application but seemed like a different issue.

Chris Bottomley

Do you hard code the vba into the excel workbook or upload exported modules ... depending on the complexity of the code it will be easy enough to code the vba directly into thisWorkbook if you supply the code required or at least some idea of the 'size'.

Chris

sjgrey

ASKER

I'm exporting the ordinary and class modules. They aren't massive.

The code fro the event handler will probably be one or two lines to call a a routine from the exported modules but I don't know how to get it into the Worksheet_SelectionChange for the relevant sheet, sheet number 1 in the workbook.

Chris Bottomley

The following covers some of the ground you have already stated as done ... but is done to give a more easily tested implementation and provide a more full record for future viewers ;o).

The bit you require I think is prefixed by the comment:
'Add sheet eventcode

Sub genxlCode()
Dim xlApp As Object
Dim xlBook As Object
Dim xhSheet As Object
Const xlExcel8 As Integer = 56

'Open
Set xlApp = CreateObject("excel.application")
    Set xlBook = xlApp.workbooks.Add
'Add default module
    xlApp.VBE.activevbproject.vbcomponents.import "c:\deleteme\importme.bas"
'Add sheet eventcode
    With xlApp.VBE.activevbproject.vbcomponents("Sheet1").codemodule
        .InsertLines 1, "Private Sub Worksheet_Change(ByVal Target As Range)"
        .InsertLines 2, "    call importme.gashcode"
        .InsertLines 3, "End Sub"
    End With
'Close
    xlApp.DisplayAlerts = False
    xlBook.SaveAs FileName:="c:\deleteme\random.xls", FileFormat:= _
        xlExcel8
    xlBook.Close
    Set xlBook = Nothing
    xlApp.Quit
End Sub

Open in new window

Let me know if it meets your needs or indicate my misunderstanding if it does not!

Chris

ASKER CERTIFIED SOLUTION

Chris Bottomley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sjgrey

ASKER

That worked fine

Thanks very much

Chris Bottomley

Glad it helped you and thank you for the prompt responses and grading ... always appreciated.

Chris