Design Comments or Macro to Write Macro Needed

[Eldrod]

I just finished a macro to look through a named range on a worksheet and add or remove a cell pattern based on a "hide" or "show" code in the first column. This macro is called by the Workbook_SheetActivate and Workbook_SheetChange event handlers coded in the macro's ThisWorkbook sheet. So when ever a cell is changed, the macro is called and resets the pattern based on any changes to the hide or show codes. This seems to work well when I place test code in the macro's (.xlsm) file. However, once I save this as an .xlam file and install it as an addin, it does not function when a new spreadsheet is loaded.

If I manually add the Workbook_SheetActivate and Workbook_SheetChange events to this new spreadsheet's Thisworkbook sheet, then it does apprear to try to call the macro.

So the questions I have are:
1. Is there some reason why the calls in the macro's ThisWorkbook sheet are not functioning (or is that by design) when running as an Addin macro? (I have other code for Workbook_Open in that macro that works, but it only gets called on open, not every change...)

2. Can I progammatically open the new spreadsheet's ThisWorkbook sheet and write the Sub Workbook_SheetChange macro (it's only about 3 lines)? It's OK if that programmatically built code is lost when the file is closed -- in fact, I would like to remove it on close if it doesn't.

Does anyone know how to do that or have a better idea for what I'm trying to accomplish?

Thanks!

 

[Andrew Poulsom]

You need to use Application events:

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

 

[Eldrod]

Thanks for the help! I did find that link a little while after my posting and it seems to be just what I needed. Being able to write macros that can construct code that must be located in the spreadsheet's ThisWorkbook sheet opens up a lot of new possibilities.

 

[Eldrod]

Andrew,
I was able to get my macro to write the Worksheet functions to the calling spreadsheet's ThisWorkbook but after it does it, the VBA editor left open on the ThisWorkbook tab where the new code was written. In reviewing that document, I didn't see how to close the VBE or prevent it from opening in the first place. Do you or anyone else have any idea on how to prevent that from opening? I don't want my easily frightened user to have to manually close the VBE when this runs.
Thanks again!

 

[Andrew Poulsom]

The link I posted doesn't write any code. It uses Application level events that you can put in your Add-In.

 

[Eldrod]

Andrew, I followed the examples on the link you provided and I was successful in getting the code in my macro to write the other code into its ThisWorkbook file. The problem I have is that when I run my macro, even with the VBE closed, it opens the VBE to write the code to ThisWorkbook, but then exits the macro leaving the VBE open and with the focus. So when I activate the macro from the spreadsheet, I end up looking at the VBE withe the code that was just constructed in the Thisworkbook file.
I was asking is anyone who has done this knows how to get the VBE to close when the macro ends. I'm looking for a seamless macro run that doesn't leave the user sitting in the VBE instead of the original spreadsheet. Make sense?

 

[Andrew Poulsom]

What code is your macro writing?

 

[Eldrod]

Here's the code from my macro that writes the code to ThisWorkbook:

Option Explicit
Public SIM_Version_Number As String


Sub SimulatorLoad()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long
    Dim StartLine As Long
    Dim NumLines As Long
    Dim ProcName As String
    Dim STModel_Name As String
    Const DQUOTE = """" ' one " character
    Const vbext_wt_MainWindow As Long = 0&

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("ThisWorkbook")
    Set CodeMod = VBComp.CodeModule

    SIM_Version_Number = "Ver 1.0.0"
    STModel_Name = "Stanley Tools v1.0.9.xlam"
       
    ' Delete the SheetActivate code in ThisWorkbook to ensure we have a clean install
        ProcName = "Workbook_SheetActivate"
        On Error Resume Next
        With CodeMod
            StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
            NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
            .DeleteLines StartLine:=StartLine, Count:=NumLines
        End With

    ' Delete the SheetActivate code in ThisWorkbook to ensure we have a clean install
        ProcName = "Workbook_SheetChange"
            With CodeMod
                StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
                NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
                .DeleteLines StartLine:=StartLine, Count:=NumLines
            End With
        On Error GoTo 0
        
    ' Call the Stanley Tools Settings module to see of these events should be loaded to ThisWorkbook
    Call LoadSettings
    
    SimulatorControl.Show
    
    Unload SimulatorControl
    
    ' Write all the values back to the file
    Open STSettingsFile For Output As #1
        Print #1, "STSetting_VFB_Mode=" & STSetting_VFB_Mode_Value
        Print #1, "STSetting_SIM_Mode=" & STSetting_SIM_Mode_Value
    Close #1
    
    If STSetting_SIM_Mode_Value = "False" Then GoTo TerminateMain:
        
    ' Code to write the SheetActivate event to ThisWorkbook
    With CodeMod
        LineNum = .CreateEventProc("SheetActivate", "Workbook")
        LineNum = LineNum + 1
        .InsertLines LineNum, "    ' This code written by the SimulatorLoad macro in Stanley Tools"
        LineNum = LineNum + 1
        .InsertLines LineNum, "    Run (" & DQUOTE & "'" & STModel_Name & "'!Simulator" & DQUOTE & ")"
         'Run ("'Stanley Tools v1.0.9.xlam'!Simulator")
    End With
    
    ' Code to write the SheetChange event to ThisWorkbook
    With CodeMod
        LineNum = .CreateEventProc("SheetChange", "Workbook")
        LineNum = LineNum + 1
        .InsertLines LineNum, "    ' This code written by the SimulatorLoad macro in Stanley Tools"
        LineNum = LineNum + 1
        .InsertLines LineNum, "    Run (" & DQUOTE & "'" & STModel_Name & "'!Simulator" & DQUOTE & ")"
    End With


TerminateMain:
End Sub

and here is the code that shows up in the calling spreadsheet's ThisWorkbook:

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ' This code written by the SimulatorLoad macro in Stanley Tools
    Run ("'Stanley Tools v1.0.9.xlam'!Simulator")

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' This code written by the SimulatorLoad macro in Stanley Tools
    Run ("'Stanley Tools v1.0.9.xlam'!Simulator")

End Sub

This code for the ThisWorkbook sheet is correctly produced, but the VBE is left open on this code in the VBE instead of building this code in background and retaining focus on the calling spreadsheet. It's kinda frustrating that I'm this close to getting this to work like I need, but can't get the VBE to stay invisible in the process. Thanks for any guidance you can provide.

 

[Eldrod]

OK, I just came across this line placed at the end of the building macro that will close the VBE. I still get some screen flashing as the windows are opened and created that I'd prefer to not have, but this is at least closer to what I needed.

Application.VBE.MainWindow.Visible = False

Here is the module in it's entirety:

Option Explicit
Public SIM_Version_Number As String


Sub SimulatorLoad()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long
    Dim StartLine As Long
    Dim NumLines As Long
    Dim ProcName As String
    Dim STModel_Name As String
    Const DQUOTE = """" ' one " character
    Const vbext_wt_MainWindow As Long = 0&

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("ThisWorkbook")
    Set CodeMod = VBComp.CodeModule

    SIM_Version_Number = "Ver 1.0.0"
    STModel_Name = "Stanley Tools v1.0.9.xlam"
       
    ' Delete the SheetActivate code in ThisWorkbook to ensure we have a clean install
        ProcName = "Workbook_SheetActivate"
        On Error Resume Next
        With CodeMod
            StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
            NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
            .DeleteLines StartLine:=StartLine, Count:=NumLines
        End With

    ' Delete the SheetActivate code in ThisWorkbook to ensure we have a clean install
        ProcName = "Workbook_SheetChange"
            With CodeMod
                StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
                NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
                .DeleteLines StartLine:=StartLine, Count:=NumLines
            End With
        On Error GoTo 0
        
    ' Call the Stanley Tools Settings module to see of these events should be loaded to ThisWorkbook
    Call LoadSettings
    
    SimulatorControl.Show
    
    Unload SimulatorControl
    
    ' Write all the values back to the file
    Open STSettingsFile For Output As #1
        Print #1, "STSetting_VFB_Mode=" & STSetting_VFB_Mode_Value
        Print #1, "STSetting_SIM_Mode=" & STSetting_SIM_Mode_Value
    Close #1

    Application.ScreenUpdating = False
    
    If STSetting_SIM_Mode_Value = "False" Then GoTo TerminateMain:
      
    ' Code to write the SheetActivate event to ThisWorkbook
    With CodeMod
        LineNum = .CreateEventProc("SheetActivate", "Workbook")
        LineNum = LineNum + 1
        .InsertLines LineNum, "    ' This code written by the SimulatorLoad macro in Stanley Tools"
        LineNum = LineNum + 1
        .InsertLines LineNum, "    Run (" & DQUOTE & "'" & STModel_Name & "'!Simulator" & DQUOTE & ")"
         'Run ("'Stanley Tools v1.0.9.xlam'!Simulator")
    End With
    
    ' Code to write the SheetChange event to ThisWorkbook
    With CodeMod
        LineNum = .CreateEventProc("SheetChange", "Workbook")
        LineNum = LineNum + 1
        .InsertLines LineNum, "    ' This code written by the SimulatorLoad macro in Stanley Tools"
        LineNum = LineNum + 1
        .InsertLines LineNum, "    Run (" & DQUOTE & "'" & STModel_Name & "'!Simulator" & DQUOTE & ")"
    End With
    
    Application.VBE.MainWindow.Visible = False

TerminateMain:
Application.ScreenUpdating = True
End Sub

If anyone can figure out how to make it not show the flashing VBE sections as it builds, please let me know. I tried adding the ScreenUpdating = False, but that had no effect.
Thanks!

 

[Andrew Poulsom]

Why don't you put the event code in your Add-In, like in the link I posted?