• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

closing filedialogue box

Ronaldo

New Member
I am using the code below to open up a file dialogue box, to allow user's to locate and select a single file. The VBA code then goes on top perform a fair amout of processing. My issue is that the dialogue box remains on screen. I want to close it immediately. Any idea's


Dim fd As FileDialog

Dim ffs As FileDialogFilters

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd

Set ffs = .Filters 'clear filters

With ffs

.Clear

.Add "Files", "*.*"

End With

.AllowMultiSelect = False

If .Show = False Then Exit Sub

Infile = .SelectedItems(1)

End With
 

Hui

Excel Ninja
Staff member
Ronaldo

When I run your code in XL 2007 the dialogue closes correctly and returns the file name correctly
 

Ronaldo

New Member
Hello Hui,

It does everything OK except close (immediately). After the user selects a file, the code goes on to do a lot of laborious time-consuming processing, during which the selection box stays plumb in the middle of the screen. What I want to do is to display some processing progress counters, but they don't display until its finished (and the filedialog screen disappears)
 

Ronaldo

New Member
the problem doesn't seem to be with the filedialog box. I by-passed this by hardcoding the input filename to be used. Still, the field in the form that I update each loop (reacord read) of the code, fails to update until it finishes. I thought I could get the form field to scroll through numbers from 1 upwards ??
 

Hui

Excel Ninja
Staff member
Ronaldo


I tried the following in 2003, 2007 & 2010 and the dialogue has gone in all versions by the time the Msgbox executes ?


Sub h()


Dim fd As FileDialog

Dim ffs As FileDialogFilters

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd

Set ffs = .Filters 'clear filters

With ffs

.Clear

.Add "Files", "*.*"

End With

.AllowMultiSelect = False

If .Show = False Then Exit Sub

Infile = .SelectedItems(1)

End With


MsgBox Infile


End Sub
 

Ronaldo

New Member
Ah. Now that makes a difference in that it also serves to update the field I have on my form. However, I don't want a msgbox popping up all the while.


Please allow me to re-state the problem knowing what I now know. I don't believe the filedialog box has anything to do with it.


I have a Form that has a button to invoke a procedure that reads and processes records from an external tab delimited file. Also on the form, I have a "text box" control with the name "RecsRead".


When the procedure is executed, and the records are read from the file (in a loop) when each record is read I have the statement . . .


RecsRead.Value = iRec


where iRec is the loop counter. The text box (on the form) does not update until the process has completely finished - rather than after each record is read - which is what I want ??


It's as though VBA is so intent on running the code, it can't be bothered to update the form ?


Any help on this gratefully received
 

Hui

Excel Ninja
Staff member
to update the text box (on the form) put a line after RecsRead.Value = iRec


RecsRead.Value = iRec

Textbox.Value = iRec 'change the textbox name to match the text boxes name


You may also want to check that the original code you have shown is not inside any loops
 

Ronaldo

New Member
Sorry Hui, the text box name is "RecsRead" ?


The code pasted is inside a loop. If I put a msgbox (as you suggested earlier) it updates fine. Just doesn't do it otherwise.
 

Hui

Excel Ninja
Staff member
Ronaldo

I wasn't suggesting that you put a Msgbox line in.

My code shows that your code is working correctly and not the cause of the dialogue staying on screen

If you are only loading 1 file, you probably don't need the entire file loading section in a loop. It looks like what ever you are doing in the loop is not exiting the loop properly but returning back to reload a file
 

Ronaldo

New Member
No. I know you wasn't, but it did help as it suggested that the processor was in fact too busy to post my variable to the form. I have it sorted now by inserting "DoEvents" as below


RecsRead.Value = iRec

DoEvents


many thanks for your help
 
Top