Tony has skills. He has created a great workbook, done his VBA Coding well and has incorporated a Userform into his work to make things simple and smooth... but the Userform won't launch. Clicking the 'Launch' button on the user side produces a 'Compile Error: Expected Function or Variable'. What is causing the error - it's a single, short line of code!? Follow along with Episode #1598 to find out what the issue is and how to correct it quickly.
...This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast - Userform Won't Launch Hey! Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question is sent in by Tony.
Tony is coming up this VBA learning curve.
He’s doing great things with his reports but he sent me one - he said “I'm banging my head against the wall.
It just seems so simple - why won't this button launch this form?” Alright.
So we click the button and what we have is we have that button assigned to a macro called “SourceForm” and all that does is it does one thing and we have a userform and it does “.Show” on that user form and that generally should work but it is not working today.
I mean how simple can this be?
It's one line of code.
The name of the Userform.Show -- which is the right way to show a form and what's the error message we get?
-- “Expected function or variable” -- what the heck does that mean?
That doesn't tell me anything!
This error should say, “Hey, we're really sorry, but you can't have the macro name and the userform name match.” So, see here - here's the form in the properties.
Instead of userform one, Tony renames it to be SourceForm, which is a great thing to do but then that macro has to be called something else.
It can't be called SourceForm.
So, if I just say “ShowSourceForm” or “MySourceForm” our “GoSourceForm” or even “Bob” or “Tony” -- whatever -- then it will work perfectly.
Now I just changed the name of that macro so we have to come back here, right click, assign macro and assign to ShowSourceForm, click OK.
Alright, that simple change right there.
What a strange problem that Tony ran into there.
It was just coincidental that he happened to have the macro name and the userform name have the exact same thing.
So, there you go.
We'll have to put that error message in the keywords in case anyone else has that, they can see the answer quickly.
Well, hey, I want to thank Tony for sending that question in.
I want to thank you for stopping by.
For more on macros, check out my book - VBA and Macros.
Learn Excel from MrExcel podcast - Userform Won't Launch Hey! Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question is sent in by Tony.
Tony is coming up this VBA learning curve.
He’s doing great things with his reports but he sent me one - he said “I'm banging my head against the wall.
It just seems so simple - why won't this button launch this form?” Alright.
So we click the button and what we have is we have that button assigned to a macro called “SourceForm” and all that does is it does one thing and we have a userform and it does “.Show” on that user form and that generally should work but it is not working today.
I mean how simple can this be?
It's one line of code.
The name of the Userform.Show -- which is the right way to show a form and what's the error message we get?
-- “Expected function or variable” -- what the heck does that mean?
That doesn't tell me anything!
This error should say, “Hey, we're really sorry, but you can't have the macro name and the userform name match.” So, see here - here's the form in the properties.
Instead of userform one, Tony renames it to be SourceForm, which is a great thing to do but then that macro has to be called something else.
It can't be called SourceForm.
So, if I just say “ShowSourceForm” or “MySourceForm” our “GoSourceForm” or even “Bob” or “Tony” -- whatever -- then it will work perfectly.
Now I just changed the name of that macro so we have to come back here, right click, assign macro and assign to ShowSourceForm, click OK.
Alright, that simple change right there.
What a strange problem that Tony ran into there.
It was just coincidental that he happened to have the macro name and the userform name have the exact same thing.
So, there you go.
We'll have to put that error message in the keywords in case anyone else has that, they can see the answer quickly.
Well, hey, I want to thank Tony for sending that question in.
I want to thank you for stopping by.
For more on macros, check out my book - VBA and Macros.
