Switching back to active Module1 pane from UserForm pane

[rarkin]

I run the RowRangeForm macro (below) from within another Excel macro, both in Module1 of my VBAProject (Personal.XLSB). The form macro works fine but the VBA Editor remains showing the Userform pane instead of switching back to the active Module1 pane. I want Module1 and Procedure that called the form to stay visible in the VBA Editor.

I tried using this:

ThisWorkbook.VBProject.VBE.ActiveCodePane.Show

But that gave this error message: "Run-time error '1004": Programmatic access to Visual Basic Project is not trusted"

Here's my code:

Sub RowRangeForm()

RowRange.Show
RowStartValue = RowRange.RowStart.Value
RowEndValue = RowRange.RowEnd.Value
Unload RowRange

ThisWorkbook.VBProject.VBE.ActiveCodePane.Show 'gets the "not trusted" error.

End Sub

I've also tried a couple different ways but can't get it to work...probably needs the object set/defined correctly? Any suggestions appreciated!

--Ray

 

[combo]

Access to vba project has to be set manually via the trust center.

combo

 

[rarkin]

Thanks for the response, combo. It occurred to me there might be a much simpler solution...and indeed there was...the KISS principle is alive and well.

Turns out I had to do was Hide the form after unloading it---that did the trick, leaving Module1 pane showing in the current procedure. Unloading the form did hide the Form itself from showing, but did not hide the VBE Userform pane.

Here was the final code (note my RowStartValue and RowEndValue variables were declared globally):

Sub RowRangeForm()
'Runs the RowRange form and assigned form input to global variables.
RowRange.Show
RowStartValue = RowRange.RowStart.Value
RowEndValue = RowRange.RowEnd.Value
Unload RowRange 'This cloes Form and resets form fields to defaults
RowRange.Hide 'This hides the VBE Userform pane.
End Sub

No need for commands requiring the "Trust access" setting change.

** Important note for anyone changing that setting: I read somewhere that in addition to security concerns, some anti virus programs might see that Trust access setting has been changed and then might delete all your VBA code. If so a good reason to avoid changing it!

Thanks again for the response.

 

[strongm]

Actually, normally I'd expect the VBA IDE to be kept hidden - unless you start the code from the IDE. And the IDE does not do live tracking as code is running (well, uinless you are Stepping through the code). So essentially you are just being lucky with what the IDE is displaying when the code finishes.

>in addition to security concerns, some anti virus programs might see that Trust access setting has been changed and then might delete all your VBA code

Surely that is a security concern ;-) ... but I am not aware of any AV software that arbitrarily deletes VBA from an application on the basis that the Trust setting has been changed. Normally those that implement VBA macro scanning (as opposed to simple script scanning - VBscript, JavaScript) implement a set of heuristics to identify whether macro code is harmful (e.g looking for self-modification code such as .InsertLine), and even then generally do no, by default, delete code identified as hostile. Usually they either simply block the hostile action or ask the user what action to take.

 

[rarkin]

Thanks for the clarifications.

I have an external monitor attached to my laptop and when I test/run my excel macros I like to keep the VBE window open for convenience to step through and edit code That's why I wanted the module1 pane to stay showing without having to close/open VBE or constantly click back to reopen module1 from the Userform pane.

When running my macro from outside VBE (but with VBE still open), it was keeping the Userform pane showing and not switching back to the module1 pane.

When I close VBE, run the macro, then reopen VBE---the Module1 pane and the current procedure are what is showing. I'm not sure why it behaves differently when VBE is open, but at least using the Hide command at the end solved that. Thanks again.

--Ray

 

[strongm]

Ok, well have it your own way. There's clearly something else going on here, but if for now it is working the way you want ...

 

[combo]

Information about the view of modules windows, which are opened and what are their positions, are stored in excel file. So you stay with the view you had when saving the file.
Hide command in case of userform hides the instance of userform template in your vba project, it has no influence on windows in your VBE IDE.

combo