1

I'd like to be able to add code to a newly created worksheet. The following block of code does that, but will give me an error (pointing to the first line of the code below) if the Visual Basic editor is not open. And, if it is open in the background, it will activate the VB editor window after the macro finishes running. 

With wb.VBProject.VBComponents(wb.Worksheets(newSheetName).CodeName).CodeModule
    .InsertLines Line:=.CreateEventProc("FollowHyperlink", "Worksheet") + 1, _
    String:=vbCrLf & _
    "Call FindAllInSheet(Target.Range.Text, Range(Cells(2, 2),Cells(" & num_triple_combos + 1 & ", " & start_triples_col + 1 & ")))"
End With

Is there a way to avoid this behavior?

user2441
  • 11
  • 2
  • I found a similar question here, but after reading the answers I'm still not sure how to open and close the CodePane/CodeModule/VBE (?) as needed: http://stackoverflow.com/questions/32748946/why-doesnt-vbe-activecodepane-codemodule-work-when-the-vbe-code-window-isnt – user2441 Mar 04 '16 at 22:17

1 Answers1

0

For now, what I've done that works is by surrounding my code with

Application.VBE.MainWindow.Visible = True

...

Application.VBE.MainWindow.Visible = False

The only issue now is that the macro will cycle through every single existing sheet before adding the code to the designated sheet. I'm not sure why.

Source for my lead: http://www.mrexcel.com/forum/excel-questions/31259-macro-call-visual-basic-editor.html

user2441
  • 11
  • 2