Solved: select folder bij list

[coxonitus]

hi everybody.

i will try to explain mij problem.
I'm working with 1 excel sheet... where buttons link me through to folders.
the sheet is used by several users, each with there one workspace.
if 1 users clicks a button, they have fill in a password, when password correct, there needs to come a list of folders where they can select the folder they need to work in.
In each folder there is an excel workfile.

Can somebody help me out, or is this not possible??

This is the code I'm using. it works fine but when the file list opens a can open underlying folders which is not what i want.

[VBA] PW = InputBox("Geef het Paswoord aub !", , "*************")
If PW = "" Then Exit Sub
If PW <> "jackson" Then
MsgBox "Verkeerd Wachtwoord."
Exit Sub
End If
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim Filename As Variant
' File filters
Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
' Default filter to *.*
FilterIndex = 3
' Set Dialog Caption
Title = "Select File(s) to Open"
' Select Start Drive & Path
ChDrive ("C")
ChDir ("C:\Documents and Settings\jschulpen\Bureaublad\projectberstanden\Office")
With Application
' Set File Name Array to selected Files (allow multiple)
Filename = .GetOpenFilename(Fiter, FilterIndex, Title, , True)
' Reset Start Drive/Path
ChDrive (Left(.DefaultFilePath, 0))
ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Not IsArray(Filename) Then
MsgBox "No file was selected."
Exit Sub
End If
' Open FilesFor
i = LBound(Filename)
msg = msg & Filename(i) & vbCrLf
' This can be removed
Workbooks.Open Filename(i)
MsgBox msg, vbInformation, "Files Opened" ' This can be removedEnd Sub

End Sub[/VBA]

[Bob Phillips]

Sorry, I am not really sure what you want to happen. There is a bug in your code because you are not using Option Explicit, but I have no idea whether that fixes your problem.

[Bob Phillips]

Just in case the code error is the problem, here is the fixed version

[vba]

PW = InputBox("Geef het Paswoord aub !", , "*************")
If PW = "" Then Exit Sub
If PW <> "jackson" Then
MsgBox "Verkeerd Wachtwoord."
Exit Sub
End If
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim Filename As Variant
' File filters
Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
' Default filter to *.*
FilterIndex = 3
' Set Dialog Caption
Title = "Select File(s) to Open"
' Select Start Drive & Path
ChDrive ("C")
ChDir "C:\test" '("C:\Documents and Settings\jschulpen\Bureaublad\projectberstanden\Office")
With Application
' Set File Name Array to selected Files (allow multiple)
Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
' Reset Start Drive/Path
ChDrive (Left(.DefaultFilePath, 0))
ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Not IsArray(Filename) Then
MsgBox "No file was selected."
Exit Sub
End If
' Open FilesFor
i = LBound(Filename)
msg = msg & Filename(i) & vbCrLf
' This can be removed
Workbooks.Open Filename(i)
MsgBox msg, vbInformation, "Files Opened" ' This can be removed[/vba]

[coxonitus]

XLSM file "press Button"after correct PW thenfolder 1folder 2 folder 3"open folder list "sub folder(s)Sub folder(s)Sub Folder(s)"choose Folder"xls file xls file xls file"choose list to work in"if users clicks button he is restricted to only choose options in this Folder ( for example see Yellow)all the other folders are "off Limit" for that person.

[Bob Phillips]

Sorry mate, I am not trying to be awkward, but I really didn't understand your follow-up at all. Post it in Dutch and let me see if Google translate makes it clear for me, or even do that yourself.

[coxonitus]

sorry,
i tried to upload an excel lay out.

Als ik deze code gebruikt zoals door jullie gepost krijg ik alle mappen die ik zoek te zien in het windows scherm.
Wat ik zoek is,
- klik button
- pop up scherm met alleen de windows mappen voor de betreffende gebruiker
-kies map
- dan open excel file.

ik zal een tekening maken en deze posten.
ik hoop dat je hier iets aan hebt

[Bob Phillips]

This is what Google translate came up with,

If I use this code as posted by you I get all the folders I'm looking to see in the windows screen.
What I seek is
- click button
- pop up window with just the windows folder for the user
-choose map
- then open excel file.

I will draw pictures and posts.
I hope you have something to

Isn't that just great.

So, are you saying you want to restrict to just the folders that the initial drive selection shows, and not let them navigate up a level or whatever. If that is so, I think you will be out of luck.

[coxonitus]

Yes, XLD,

