Change code to an input box to enter the file name

**nathandavies9** · 03-30-2017, 05:19 AM

Hi all,

I have some code which i have tested and is working great, but i would like to change it slightly so that i can input a file name so it picks the correct .xls workbook.

in my code at the minute it is looking for "sample_BOM" i would like to be able to enter the file name manual every time, i have commented the code so you can see where it is.

Please Login or Register  to view this content.

I would also like to only open the userform when a button is pressed in the excel worksheet and not automatically when the workbook is opened. I have this macro attached to a button which opens the user form once the BOM is open.

Please Login or Register  to view this content.

**sintek** · 03-30-2017, 07:15 AM

To have the userform not open when worksheet is activated, remove this line

Please Login or Register  to view this content.

from

Please Login or Register  to view this content.

Then put this in your button on the worksheet

Please Login or Register  to view this content.

i.e

Please Login or Register  to view this content.

**bakerman2** · 03-30-2017, 07:42 AM

Something like this.

Please Login or Register  to view this content.

**nathandavies9** · 03-30-2017, 08:17 AM

Thank you both of you!!

this has worked perfectly!

Would it be possible to add an error message to the code so if the file is not open it tells you rather than creating an error to debug?

"Bill of Materials Not Open Please Open First"

**nathandavies9** · 03-30-2017, 08:31 AM

I have two separate codes and i'm not sure which bit i would put the code in,

Please Login or Register  to view this content.

then the macro to open the user form.

Please Login or Register  to view this content.

**bakerman2** · 03-30-2017, 10:02 AM

This opens it for you if still closed.
Replace "Filename" with wBook.

Please Login or Register  to view this content.

**nathandavies9** · 03-30-2017, 10:09 AM

bakerman2,

Where should i put this code? Will it still work if my root folder is different every time?

**nathandavies9** · 03-30-2017, 10:13 AM

I still get an error on the macro, i'm not sure if i have put it in the correct place.

Please Login or Register  to view this content.

**nathandavies9** · 03-30-2017, 10:20 AM

This is what i tried in the macro, but again it came up with an error.

Please Login or Register  to view this content.

**bakerman2** · 03-30-2017, 08:08 PM

Put the function in a standard module.
In your userform module this.
Also if your rootfolder is always changing you would have to substitute the InputBox to a FilePicker dialogue box.

Please Login or Register  to view this content.

**nathandavies9** · 03-31-2017, 03:38 AM

Hi Bakerman2,

I have put the function in a new module, and then inserted the code above, but i get an error saying \\NEWBENSON\Company\IMC Issue Sheets\wBook can not be found, it doesnt seem to be searching for the .xls file.

any suggestions?

**nathandavies9** · 03-31-2017, 03:43 AM

I have decide that the file location will be in the same place every time so no need to worry about that any more

**bakerman2** · 03-31-2017, 03:58 AM

I just copied your codelines so it didn't occur to me immediatly but wBook needs to be without the double quotes.

Please Login or Register  to view this content.

**nathandavies9** · 03-31-2017, 03:59 AM

I'm not sure what that means, very new to vba code sorry!

**bakerman2** · 03-31-2017, 04:07 AM

Just replace the same line in the code with the line in my previous post.

**nathandavies9** · 03-31-2017, 04:08 AM

I have managed to work out how to get the .xls open and that now works perfectly!

I have one other little problem now which i was unaware off til just now, Would it be possible to add another input box to get the worksheet name?

Please Login or Register  to view this content.

I have just tried to create the code myself but doesnt work thing i have something wrong!

**bakerman2** · 03-31-2017, 05:33 AM

Same as wBook, use wSheet without the double quotes.

**nathandavies9** · 03-31-2017, 05:38 AM

I thought i was close! just tried it and it worked perfectly!

Thanks for your help through all this!

REP ADDED

Cheers
ND

**bakerman2** · 03-31-2017, 05:47 AM

You're very welcome and thanks for rep+.

If this answers your question please mark the thread as Solved.

Change code to an input box to enter the file name

LinkBack

Thread Tools

Rate This Thread

Display

Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Re: Change code to an input box to enter the file name

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VB Code to enter cell value when file is open

Change VBA code using input box

Formula to enter data/change the colour of a cell if a file isn't updated

vba code to open excel file, automatically input password, run a macro and close the file

[SOLVED] VBA Code to Enter File Name, Activate the File, and Run Macro on File

Doing away with an Input box - how to change this code?

How to enter automatic entry of file name in to VBA Code?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions