-1

I'd like to avoid having the VBE display for users.

Here's the scenario: Access 2010 *.accdb which is used throughout the organization. When the user opens the database application, one of the modules is refreshed and resaved based on information in a table within the database. This works well.

Systems used are Windows 7 & 10.

Problem When the module noted above is refreshed & saved (via VBA) the VBE opens and is displayed.

So far After updating & saving the module, I've added the following code:

'hide the vbe
Application.VBE.MainWindow.visible = False

Unfortunately this only hides the VBE after it is clearly visible to the user. So it looks rather clunky.

Any ideas on how I can still manipulate the module and keep the VBE from being seen?

Thanks in advance for your help!

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
NWdev
  • 483
  • 1
  • 6
  • 19
  • 2
    _refreshed & saved_ - do you really need this? I've never had or met such a need. – Gustav Jun 30 '16 at 19:11
  • @Gustav Lucky you. You must have clients that actually understand how a database is supposed to be used, or already have enough money you can afford to tell them that their preferred tool isn't meant for such an application. Someday, I hope to be in your position. – CWilson Jul 01 '16 at 13:58
  • Sorry, neither of these scenarios are true. I seriously believe you are moving along a wrong track. – Gustav Jul 01 '16 at 14:25
  • 1
    @CWilson: or he designs his modules in a way that all parametrization is done via data tables (or whatever means), not by editing modules on-the-fly (shudder). Deployment is usually done with .mde/.accde files, where this wouldn't work anyway. – Andre Jul 01 '16 at 14:29
  • I've never had a client tell me how I shoudl do something - they tell me what they'd like to see. It's up to the developer to find the solution. Changing code on the fly is something that just doesn't make sense when you can supply parameters to functions and querydefs. – dbmitch Jul 01 '16 at 16:01
  • I disagree with the fundamental assumption being made here, that commands like `.Module.InsertLines` should never under any circumstances be used, especially when they have been part of the tool in question for over 20 years. But that is completely off topic. StackOverflow is not a forum, where we discuss what a questioner 'should' want or 'should' ask. Answerers either answer the question, or ignore it. – CWilson Jul 02 '16 at 18:53
  • Under some circumstances, we downvote poorly written or off topic questions and answers, but we don't downvote questions and answers that delineate a strategy we ourselves personally don't ascribe to. If we are able to fully articulate a better strategy, sometimes we suggest that strategy, in an answer (at the risk of being downvoted as off topic), but suggesting to others to not answer an on topic question is anathema to how the SE sites are meant to be run. On the other hand, if an answerer can, in fact, come up with a 'more correct' question, – CWilson Jul 02 '16 at 18:54
  • SE specifically suggests posting that question, and comments can be well used to link those two questions. – CWilson Jul 02 '16 at 18:54
  • Trying a similar approach to @CWilson's response, however other work has taken me away from this particular issue. Once I more fully vet my final approach I'll note it here. I do question why both the question and answer have received a -1 vote (down vote). It seems some ascribe to a perfect world where applications are always used as initially designed. – NWdev Jul 28 '16 at 14:30

1 Answers1

-1

You are not using Access the way it was meant to be used. Neither do I. :)

Try to add the following to the declarations at the top of the module:

Declare Function SetWindowPos Lib "user32.dll" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal ClassName As String, ByVal WindowName As String) As Long

And then add the below to the command button that calls the function(s) that change the VBE:

Application.Echo False
SetWindowPos FindWindow("wndclass_desked_gsk", Application.VBE.MainWindow.Caption), 0&, 0&, 2000&, 1, 1, &H80 Or &H1
<run your code that calls the VBE>
Application.VBE.MainWindow.Visible = False
Application.Echo True

This will first stop Access from displaying any screen changes, move the VBE window off the screen while it is still hiding in the background, runs your code, hides the window anyway once it is off the screen, and then displays the updated screen again as normal. VBE is both off the screen, and hidden from taskbar/desktop viewing.

Now, moving the VBE window off the screen is obviously cheating... but my client doesn't care if I cheat or not.

This can be annoying (or worse) if you use this code and then need to debug unexpected errors. Also, sometimes Access will save this window position upon close, even across databases, which is undesirable. Use with caution, or plenty of time on your hands.

The internet says that the following is supposed to help too, but I haven't gotten it to work in Access 2016. Maybe you are smarter than I?

LockWindowUpdate FindWindow("wndclass_desked_gsk", Application.VBE.MainWindow.Caption)

I somehow lost the declaration for LockWindowUpdate, but I am sure you can find it. I probably stole it from Chip Pearson or something anyway.

CWilson
  • 425
  • 10
  • 28
  • The answer is good, and downvotes for strategies that you don't ascribe to but are still endorsed by the manufacturer of the tool are opposed to the philosophy of SE. You are welcome to (and probably should) disagree with building a solution around this strategy, but that is not enough reason to bury a valid and accurate answer. The site does not work well under such circumstances. – CWilson Jul 02 '16 at 19:01
  • CWilson thanks for the approach positioning off screen seems an appropriate solution. Will post back (& vote up) after testing it this week. One reason I appreciate SE is that even when not using things as they typically are used, someone has gone down the path before and is willing to share their experience/expertise. Lots of good info here! – NWdev Jul 03 '16 at 20:04