Home

Run-time error 9 after copying a macro to a new project in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-877563%22%20slang%3D%22en-US%22%3ERun-time%20error%209%20after%20copying%20a%20macro%20to%20a%20new%20project%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-877563%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20very%20simple%20macro%20as%20below%20to%20run%20a%20UserForm%20(formHygiene)%20in%20VBA.%20It%20works%20fine%20in%20one%20file.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20451px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133657i052D2AC991B59881%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture1.JPG%22%20title%3D%22Capture1.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20wanted%20to%20use%20it%20for%20other%20Excel%20file%20and%20copied%20the%20macro%20and%20the%20UserForm%20to%20other%20(by%20dragging%20them%20into%20a%20new%20VBA%20Project%20in%20VBA).%20Then%20when%20I%20ran%20the%20macro%20in%20the%20new%20Excel%2C%20I%20am%20getting%20a%20%22Run-time%20error%209%3A%20Subscript%20out%20of%20range%22.%20When%20I%20tried%20to%20debug%2C%20it%20points%20to%20the%20line%20%22formHygiene.Show%22%20in%20the%20macro.%20So%20I%20think%20the%20error%20can%20be%20due%20to%20the%20new%20file%20not%20seeing%20the%20UserForm%20(formHygiene)%20somehow.%20But%20the%20UserForm%20is%20shown%20for%20the%20new%20one%2C%20as%20you%20see%20in%20the%20screenshot%20below.%20Can%20anyone%20tell%20me%20why%20I%20am%20getting%20this%20error%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20393px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133659iF60188FF0588EEDD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture2.JPG%22%20title%3D%22Capture2.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3EDavid%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-877563%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-878524%22%20slang%3D%22en-US%22%3ERe%3A%20Run-time%20error%209%20after%20copying%20a%20macro%20to%20a%20new%20project%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878524%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415908%22%20target%3D%22_blank%22%3E%40seolac%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20to%20reproduce%20that%2C%20but%20I%20didn't%20see%20any%20error!%3C%2FP%3E%3CP%3EIf%20the%20new%20workbook%20has%20a%20different%20structure%2C%20so%20it's%20possible%20that%20the%20code%20in%20the%20UserForm%20refers%20to%20something%20missing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20watch%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DAC3GgPOalLM%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Evideo%3C%2FA%3E%20to%20learn%20more%20about%20the%20Run-time%20error%209%20and%20its%20common%20reasons.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
seolac
Occasional Visitor

I have a very simple macro as below to run a UserForm (formHygiene) in VBA. It works fine in one file.

Capture1.JPG

I wanted to use it for other Excel file and copied the macro and the UserForm to other (by dragging them into a new VBA Project in VBA). Then when I ran the macro in the new Excel, I am getting a "Run-time error 9: Subscript out of range". When I tried to debug, it points to the line "formHygiene.Show" in the macro. So I think the error can be due to the new file not seeing the UserForm (formHygiene) somehow. But the UserForm is shown for the new one, as you see in the screenshot below. Can anyone tell me why I am getting this error?

 

Capture2.JPG

Thanks!

David

1 Reply

@seolac

 

Hi,

 

I've tried to reproduce that, but I didn't see any error!

If the new workbook has a different structure, so it's possible that the code in the UserForm refers to something missing.

 

Please watch this video to learn more about the Run-time error 9 and its common reasons.

 

Hope that helps

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies