VBA Challenge UserForm

%3CLINGO-SUB%20id%3D%22lingo-sub-1884028%22%20slang%3D%22en-US%22%3EVBA%20Challenge%20UserForm%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1884028%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20goals%2Fchallenges%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20When%20adding%20a%20new%20project%2C%20I%20want%20it%20to%20go%20as%20the%20last%20item%20in%20the%20same%20cost%20center%20group.%20(For%20example%2C%20if%20I%20have%20a%20new%20project%20on%20Cost%20Center%202000%2C%20the%20new%20line%20would%20be%20in%20row%207.%20The%20new%20project%20should%20go%20on%20the%20last%20line%20of%20the%20group%20of%20the%20same%20cost%20center).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20While%20Tracker%20LC%20is%20the%20main%20one%20where%20the%20new%20project%20will%20go%2C%20I%20want%20the%20new%20line%20to%20be%20duplicated%20in%20%22Tracker%20USD%22%20(with%20the%20formulas%20intact).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3)%20I%20would%20like%20the%20form%20to%20refresh%20back%20to%20blank%20state%20after%20each%20use.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1884028%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1884242%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Challenge%20UserForm%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1884242%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867877%22%20target%3D%22_blank%22%3E%40El_Cumulo305%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%20version.%20I%20corrected%20the%20tab%20order%20of%20the%20userform%2C%20and%20modified%20the%20code.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1890513%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Challenge%20UserForm%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1890513%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20answer.%20The%20code%20is%20working%20beautifuly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20in%20the%20dummy%20file%20that%20I%20sent%2C%20I%20forgot%20to%20include%20some%20additional%20columns%2C%20which%20cause%20formulas%20issues%20when%20I%20use%20the%20userform%20on%20my%20documents.%20I%20wasn't%20able%20to%20replicate%20the%20code%20to%20cover%20the%20extra%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdditionally%2C%20is%20it%20possible%20to%20have%20the%20currency%20always%20set%20as%20%22USD%22%20in%20the%20%22Tracker%20USD%22%20for%20the%20new%20lines.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESincerely%2C%3C%2FP%3E%3CP%3EPhilippe%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1890869%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Challenge%20UserForm%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1890869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867877%22%20target%3D%22_blank%22%3E%40El_Cumulo305%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

My goals/challenges:

@Hans Vogelaar 

 

1) When adding a new project, I want it to go as the last item in the same cost center group. (For example, if I have a new project on Cost Center 2000, the new line would be in row 7. The new project should go on the last line of the group of the same cost center).

 

2) While Tracker LC is the main one where the new project will go, I want the new line to be duplicated in "Tracker USD" (with the formulas intact).

 

3) I would like the form to refresh back to blank state after each use.

 

 

6 Replies

@El_Cumulo305 

Try this version. I corrected the tab order of the userform, and modified the code.

Hello @Hans Vogelaar,

 

Thank you very much for your answer. The code is working beautifuly. 

 

However, in the dummy file that I sent, I forgot to include some additional columns, which cause formulas issues when I use the userform on my documents. I wasn't able to replicate the code to cover the extra columns. 

 

Additionally, is it possible to have the currency always set as "USD" in the "Tracker USD" for the new lines. 

 

Thank you again.

 

Sincerely,

Philippe 

 

 

 

@El_Cumulo305 

See the attached version.

@Hans Vogelaar 

 

Thanks you very much.

 

Just one last thing; when I add a project on the last line, the format with the vertical black line do not follow. See attached screenshot:

 

El_Cumulo305_0-1605541001837.png

 

Can you please fix this.


Thanks.

 

Sincerely

 

@El_Cumulo305 

Here is a new version.

@Hans Vogelaar 

 

Thank you ! It's absolutely perfect ! 

 

Sincerely,

Philippe