SOLVED

Procedure is greyed out in VBA Insert pulldown

Copper Contributor

I am unfamiliar with VBA but find myself in dire need of its LTRIM function as I have imported my Excel project plan into Project (original Project file is corrupted) complete with leading spaces at every Task.  I have enabled Developer, click on Visual Basic/Insert but Procedure is greyed out.   Any ideas?

 

Windows 11

MS Project V2310 Build 16924.20150

6 Replies
Smeac81,
For reference, there is a new update for Project dated Nov 29 although it shouldn't have any bearing on your issue.

How exactly did you "import" your Excel plan into Project? You said your original Project file is corrupt. What is the relationship between your "original" file and the "imported" file? (I'm just trying to understand what you are doing.)

Procedures (i.e. macros) are resident in modules (i.e. a module may contain multiple macros). So what you want to do is to insert a module. Once a module is created, then you will be able to insert a procedure but, I just insert a module and then start writing the macro code in the VBE editing window. I personally have never used the insert "procedure" function.

You indicate you are unfamiliar with VBA, so what exactly do you want to do?

John
Hi John,

Thank you for the reply. Yes, as you will see, very unfamiliar with VBA, I will apologize for that from the start.

I did discover that going the module route works and I don't need to use procedure. I am now able to start writing the code but can't get very far due to my lack of knowledge of VBA.

I actually had two versions of my Project file and both became corrupted - went from 1mb to 55mb, but I had earlier exported the Project file to Excel so others could see it that did not have Project. At that point, I did a copy/paste into Project and that is where the leading spaces were introduced I believe. I did try the Import Wizard in Project but that failed.

So now I want to use the LTRIM function in VBA to get rid of the leading spaces. Does that help?
Smeac81,
I think the first issue to address is why your two versions of the Project file got so bloated. Do you have a lot of custom formatting, a huge number of resources, or other facets about your file editing that might cause them to bloat?

Just copying and pasting should not introduce leading spaces. Are you sure the leading spaces weren't added in Excel?

You said the import wizard failed. How so? How exactly did you attempt the import?

If possible would you be willing to send me your file?

John
best response confirmed by Dale Howard (MVP)
Solution
While you have all of the tasks (and summaries) in excel, and they all have leading spaces, why not remove them there before copying them and pasting them into MSP?
You could use a nested function like =proper(clean(trim))), and clean it all up in one go.
Proper() converts test to title case (or proper noun case), with caps on first character of each word.
Clean() removes unprintable characters, whatever they are but anyway they are removed.
Trim() removes leading, trailing and double spaces.

After, you just go through and re-do the WBS indents in MSP.
Thank you John/Trevor for your inputs. Yes all good input in hindsight, this helps me understand how best to deal with this in the future. I did manually delete the leading spaces as I had a time crunch to make a meeting deadline.

The original problem with the files being corrupted is elusive and IT (inside and 3rd party) say the laptop is just fine although it crashed in the meeting. But what do I know? So everything is fine as long as I don't use the laptop - using my personal laptop now.

Thanks again - Joe
Smeac81,
You're welcome and thanks for the feedback.
John
1 best response

Accepted Solutions
best response confirmed by Dale Howard (MVP)
Solution
While you have all of the tasks (and summaries) in excel, and they all have leading spaces, why not remove them there before copying them and pasting them into MSP?
You could use a nested function like =proper(clean(trim))), and clean it all up in one go.
Proper() converts test to title case (or proper noun case), with caps on first character of each word.
Clean() removes unprintable characters, whatever they are but anyway they are removed.
Trim() removes leading, trailing and double spaces.

After, you just go through and re-do the WBS indents in MSP.

View solution in original post