Second run of macro hangs on .show of application.filedialog object!
 

Dear All!

I am new to this forum and quite new on programming Excel VBA as well, but here goes...

I am programming a module containing a Sub that employs the application.filedialog object to get the path to a folder from the user.

It works fine the first time I run it after starting Excel, but it hangs when I attempt it the second time. I use the Task Manager to close Excel and get the message that "The system cannot end this program because it is waiting for a response from you".

I think that I understand as much as that the filedialog object belongs to the Excel application as such and I guess it somehow persists between my macro runs. I also assume that somewhere in my macro I am doing something to change its state and cause it to malfunction the second time around.

I could post some code, but thought that a quick fix would be if it was somehow possible to reset or reload the filedialog object from scratch. I have not found such a possibility and to my surprise I have not been able to find any one else describing this issue on this or other forums.

Any help would be greatly appreciated!

Second run of macro hangs on .show of application.filedialog object!
 

I've always found this much more consistently reliable...

Function GetDirectory$(Optional OpenAt, Optional Msg$)
Dim SH As Object

If Msg = "" Then Msg = "Please choose a folder"
Set SH = CreateObject("Shell.Application").BrowseForFolder( 0, Msg,
&H40 Or &H10, OpenAt)

On Error Resume Next
GetDirectory = SH.Self.Path
On Error GoTo 0
Set SH = Nothing
End Function 'GetDirectory()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Works like a charm! How do I join your fan-list Garry?

Second run of macro hangs on .show of application.filedialog object!
 

'GS[_2_ Wrote:
;1616942']I've always found this much more consistently reliable...

Function GetDirectory$(Optional OpenAt, Optional Msg$)
Dim SH As Object

If Msg = "" Then Msg = "Please choose a folder"
Set SH = CreateObject("Shell.Application").BrowseForFolder( 0, Msg,
&H40 Or &H10, OpenAt)

On Error Resume Next
GetDirectory = SH.Self.Path
On Error GoTo 0
Set SH = Nothing
End Function 'GetDirectory()

--
Garry

Works like a charm! How do I join your fan-list Garry?

I don't know anything about a fan-list, but I do appreciate the
feedback!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion