How do I find files which contain user forms.
- Thread starter Lymm
- Start date
SirJB7
Excel Rōnin
Hi, Lymm!
About the button, you should add it from the Insert Controls tool and choose ActiveX and not FormControl button type. The first behaves like a normal object with properties as a button on an userform (or in Visual Basic language). The second one draws a shape to which you can add a macro. Even if with both of them you can get the same result (code executed upon click within a rectangle), the ActiveX control is preferred as it's easier to handle properties and to standardize code assignation in a workbook.
If you wish to use the FormControl button type, right click on it, Assign Macro, and point to "LoadWorkbooksInfo".
Regards!
PS: sorry for the absence, but Monday & Tuesday were non-working days, so I took a little break.
PS2: in my country "everything's fine" it's an utopia, so I can't understand what kind of celebration of the Worker's Day (Tue, May 1st) is making non-workable the sandwich day (Mon, Apr 30th) to have a 4 days long week-end... demagogy for sure...
About the button, you should add it from the Insert Controls tool and choose ActiveX and not FormControl button type. The first behaves like a normal object with properties as a button on an userform (or in Visual Basic language). The second one draws a shape to which you can add a macro. Even if with both of them you can get the same result (code executed upon click within a rectangle), the ActiveX control is preferred as it's easier to handle properties and to standardize code assignation in a workbook.
If you wish to use the FormControl button type, right click on it, Assign Macro, and point to "LoadWorkbooksInfo".
Regards!
PS: sorry for the absence, but Monday & Tuesday were non-working days, so I took a little break.
PS2: in my country "everything's fine" it's an utopia, so I can't understand what kind of celebration of the Worker's Day (Tue, May 1st) is making non-workable the sandwich day (Mon, Apr 30th) to have a 4 days long week-end... demagogy for sure...
Hi SirJB7, I now have the Cmd button sorted and the macro runs from it ok but I still do not get any counts of the user forms. If I run the macro from the code area I can see it going through the files and forms but I am not getting a count of the user forms. I get a pop up message in the middle telling me I have a new menu, dont know if that is significant or not.
SirJB7
Excel Rōnin
Hi, Lymm!
I don't have access to any PC running Excel 2003 version, so I can't reproduce your issue in the same environment.
A couple of things:
a) have you tried what NARAYANK991 posted?
Open the workbook with user forms, press Alt-F11 and from the Immediate window, type each line separately and press Enter (if you don't see the immediate window, press Ctrl-G): I added a third line.
1) ?ActiveWorkbook.VBProject.VBComponents.Count
2) ?ActiveWorkbook.VBProject.VBComponents(1).Type
3) for z=1 to ActiveWorkbook.VBProject.VBComponents.Count:?z;ActiveWorkbook.VBProject.VBComponents(z).name: next z
b) then copy whole output from immediate window and paste it here, please (from the first line typed)
Regards!
I don't have access to any PC running Excel 2003 version, so I can't reproduce your issue in the same environment.
A couple of things:
a) have you tried what NARAYANK991 posted?
Open the workbook with user forms, press Alt-F11 and from the Immediate window, type each line separately and press Enter (if you don't see the immediate window, press Ctrl-G): I added a third line.
1) ?ActiveWorkbook.VBProject.VBComponents.Count
2) ?ActiveWorkbook.VBProject.VBComponents(1).Type
3) for z=1 to ActiveWorkbook.VBProject.VBComponents.Count:?z;ActiveWorkbook.VBProject.VBComponents(z).name: next z
b) then copy whole output from immediate window and paste it here, please (from the first line typed)
Regards!
Hi SirJB7
After I entered the first row I got Run-time error 1004
"Programmatic access to Visual Basic Project is not trusted"
After the second line was entered I got Run-time error 1004
Application-defined or object defined error.
After the third line was entered i got the same error as for 2
?ActiveWorkbook.VBProject.VBComponents.Count
?ActiveWorkbook.VBProject.VBComponents(1).Type
for z=1 to ActiveWorkbook.VBProject.VBComponents.Count:?z;ActiveWorkbook.VBProject.VBComponents(z).name: next z
After I entered the first row I got Run-time error 1004
"Programmatic access to Visual Basic Project is not trusted"
After the second line was entered I got Run-time error 1004
Application-defined or object defined error.
After the third line was entered i got the same error as for 2
?ActiveWorkbook.VBProject.VBComponents.Count
?ActiveWorkbook.VBProject.VBComponents(1).Type
for z=1 to ActiveWorkbook.VBProject.VBComponents.Count:?z;ActiveWorkbook.VBProject.VBComponents(z).name: next z
SirJB7
Excel Rōnin
Hi, Lymm!
The first message seems to be a security issue regarding macros and its enabling.
Try with the info in this article:
http://office.microsoft.com/en-us/change-macro-security-settings-in-excel-HP010342232.aspx
Set Disable All Macros With Notification, Trust Access To The VBA Project Object Model, both to on. And then try again.
BTW, do you use TeamViewer or LogMeIn? Those are applications for remote support. The first one is very simple, you install it, you run it, it gives a number for your PC's session, you send (mail, not here) the number, and I can enter and perform these and further operations accessing to your keyboard and mouse. You'll be seeing what I do, and you can terminate the session at any time. When finished, you might opt for uninstall the application, if you wish.
Regards!
The first message seems to be a security issue regarding macros and its enabling.
Try with the info in this article:
http://office.microsoft.com/en-us/change-macro-security-settings-in-excel-HP010342232.aspx
Set Disable All Macros With Notification, Trust Access To The VBA Project Object Model, both to on. And then try again.
BTW, do you use TeamViewer or LogMeIn? Those are applications for remote support. The first one is very simple, you install it, you run it, it gives a number for your PC's session, you send (mail, not here) the number, and I can enter and perform these and further operations accessing to your keyboard and mouse. You'll be seeing what I do, and you can terminate the session at any time. When finished, you might opt for uninstall the application, if you wish.
Regards!
SirJB7
Excel Rōnin
Hi, Lymm!
Here's the link to the (final, I hope!) version of the file for documenting Excel files, with subfolder processing. It has the same .xlsm file than previous .rar link, and replaces Excel file:
http://dl.dropbox.com/u/60558749/How%20do%20I%20find%20files%20which%20contain%20user%20forms.%20%28for%20Lymm%20at%20chandoo.org%29.xlsm
Just advise if any issue.
Regards!
PS: Give a look at next post for further info.
Here's the link to the (final, I hope!) version of the file for documenting Excel files, with subfolder processing. It has the same .xlsm file than previous .rar link, and replaces Excel file:
http://dl.dropbox.com/u/60558749/How%20do%20I%20find%20files%20which%20contain%20user%20forms.%20%28for%20Lymm%20at%20chandoo.org%29.xlsm
Just advise if any issue.
Regards!
PS: Give a look at next post for further info.
SirJB7
Excel Rōnin
@NARANYANK991
Hi!
If you happen to read this (bis, again and finally, I hope!), download the original file from link of previous post, as it's the final version with this additional features:
- select folder
- recursive subfolders processing
- handle protected VBA projects (just message eh!)
- object code inspector (select a cell with object name and press button)
Try it and let me know any issue.
Regards!
PS: Tomorrow I'll post it in a new clean topic.
Hi!
If you happen to read this (bis, again and finally, I hope!), download the original file from link of previous post, as it's the final version with this additional features:
- select folder
- recursive subfolders processing
- handle protected VBA projects (just message eh!)
- object code inspector (select a cell with object name and press button)
Try it and let me know any issue.
Regards!
PS: Tomorrow I'll post it in a new clean topic.
NARAYANK991
Excel Ninja
Hi Pablo ,
Thanks for sharing. I have already downloaded your file , and will check it out.
A first impression : do you think the analysis of the subfolders can be made optional using a checkbox ? If checked , include subfolders in the analysis , else do not ?
Narayan
Thanks for sharing. I have already downloaded your file , and will check it out.
A first impression : do you think the analysis of the subfolders can be made optional using a checkbox ? If checked , include subfolders in the analysis , else do not ?
Narayan
Hi Sir JB7, Great file. I did have to rebuild the buttons and check box as converting the file for excel 2003 seems to disconnect the code from them. I also got a couple of error messages. One about declaring constants which went away when I put in const xlSortOnvalues = 0, but then it stalled again at the sort sheets first line
With ActiveWorkbook.Worksheets("Workbooks").Sort
The error was "Run-time error 438 Object doesnt support this property or method" Didnt know what to do here so cheated and put in "on error resume next and it finished running. It probably doesnt like running with 2003 but it listed all my files and forms etc in the folder which was very handy. THe form that shows all the codes is very cool.
With ActiveWorkbook.Worksheets("Workbooks").Sort
The error was "Run-time error 438 Object doesnt support this property or method" Didnt know what to do here so cheated and put in "on error resume next and it finished running. It probably doesnt like running with 2003 but it listed all my files and forms etc in the folder which was very handy. THe form that shows all the codes is very cool.
SirJB7
Excel Rōnin
@NARAYANK991
Hi!
Good idea. Done yet. Same links (both). You know the routine, don't you? Just follow the yellow line... ;-)
Regards!
@Lymm
Hi!
Well, I needed it, so I profited your post and killed two birds with a single shot.
Don't forget thanking NARAYANK991 for having to download it again and perform all the stuff for running it on ancient versions of Excel. :=)
Maybe this is the new final. Same link, blah, blah, ...
Regards!
Hi!
Good idea. Done yet. Same links (both). You know the routine, don't you? Just follow the yellow line... ;-)
Regards!
@Lymm
Hi!
Well, I needed it, so I profited your post and killed two birds with a single shot.
Don't forget thanking NARAYANK991 for having to download it again and perform all the stuff for running it on ancient versions of Excel. :=)
Maybe this is the new final. Same link, blah, blah, ...
Regards!