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

 

[dariusd77]

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

Not sure what you mean be immediate window..

 

[dariusd77]

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

I get the following error when i put this code into the immediate window.

Compile error: Invalid in Immediate pane.

private Sub btnOpenDatasheetMenu_Click()


VBAProject.frmUserFormExample.Hide
frmUsrDataSheet.Show


End Sub

 

[dariusd77]

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

I also cannot seem to use the command control buttons, because they want you to assign a macro to them. where as the activex lets you open up an area to input code. Also one other thing that is strange I cannot delete the buttons off of the worksheet. When i right click on them nothing happens.

 

[ChrisM]

The immediate window is only used to run executable lines of code, "private sub xxxx" is not such a line. You only run one line at a time. You have pasted in 4 lines, I only wanted you to run one.

If you use a Form command button, you have to create a Sub to execute your desired code, then assign that sub to the button using the macro assigner as you mentioned:

Private Sub MySub
    frmuserFormExample.Show
End Sub

Then just assign "MySub" to the button.

If you can't delete your ActiveX button, you need to toggle in to Edit mode, it's on the Developer ribbon.

There's no need to add VBAProject. to this:

VBAProject.frmUserFormExample.Hide

You weren't calling it that before, why did you add that? Just call it "frmUserFormExample"

 

[dariusd77]

The immediate window is only used to run executable lines of code, "private sub xxxx" is not such a line. You only run one line at a time. You have pasted in 4 lines, I only wanted you to run one.

If you use a Form command button, you have to create a Sub to execute your desired code, then assign that sub to the button using the macro assigner as you mentioned:

Private Sub MySub
    frmuserFormExample.Show
End Sub

Then just assign "MySub" to the button.

If you can't delete your ActiveX button, you need to toggle in to Edit mode, it's on the Developer ribbon.

There's no need to add VBAProject. to this:

VBAProject.frmUserFormExample.Hide

You weren't calling it that before, why did you add that? Just call it "frmUserFormExample"

Yes i am only calling it frmuserFormExample..

Ok so I did what you suggested.. now get error 424. Object required.

 

[ChrisM]

If that line fails, then VBA is not seeing your userform. Is the userform in the same workbook that the code is running in? Confirm the name one more time by selecting the form in VBA and viewing the Properties window. Look at the first field, (Name) and verify once again it is written the same, double check for leading/trailing spaces.

 

[dariusd77]

If that line fails, then VBA is not seeing your userform. Is the userform in the same workbook that the code is running in? Confirm the name one more time by selecting the form in VBA and viewing the Properties window. Look at the first field, (Name) and verify once again it is written the same, double check for leading/trailing spaces.

I used another solution that comes close, basically I put the following code in a module.

Public Sub ShowDataSheetForm()
frmUsrDataSheet.Show
End Sub

then I put this code into the butt*******event

Private Sub MySub()
   Application.Run "UserformExample\frmUserDataSheet.xlsm!ShowDataSheetForm"
End Sub

However I had to move the location of the workbook because there seems to be a default location that points to document folder. so i moved the workbook with the form in it to that location. then when i clicked on the button on the work sheet it worked. The only problem with this is that it means that is where it will have to be located at on the users computer which sucks.