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.
LVL 1
sjgreyAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Note the supplied code was meant to work irrespective.  The following also works and demonstrates the changed cell feedback as long as a suitale sub exists in the imported module ... for example:

Sub gashCode(Optional addy As Range)
    If addy Is Nothing Then
        MsgBox "Hi from GashCode Sub in Module ImportMe"
    Else
        MsgBox "Cell " & addy.Address(False, False) & " changed to " & addy.Text
    End If
End Sub

Open in new window


'...
    With xlApp.VBE.activevbproject.vbcomponents("Sheet1").codemodule
        .InsertLines 1, "Private Sub Worksheet_Change(ByVal Target As Range)"
        .InsertLines 2, "    call importme.gashcode"
        .InsertLines 3, "    call importme.gashcode(target)"
        .InsertLines 4, "End Sub"
    End With
'...

Open in new window


Chris
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
0
 
sjgreyAuthor Commented:
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.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
0
 
sjgreyAuthor Commented:
That worked fine

Thanks very much
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Glad it helped you and thank you for the prompt responses and grading ... always appreciated.

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.