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

Discussion in 'Excel Programming' started by Horus, Apr 8, 2014.

  1. Horus

    Horus Guest

    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 th
    application.filedialog object to get the path to a folder from th
    user.

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

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

    I could post some code, but thought that a quick fix would be if it wa
    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 bee
    able to find any one else describing this issue on this or othe
    forums.

    Any help would be greatly appreciated


    --
    Horus
     
    Horus, Apr 8, 2014
    #1
    1. Advertisements

  2. Horus

    GS Guest

    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
     
    GS, Apr 9, 2014
    #2
    1. Advertisements

  3. Horus

    Horus Guest

    '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


    --
    Horus
     
    Horus, Apr 9, 2014
    #3
  4. Horus

    GS Guest

    > '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
     
    GS, Apr 9, 2014
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. A.Q

    Macro and FileDialog

    A.Q, Dec 22, 2005, in forum: Excel Programming
    Replies:
    0
    Views:
    88
  2. droopy928gt

    Excel FileDialog object and Visual Basic 6.

    droopy928gt, May 19, 2006, in forum: Excel Programming
    Replies:
    0
    Views:
    99
    droopy928gt
    May 19, 2006
  3. H.A. de Wilde

    Application.FileDialog(msoFileDialogOpen)

    H.A. de Wilde, Jun 3, 2006, in forum: Excel Programming
    Replies:
    1
    Views:
    170
    Ron de Bruin
    Jun 4, 2006
  4. H.A. de Wilde

    Application.FileDialog(msoFileDialogOpen) and error 75

    H.A. de Wilde, Jun 14, 2006, in forum: Excel Programming
    Replies:
    3
    Views:
    150
    mudraker
    Jun 24, 2006
  5. divya
    Replies:
    2
    Views:
    116
    divya
    Jul 8, 2006
  6. JFK

    FileDialog Object

    JFK, Mar 20, 2007, in forum: Excel Programming
    Replies:
    3
    Views:
    110
    NickHK
    Mar 22, 2007
  7. greg

    Application.FileDialog(msoFileDialogSaveAs)

    greg, Feb 18, 2008, in forum: Excel Programming
    Replies:
    3
    Views:
    940
  8. geoff_ness
    Replies:
    5
    Views:
    310
    geoff_ness
    Oct 28, 2009
Loading...