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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. A.Q

    Macro and FileDialog

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

    Excel FileDialog object and Visual Basic 6.

    droopy928gt, May 19, 2006, in forum: Excel Programming
    Replies:
    0
    Views:
    101
    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:
    175
    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:
    158
    mudraker
    Jun 24, 2006
  5. divya
    Replies:
    2
    Views:
    119
    divya
    Jul 8, 2006
  6. JFK

    FileDialog Object

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

    Application.FileDialog(msoFileDialogSaveAs)

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