PDA

View Full Version : open specific module



lior03
12-28-2005, 06:08 AM
hello
is it possible to open the vb editor exactly at a specific module .
maybe through a inputbox to which the user will enter the module name or number?
and get a message if it is not there- "module do not exist,please try again".
thanks

Marcster
12-28-2005, 09:22 AM
Hi lior03,

I've come up with the code below:

Function ModuleExists(ModuleName As String) As Boolean
On Error Resume Next
ModuleExists = Len(ThisWorkbook.VBProject.VBComponents(ModuleName).Name) <> 0
End Function

Sub OpenModule()
'Searches for module (held in strModule) and displays it if found.
'Uses Function ModuleExists

Dim strModuleName As String
strModuleName = "Module1" 'The name of the module to find

If ModuleExists(strModuleName) Then
MsgBox strModuleName & " found."
Application.VBE.MainWindow.Visible = True 'Show the VBE
'Display the found module:
ThisWorkbook.VBProject.VBComponents(strModuleName).Activate
Else
MsgBox strModuleName & " module do not exist,please try again"
End If

End Sub


Change the contents of strModuleName in Sub OpenModule
to the name of the module you want to search for.

Hope this helps...

Marcster.

Shazam
12-28-2005, 09:59 AM
How about this ?


Sub FindModule()
Dim VBComp As VBIDE.VBComponent
Dim Module As String
On Error Resume Next
Module = InputBox("What module do you wish to open?")
Application.ActiveWorkbook.VBProject.VBComponents.Import ("C:\" & Module & ".bas")
If Err = "53" Then
MsgBox "Module do not exist,please try again."
End If

End Sub



The code wont work until you install your VBA reference. Go to tools in your VBA screen and click on References and look for:

Microsoft Visual Basic for Applications Extenssiblity 5.3

Then check mark it.

Then go to your regular excel screen and go to Tools- Macro -Security- trusted publishers and check mark in the box that says:

Trust access to Visual Basic Prodject.