this is what i mean, so securing the button with a pW has no purpose at all.
But is there no bypass, like MSO dialog, with restrictions, or the Shell application,
I'm not good at these things, so i lay my faith in you guys.

[GTO]

Greetings coxonitus,

reference:

hi,
i'm looking through your posts and maybe one of you guys could help me out.

i wil try to explain mij problem.
i have a dashboard, when i click a button i have to give a password.
When password correct it has to open some sort of pop-up which gives me only the folders i want to choose, when i click a folder it opens the excel file.

in my case, when password correct it opens the pop-up in windows. that means it give's me the searcher of "C": drive. All i want is a local searcher or else the password i gave has no function.

someone??

...which you posted in: http://www.vbaexpress.com/forum/newr...reply&p=241652

I have to take off pretty quick, but I was wondering exactly what you meant in the bolded part.

For instance, let's say that the "pop up" initially started at (just by example) C:\MyData, and in C:\MyData\ there were three folders named: 'Folder 1', Folder 2' and 'Folder 3'. If I clicked on 'Folder 3', it won't open a workbook, it would open the folder. Does that make sense?

So what would we want to happen when one of the three folders is 'opened' or clicked?

[coxonitus]

the users have only acces to the folder they have to work in, that's why i handed a password.

when the list of folders( for users only) opens, they can choose.
(sub Folder1) (sub Folder2)
if chosen sub folder 1 they open the excel file within to insert there work or whatever.
that's why i handed a password registration, or else it has no function, i have to direct the user to there own directory.

i'm sorry if the question is rather difficult but i have an example in Dutch if you want.

[GTO]

Hi again,

Bob (XLD) is tons smarter than yours truly, but I wanted to see if its clarity that might be missing. I think attaching the workbook is a good idea, as maybe we can see better what you are trying to do.

That said, you have the vbproject password protected

You could list the password if not one you really use, or edit the last post and reload the attachment with out the password.

Mark

[coxonitus]

u name i serve

thnx so far.

[Bob Phillips]

This is my take on the situation.

If you want to direct them to a folder, directory, and then only allow them to pick from that folder or any sub-folders, you cannot use ANY of the file dialogs, as they will allow you navigate anywhere. The password in this sense is an irrelevance, as it controls what happens in your Excel code, but the file dialog is an system function.

If this is a correc t reading of the situation, I think you would have to build your own file dialog, maybe a treeview of all allowable files. This is maybe not easy for yourself with your current experience, but is not that hard for someone here who is more advanced with VBA.

Is this a correct assessment of what you want?

[coxonitus]

XLD,

you are right, my experience is based on VBA forums. Sorry about that, but i think i'm learning fast.
to become a pro i thin it will take lightyears....

so if i'm not mistaken you meant it's fixable.

Could you help me out??

[Bob Phillips]

I will see if I can knock something up over the next couple of days, so be patient. Hopefully someone else will jump in if they can deliver faster.

[BrianMH]

[vba]Sub StopOtherDir()
Dim user As String
Dim allowedpath As String
Dim Path
Dim fd
user = Environ("username")

Select Case user
Case "Brian"
allowedpath = "C:\Users\Brian\Documents\"
Case "Rob"
allowedpath = "C:\Users\Brian\Documents\"
Case Else
MsgBox "please contact file creator to be added to the user list"
Exit Sub
End Select

Set fd = Application.FileDialog(msoFileDialogFilePicker)


Do
fd.InitialFileName = allowedpath
If fd.Show = -1 Then
Path = fd.SelectedItems(1)
Else
Path = "cancelled"
End If
Loop Until Path Like allowedpath & "*" Or Path = "cancelled"
End Sub

[/vba]
This seems easier. If the path is outside of the folder or its subfolders it loops back and directs the user back to their folder. It uses Environ to capture their username but you could change that bit to a password and change the select statement as appropriate.

[coxonitus]

Brian,

It doesn't work.
It gives me immediatly the MsgBox "please contact file creator to be added to the user list

could you help me out here, for a new test

[BrianMH]

You need to edit the case statement to show the appropriate usernames returned by Environ and edit the paths.

[coxonitus]

Brian,

it works, ok.
But please look at the replies from XLD.
The problem i have is that the users have to ,only get acces to their own directory. That's not working now, because it browses througout "c".
I want to create a directory list, that gives me only those options i give them.

thnx anyway

[Bob Phillips]

I didn't try the treeview option as Brian came up with an interesting idea, and I thought that might suffice. Would you like me try and knock that up?