test for module...

[robfo0]

Hi again, i got this code from a http://www.cpearson.com/excel/vbe.htm given to me by al, i have a question about this piece of code:

this test for the existance of a module in the workbook its run from. My question is, is there a way to modify it to test for the module in ALL the workbooks i have open (say if for example i had 3 open)? heres the code:

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

thanks

[brettvba]

if you select the workbook before each run it would check that active workbook.

[robfo0]

well, its a little more complicated than that. There wont always be another workbook open, and the reason im testing for a module is if there IS another workbook open, it will have different names all the time. any other ideas?

[brettvba]

Function ModuleExists(ModuleName As String) As Boolean
On Error Resume Next
ModuleExists = Len( _
ThisWorkbook.VBProject.VBComponents(ModuleName).Name) <> 0
If ModuleExists = True Then
MsgBox "Module Exists"
End If
End Function


Sub runfunction()
ModuleExists ("Cat")
End Sub

That would tell you if a module named cat was in your current workbook
what are you actually trying to do
it checks the active workbook do you want it to check closed workbooks?

[brettvba]

Public wkbname
Function ModuleExists(ModuleName As String) As Boolean
On Error Resume Next

Workbooks(wkbname).Select
ModuleExists = Len( _
ThisWorkbook.VBProject.VBComponents(ModuleName).Name) <> 0
If ModuleExists = True Then
MsgBox "Module Exists in " & wkbname
End If
End Function


Sub runfunction()

wkbname = ("Book1.xls")
ModuleExists ("Cat")
End Sub

or this macro to specify one particular book
or replace with an inputbox if required

[ This Message was edited by: brettvba on 2002-04-18 21:57 ]

[robfo0]

the code you posted will ONLY check the workbook that is open for the module. I dont want to test the workbook its run from, i want to test if there are ANY workbooks open that contain the module. I would test for the workbook itself, but like i said the names are always different.

Ultimately what im trying to do is:
-I have a workbook (Book1) open which contains data.
-I want to use a button to open another workbook (Book2).
-Book2 contains a userfor which starts when it opens, asks a few questions, then opens yet another book (Book3) depending on what the user selected in the userform, then closes Book2
-i want to copy information from Book1 to Book3.

the problem is that i cant copy the information until the user selects the appropriate information in the userform in Book2.

my first idea was to test if book2 was open, but i had some problems with that. i wasnt able to get it worked out in my first idea here: http://www.mrexcel.com/board/viewtop...c=5261&forum=2, so im trying another approach.

[robfo0]

i think i got it although i dont know if this solves my overall problem, i wont know until i get to work to try it out tomorrow. Heres the code i came up with:

Dim oWkBook As Workbook

Sub Macro1()
Dim FromFile As Boolean

FromFile = False

For Each oWkBook In Workbooks
If ModuleExists("opencopy") = True Then
FromFile = True
Exit For
End If
Next

If FromFile = True Then
MsgBox ("code is there!")
End If

End Sub

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

[brettvba]

I don't know maybe you have got the code wrong on the ok button just a thought