Over the years I have made various user forms which I havent used for a while and dont remember which files they are in. I am using excel 2003 on win 7 Is there any way of listing the files which contain user forms. I am going to make a new user form and would like to incorporate some of the features from previous forms. Is this possible? Thank you.
How do I find files which contain user forms.
- Thread starter Lymm
- Start date
SirJB7
Excel Rōnin
Hi, Lymm!
There have been times when I would have given more than... 1 buck?... for having such a tool. So someone had to write it. Here it is:
http://dl.dropbox.com/u/60558749/How%20do%20I%20find%20files%20which%20contain%20user%20forms.%20%28for%20Lymm%20at%20chandoo.org%29.xlsm
Just place this workbook in any folder, close all Excel instances that might be running, open this workbook, press the cyan command button... et voilá, c'est tout!
Screen is freezed so give it time to work, will beep when finish. It works with all *.xl* files, and with my chandoo folder it didn't crashed... and I have such files in there...
As to be useful for me, it not only tells you what files have user forms, but it tells you everything about worksheets, charts, macro & dialog sheets, modules, class modules and userforms: count, order & name for each one.
It has certain constraints, as follows:
- it doesn't work with subfolders
- if a workbook opens other workbooks, it might duplicate the data or cancel at that opened file
- if a workbook has an auto open macro, it will run... and only you know what might it do
- if a workbook is password protected a message is set in last column
Hope you like it. Now it's part of my swiss knife ...or that was NetCat 1.11?
Regards!
There have been times when I would have given more than... 1 buck?... for having such a tool. So someone had to write it. Here it is:
http://dl.dropbox.com/u/60558749/How%20do%20I%20find%20files%20which%20contain%20user%20forms.%20%28for%20Lymm%20at%20chandoo.org%29.xlsm
Just place this workbook in any folder, close all Excel instances that might be running, open this workbook, press the cyan command button... et voilá, c'est tout!
Screen is freezed so give it time to work, will beep when finish. It works with all *.xl* files, and with my chandoo folder it didn't crashed... and I have such files in there...
As to be useful for me, it not only tells you what files have user forms, but it tells you everything about worksheets, charts, macro & dialog sheets, modules, class modules and userforms: count, order & name for each one.
It has certain constraints, as follows:
- it doesn't work with subfolders
- if a workbook opens other workbooks, it might duplicate the data or cancel at that opened file
- if a workbook has an auto open macro, it will run... and only you know what might it do
- if a workbook is password protected a message is set in last column
Hope you like it. Now it's part of my swiss knife ...or that was NetCat 1.11?
Regards!
NARAYANK991
Excel Ninja
Hi Pablo ,
Very useful utility to have in one's stock. Thanks for sharing.
Narayan
Very useful utility to have in one's stock. Thanks for sharing.
Narayan
SirJB7
Excel Rōnin
Hi, NARAYANK991!
I really cursed and blasphemed many times in several languages that I even didn't know for not finding something that I knew it was "there" but I couldn't locate it anyhow. So this time have been 45' well spent: Lymm wanted that, I strongly needed that and certainly many more will find it useful.
Regards!
I really cursed and blasphemed many times in several languages that I even didn't know for not finding something that I knew it was "there" but I couldn't locate it anyhow. So this time have been 45' well spent: Lymm wanted that, I strongly needed that and certainly many more will find it useful.
Regards!
SirJB7
Excel Rōnin
Hi, Lymmm!
I can't test it on an Excel version 2003, but I think I didn't use anything very new from 2007/2010 versions.
To check if the command button has related code or not (that it has), go to the VBA editor (Alt-F11) and check within the Workbooks worksheet window if there is this code:
-----
`Option Explicit
Private Sub cmdProcessFolder_Click()
LoadWorkbooksInfo
End Sub
-----
There is a module called Módulo1 too which holds the main code.
If you can't find them, just advise and I'll send you a .xls file.
Regards!
I can't test it on an Excel version 2003, but I think I didn't use anything very new from 2007/2010 versions.
To check if the command button has related code or not (that it has), go to the VBA editor (Alt-F11) and check within the Workbooks worksheet window if there is this code:
-----
`Option Explicit
Private Sub cmdProcessFolder_Click()
LoadWorkbooksInfo
End Sub
-----
There is a module called Módulo1 too which holds the main code.
If you can't find them, just advise and I'll send you a .xls file.
Regards!
Hi SrJB7 I found the code and clicked on run. (It does not run from the button, but I can probably fix that.) Lots of scary processing went on, I think it worked. I am about to check. Thank you. How do I tell it which folder to process. It has processed one folder called "ExcelStuff", not sure how it found that one. Ok that folder was where I had the file saved. What is the best way to use this then. I did a save as and saved it in a different folder which it then processed, but I dont think this is quite what I should be doing??
SirJB7
Excel Rōnin
Hi, Lymm!
If when clicking on button code isn't executed, check macro configuration (in 2010 Version, File tab, Options, Trust Center, Trust Center Setup, Trusted Locations and/or Macro Configuration: in this last one, at least set to Disable All With Notification and set Programmer Macro Setup to on -trust in access to VBA project object model-) or design mode (Programmer tab, Controls group, Design Mode icon, and set to off -unselected-).
I assume that if you can run the macro, first option isn't necessary and your issue must be the second one. If not, I don't guess what might be.
About the folder, it always works on the actual folder where the file is stored and doesn't recurse on subfolders (see my post http://chandoo.org/forums/topic/how-do-i-find-files-which-contain-user-forms#post-24193). So yes, that's the process, copy the file to the folder to be examined, open it, click on button, and nothing more.
Regards!
If when clicking on button code isn't executed, check macro configuration (in 2010 Version, File tab, Options, Trust Center, Trust Center Setup, Trusted Locations and/or Macro Configuration: in this last one, at least set to Disable All With Notification and set Programmer Macro Setup to on -trust in access to VBA project object model-) or design mode (Programmer tab, Controls group, Design Mode icon, and set to off -unselected-).
I assume that if you can run the macro, first option isn't necessary and your issue must be the second one. If not, I don't guess what might be.
About the folder, it always works on the actual folder where the file is stored and doesn't recurse on subfolders (see my post http://chandoo.org/forums/topic/how-do-i-find-files-which-contain-user-forms#post-24193). So yes, that's the process, copy the file to the folder to be examined, open it, click on button, and nothing more.
Regards!
SirJB7
Excel Rōnin
Hi, Lymm!
Sincerely I don't remember, I switched to 2007 when it was released and did the same with 2010, so I don't know if there's such a change in VBA since 2003 version.
Let's do something, I'll upload a file that has 3 worksheets, 2 charts, 3 macros, 2 intl macros, 1 dialog 4 modules, 2 class modules and 3 userforms. You'll find in two versions, .xlsx (2007/2010) and .xls (2003). I'll upload too the original files as I changed a bit MacroIntl data.
If you get the same data as above for both, it's all Ok. If you get it only for .xlsx version or for .xls version, well, I'd have to get my hands on a computer with an older Excel version. Do you use TeamViewer, LogMeIn or any other remote support software in that machine? If so, I could enter to debug the code to check a couple of values; if not, you should do it by yourself following the instructions I'd provide.
First, here are the 3 files:
- the one with the button
- the .xlsx test
- the .xls test
In the first you can see what data it retrieved in a 2010 version. Then you should run it on a clean folder with 3 files and compare the output. If similar, we're done; if not, then plan B.
Link:
http://dl.dropbox.com/u/60558749/Lymm.rar
Regards!
Sincerely I don't remember, I switched to 2007 when it was released and did the same with 2010, so I don't know if there's such a change in VBA since 2003 version.
Let's do something, I'll upload a file that has 3 worksheets, 2 charts, 3 macros, 2 intl macros, 1 dialog 4 modules, 2 class modules and 3 userforms. You'll find in two versions, .xlsx (2007/2010) and .xls (2003). I'll upload too the original files as I changed a bit MacroIntl data.
If you get the same data as above for both, it's all Ok. If you get it only for .xlsx version or for .xls version, well, I'd have to get my hands on a computer with an older Excel version. Do you use TeamViewer, LogMeIn or any other remote support software in that machine? If so, I could enter to debug the code to check a couple of values; if not, you should do it by yourself following the instructions I'd provide.
First, here are the 3 files:
- the one with the button
- the .xlsx test
- the .xls test
In the first you can see what data it retrieved in a 2010 version. Then you should run it on a clean folder with 3 files and compare the output. If similar, we're done; if not, then plan B.
Link:
http://dl.dropbox.com/u/60558749/Lymm.rar
Regards!
NARAYANK991
Excel Ninja
Hi Pablo ,
I downloaded the .rar file from your most recent post :
http://dl.dropbox.com/u/60558749/Lymm.rar
and executed the macro in your .xlsm file ( by clicking on the button ) and it gives the right result. No problem.
Narayan
I downloaded the .rar file from your most recent post :
http://dl.dropbox.com/u/60558749/Lymm.rar
and executed the macro in your .xlsm file ( by clicking on the button ) and it gives the right result. No problem.
Narayan
NARAYANK991
Excel Ninja
Hi Lymm ,
The problem is the "Process Folder" command button ; I do not know how it happened or what happened , but the button in your file is just a shape , not a command button.
What you can do is click on the DEVELOPER menu item , click on the INSERT option , click on the first item ( BUTTON ) in the FORM CONTROLS section.
Right click on this newly created button , click on the ASSIGN MACRO menu item , and assign the following macro from the list : LoadWorkbooksInfo
Now , if you click on the button , it should process the files and display the results.
Narayan
The problem is the "Process Folder" command button ; I do not know how it happened or what happened , but the button in your file is just a shape , not a command button.
What you can do is click on the DEVELOPER menu item , click on the INSERT option , click on the first item ( BUTTON ) in the FORM CONTROLS section.
Right click on this newly created button , click on the ASSIGN MACRO menu item , and assign the following macro from the list : LoadWorkbooksInfo
Now , if you click on the button , it should process the files and display the results.
Narayan
NARAYANK991
Excel Ninja
Hi Lymm ,
Sorry , but when I run the same macro ( LoadWorkbooksInfo ) in my folders , the userforms are listed.
Narayan
Sorry , but when I run the same macro ( LoadWorkbooksInfo ) in my folders , the userforms are listed.
Narayan
NARAYANK991
Excel Ninja
Hi Lymm ,
I have Excel 2007 ; I tried out the macro from your file.
Narayan
I have Excel 2007 ; I tried out the macro from your file.
Narayan
NARAYANK991
Excel Ninja
Hi Lymm ,
I don't think we should give up ! Can you try the following and see what you get ?
1. Open the workbook which contains the userform , and in the Immediate Window of VBA , type in the following :
[pre]
[/pre]
See what number you get.
Replace the number 1 in step 2 above , with numbers 2 , 3 ,... till the number you got in step 1 above e.g. if the count in step 1 was 6 , try step 2 with numbers 1 , 2 , 3 , 4 , 5 and 6.
If the result of step 2 is 3 any time , it means Excel is recognizing the userform in the workbook ; there is no reason why SirJB7's macro shouln't work correctly.
Narayan
I don't think we should give up ! Can you try the following and see what you get ?
1. Open the workbook which contains the userform , and in the Immediate Window of VBA , type in the following :
[pre]
Code:
?ActiveWorkbook.VBProject.VBComponents.Count
See what number you get.
2. Type in the following :
?ActiveWorkbook.VBProject.VBComponents(1).TypeSee what number you get.
Replace the number 1 in step 2 above , with numbers 2 , 3 ,... till the number you got in step 1 above e.g. if the count in step 1 was 6 , try step 2 with numbers 1 , 2 , 3 , 4 , 5 and 6.
If the result of step 2 is 3 any time , it means Excel is recognizing the userform in the workbook ; there is no reason why SirJB7's macro shouln't work correctly.
Narayan