How to select or open file folder and then choose a file using forms.

[dariusd77]

So lets say i want to have a spin button that scrolls through each month of the year(or drop down button). And each month is the name of each folder.. how do I do that.. How do i select at folder level not at the file level in the folder ..Because after they select folder then i want a form to come up that the user can then use to select the file. Each month will have the same number of files with the same name.
Example
folder january
file 1
file 2
folder February
file 1
file 2
folder March
file 1
file 2
each month ..........

So basically they will first choose the month they want using a spin button or drop down box..which = a particular folder

ie. C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Jan
ie C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Feb

once they selected the folder(month).another form(using command buttons or option buttons) will show up giving them 5 choices..which are 5 files.. within each folder, and each file is named exactly the same in each folder.

ie C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Jan\2013 Open Jobs
ie C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Jan\2013 Open orders
ie C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Feb\2013 Open Jobs
ie C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\Feb\2013 Open orders

 

[ChrisM]

No, it's not case sensitive. You did use backslashes right? in your quote of my text above it shows up as "|".

Easiest way to troubleshoot at this point is to use the Immediate window. If it's not visible in VBA then go to the View menu and enable it.

Add this line to your code, right before attempting to open the workbook:

Debug.Print strFileName

Once the code ends and/or the error occurs, look down in the immediate window and review the file path there. Look very closely for extra spaces, especially at the very end of the line.

Next, type this into a new line in the immediate window, then push the enter key to execute:

Workbooks.Open ("C:\Users\ddempsey\Desktop\projects\dashboard project\MSO dashboard\New folder\July\FSO Open Report.xls")

Does the file open?

 

[dariusd77]

I rewsolved the problem. It works now. It apeears i cannot be in the folder when running the code. Once I got out of the folder the file exist in and the ran the Code it worked fine and open up the file. Now I have to figure out how to reopen the form from the workbook, because i have it set up to close or hide the form when the workbook/file is open . Now I am tryin gto reopen the from by clicking on a button that will be at the top of the worksheet.

 

[ChrisM]

Just add this to your button click event:

frmYourFormName.Show

 

[dariusd77]

Just add this to your button click event:

frmYourFormName.Show

Ok should I be using the controlform button, or an activex button

 

[ChrisM]

Doesn't matter, whichever you prefer. Form buttons are probably simpler and less prone to issues.

 

[dariusd77]

Just add this to your button click event:

frmYourFormName.Show

I wrote this code but I get an run-time error '424'

Private Sub CommandButton2_Click()
frmuserFomrExample.Show
End Sub

 

[ChrisM]

Are you sure you spelled that right? Did you mean frmuserFormExample? You typed Fomr

 

[dariusd77]

Are you sure you spelled that right? Did you mean frmuserFormExample? You typed Fomr

Yes I meant from

 

[dariusd77]

Yes I meant from

OK no idea how the word from keeps coming up, I meant form.

 

[ChrisM]

There's not much that can go wrong with that line of code other than a misspelled name. Try this: close and reopen the file. Go to the immediate window in VBA and type that line, then push enter, see if it loads.

frmuserFormExample.Show