-3

I was just wondering: It is possible to create a click button in VBA Excel that opens the VBA editor when the button is clicked?

UPDATE: The point that I was looking for was achieved by @pgSystemTester. In this, I don't have to disable the trust settings, as suggested by another answers pasted here below. Very better way.

Eric
  • 51
  • 6
  • 1
    Yes. https://stackoverflow.com/questions/68321968/excel-macro-to-open-up-the-visual-basic-editor. If you experience this error: "programmatic access to visual basic project is not trusted", see: https://stackoverflow.com/questions/25638344/programmatic-access-to-visual-basic-project-is-not-trusted – ouroboros1 May 12 '22 at 22:12
  • @ouroboros1 Not worked. I've tried `Application.VBE.ActiveVBProject.VBComponents("Module1").Activate` but does not work as well. – Eric May 12 '22 at 22:24
  • I got the error 1004, that I dont know what it is about.. – Eric May 12 '22 at 22:26
  • 1004 is the error mentioned above. See the second link and follow the steps outlined there: "File -> Options -> Trust Center -> Trust Center Setttings -> Macro Settings -> Trust Access to the VBA Project object model." – ouroboros1 May 12 '22 at 22:28
  • Ok. But now the error is 438. – Eric May 12 '22 at 22:30
  • On which line are you getting this error? – ouroboros1 May 12 '22 at 22:32
  • Sorry, I was committing an error. Now it worked. However, considering that this worksheet will be passed on to inexperienced users who will not know how to perform this second procedure, is it possible, through VBA, to program all the steps? – Eric May 12 '22 at 22:35
  • 1
    Inexperienced users should not be in the visual basic editor, nor should they be lowering their PC's trust settings. – pgSystemTester May 12 '22 at 23:02

1 Answers1

4

In response to the comments, it seems hard to envision a situation where it's a good idea to be directing "inexperienced users" to the visual basic editor. Similarly, lowering these same users' systems' defenses against malicious code seems unwise.

With that said, you could just try to teach your users to type: alt L V

I don't like keystroke macros, but this actually seems to work for me:

Sub OpenVBE()

   Application.SendKeys ("%lv")

End Sub

This appears to work without lowering the users' trust settings also.

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • 1
    Or Alt+F11 also works – Tim Williams May 12 '22 at 23:42
  • @TimWilliams that was my first idea but it was 3 characters longer `"%{F11}"` – pgSystemTester May 12 '22 at 23:46
  • @Eric, thanks for accepting, glad it works, but I still would question the need to send inexperienced users to the vbe. Good luck. – pgSystemTester May 12 '22 at 23:47
  • But one key shorter! (as long as your keyboard has actual function keys I suppose...) – Tim Williams May 12 '22 at 23:52
  • Alt+F11, for some reason, not worked here. Answering @pgSystemTester 's question, the spreadsheet will be passed on to a customer who has no knowledge of VBA, or programming in general. The reason for opening the VBA editor was just so the user could look at the work done. So that he could assess, at least visually, everything that was accomplished. There are some clients who like to see every detail of what has been done, even if it is incomprehensible to them. – Eric May 12 '22 at 23:55
  • @TimWilliams Regarding Alt+F11, even directly on the keyboard is not working in my Excel. Any tips? – Eric May 12 '22 at 23:58
  • 1
    @Eric obviously this is just a preference, but I'd just tell the customers how to look at it. Show them one of the 5,000 youtube videos on how to view code, rather than build EXTRA code so they can see it. Just my two cents. – pgSystemTester May 13 '22 at 00:14
  • 2
    @Eric you probably have your function keys set to be inverse. try while holding down the function key (so function KEY and ALT AND F11) -- and this would actually be a problem several people might have so.... I'd say use the answer I proposed :) – pgSystemTester May 13 '22 at 00:15