PDA

View Full Version : [SOLVED] Code runs when VBA Editor is open, but doesn't work when closed



rdekanter
03-20-2019, 09:41 AM
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

大灰狼1976
03-20-2019, 08:54 PM
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.

rdekanter
03-21-2019, 02:05 AM
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!

大灰狼1976
03-21-2019, 02:11 AM
All the best!:beerchug:

OKAMI.