Consulting

Results 1 to 4 of 4

Thread: Code runs when VBA Editor is open, but doesn't work when closed

  1. #1

    Code runs when VBA Editor is open, but doesn't work when closed

    Hello,

    I have some code that launches from a control button on a sheet. It does a bunch of stuff but at one point it writes a Worksheet_Change() event into the current sheet as follows:
    'Write code to worksheet_change() event to automatically update axes
    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.codeName).CodeModule
        i = .CreateEventProc("Change", "Worksheet")
        i = i + 2
        .InsertLines i, "With ActiveSheet.ChartObjects(""Gantt"")" & vbCrLf & _
                        "   .Chart.Axes(xlValue).MinimumScale = Range(""C" & tasks + 3 & """).Value" & vbCrLf & _
                        "   .Chart.Axes(xlValue).MaximumScale = Range(""C" & tasks + 6 & """).Value" & vbCrLf & _
                        "End With" & vbCrLf
    End With
    The ouput of this should write the following code into the activesheet, assuming that access to the VBA project object model is enabled and the reference "Microsfot Visual Basic for Appplications Extensibility 5.3" is included:

    Private Sub Works
    heet_Change(ByVal Target As Range)
    
    With ActiveSheet.ChartObjects("Gantt")
       .Chart.Axes(xlValue).MinimumScale = Range("C12").Value
       .Chart.Axes(xlValue).MaximumScale = Range("C15").ValueEnd
    With
    
    End Sub
    Here is my issue. If I have the VBA Editor window open and I either run from the command button in the excel sheet, or directly from the editor itself then the code works fine and the intended result is achieved.
    However, if I close the VBA Editor and run from the command button then the Worksheet_Change() event does not get successfully written. All other code runs as intended, it's just this bit that doesn't work for some reason.

    Does anyone have any ideas as to why this may be?

    Thanks in advance,
    -Ralph

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Hi rdekanter!
    It works well on my side, but you can try this:
    Sub test()
    ActiveWorkbook.VBProject.VBComponents(1).Activate
    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        i = .CreateEventProc("Change", "Worksheet")
        i = i + 2
        .InsertLines i, "With ActiveSheet.ChartObjects(""Gantt"")" & vbCrLf & _
                        "   .Chart.Axes(xlValue).MinimumScale = Range(""C" & Tasks + 3 & """).Value" & vbCrLf & _
                        "   .Chart.Axes(xlValue).MaximumScale = Range(""C" & Tasks + 6 & """).Value" & vbCrLf & _
                        "End With" & vbCrLf
    End With
    End Sub
    Activate the project first.

  3. #3
    Hi,

    I realised after I posted that it did work in isolation - it's because the code before hand uses the dreaded SendKeys method to do some menu changes which seems to shift focus and stops it working for some reason.

    No matter though, as your idea was exactly what was needed
    ActiveWorkbook.VBProject.VBComponents(1).Activate
    followed by a command to close the window afterwards
    ActiveWorkbook.VBProject.VBE.MainWindow.Visible = False
    Thanks so much!

  4. #4
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    All the best!

    OKAMI.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •