Hidden macro names from Assign Macro Window

[YasserKhalil]

Hello everyone
I know that to hide a macro name from "Assign Macro" window (Alt + F8) we can type the word Private before Sub plus macro name
Private Sub Test()

I have a weird file that has no macro names in the Assign Macro window .. although there is no private before Sub nor before Module
Please have a look at it

[LJMetzger]

Hi Yasser,

Fortunately or unfortunately your file exhibits the same behavior for me.

You seem to have some kind of corruption. I have seen similar symptoms when there is a duplication somewhere. This kind of problem can often take a long time to resolve.

I have the following suggestions.
a. Make a backup copy of your file.
b. Open your file. Go to VBA. Export (and delete) all code modules.
c. Import a module.
d. Go to Excel to see if you can see the Macros (ALT F8).
e. If successful, repeat steps c. and d., one Module at a time.
f. If unsuccessful, the imported module is the cause of the problems.

Lewis

[PCI]

"I have a weird file that has no macro names in the Assign Macro window .. although there is no private before Sub nor before Module"
but what is your need?
it is possible to see the macros : in Visual Basic select view > Project Explorer

[YasserKhalil]

Thanks a lot Mr. Lewis for this great solution
I applied the steps and delete each module one by one and the same with userform modules ..
After that saved my file and open it again and begin to import a module one after another and checked the Alt + F8 window ..
There is no module which cause any error
After importing all modules and userforms everything is ok now.. That's weird

I thought of a quick way to such a problem .. so I thought of exporting all modules (userforms and sheet modules and standard mouldes) then delete all in on shot
But I don't know how to import all the modules from specific path (The same workbook path and the folder name which has all the modules inside)
I need your help at this point
Here' the first part which exports all modules and then delete all ...

Please Login or Register  to view this content.

[LJMetzger]

Hi Yasser,

Thanks for sharing the VBE code. I know it is more efficient to do it with VBE, but I don't like referencing the VBE Library unless I absolutely have to.

After importing all modules and userforms everything is ok now.. That's weird

I see similar type of behavior all the time. I do most of my VBA in Excel 2003. When there is an Active X control in a questioner's file (Excel 2007+), the file acts like it is corrupt in Excel 2003. To correct the problem I have to:
a. Save the file as a .xls file.
b. Delete all the 'Active X' controls on a sheet.
c. Make a copy of the sheet
d. Delete the Original Sheet
e. Rename the copy to the name of the Original Sheet.
f. Insert 'Active X' controls if necessary.
g. Save and close the file.
h. When the file is opened, the file acts normally.

Lewis

[PCI]

Do you know that if you open twice Excel and load in each Excel a different file you can open 2 VBA windows and just pick up a module from one VBA and put it in the other VBA: This could simplify the manipulations

[YasserKhalil]

Thank a lot Mr. Lewis for the logic steps
In fact I like quick solutions for problems..
Now I have solved half of the problem.. Just additional step to import all modules from the folder (exported in the last step)
Is there an easy way to import all modules in one shot...?

Another approach I tried to use VBA Cleaner (32 Bit only) but nothing repaired ..
It still only one way to solve the problem .. is to remove all modules and save the workbook then open it and import all the modules again
Thank you very much Mr. PCI for useful information ..It may help me

[LJMetzger]

Ron de Bruin to the rescue: http://www.rondebruin.nl/win/s9/win002.htm

It will import all the modules in the specified path.

[YasserKhalil]

I tried this link earlier but I couln't execute it
How to specify the path for my folder which store my exported modules
I tried this change

Please Login or Register  to view this content.

But it doen't work ..

I need a simple way which uses file dialog to select my folder then import desired modules
Can you help me doing that task?

[LJMetzger]

The easiest way is to:
a. Create a new Folder that is empty.
b. Put the file with the code in that folder
c. Set the path in the VBA code to:

Please Login or Register  to view this content.

d. All the imported and exported files will be in the same folder.
e. After you're done, move your file to whatever folder you like.

[YasserKhalil]

Thank you very much Mr. Lewis
I worked now very well ..