VBA SHBrowseForFolder Escape key/double click behaviour

[kunibert]

Hello,

I'm relatively new to using VBA to generate and save files, but these forums have proved such good help that I've only had to sign up for an account now, as I've found no solution to my problem. Please forgive me for any impolitic behaviour, with respect to posting rules in this forum.

I am running 32-bit Excel 2007 with VBA 6.5 on work Windows 7 machines and 64-bit Excel 2013 with VBA 7.1 on my home Windows 8 machine. I have pieced together a folder-browsing Function that selects, by default, the folder location that has already been stored. I have set this up so that it works on both machines with IF WIN64 blocks and the function works properly in both circumstances, for the most part.

My issues are that (1) pressing Esc yields the "Code execution has been interrupted" error message, and (2) double clicking on a folder that has no sub-folders is not recognized as selecting it. This is the same in both versions.

The line of code that is highlighted after the error message is always the line after the call to the SHBrowseForFolder function (see below). I have tried inputing return-value checks and error handling to no avail. Is this a limitation of the shell32 function SHBrowseForFolder function, or does anyone know of any way around this?

Any advice is much appreciated.
Kurt

Declarations:

Declare Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" (lpbi As BrowseInfo) As Long

Private Type BrowseInfo
    hwndOwner As Long
    pIDLRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfnCallback As Long
    lParam As Long
    iImage As Long
End Type

...

Function:

Dim lpIDList As Long
Dim tBrowseInfo As BrowseInfo

...

lpIDList = SHBrowseForFolder(tBrowseInfo)

If (lpIDList) Then
...

 

[sijpie]

On my (32bit) system it works fine ofr anytype of folder and whether I press Esc, cancel or X.

But why are you using such an obscure method that starts you off with an alien folder picker and right at the MyComputer level?

Have a look at these methods. Especially the last one mentioned on Pearson's website, which is also used with some variation on the other two.

Browse For Folder
Choose Destination Folder With Vba
http://www.mrexcel.com/forum/excel-questions/294728-browse-folder-visual-basic-applications.html

 

[kunibert]

Thanks, Sijpie,

I've seen the first link before and it's actually what I based a larger part of my module off of, but I had to incorporate parts of other code to allow for use of an initial directory. When I have time I'll look over all three and figure out exactly what is going on (I only have a loose understanding at this point, brought about by researching the errors that I'd get).

The reason why the first was more apt for my situation is because one button was to establish a directory to save in before I actually ran some procedures so that a destination folder could be established beforehand to store a day's generated data, ergo the file dialogue wasn't appropriate. In my struggles I've noted something rather strange: the complete folder-navigation module that I created would would perfectly (rather, Esc wouldn't cause the "Code execution has been interrupted" error) IF it was in a clean workbook and I ran the BrowseDirectory function from a test sub, however it would cause the error when inserted into the larger project. I tested again in the clean workbook, this time executing the Test Sub from a Form Control button put right on a worksheet and it also did not cause the error. In my larger project I actually use an ActiveX command button, and when one of these was added to the same worksheet and its code called that same test sub I would get the error.

Perhaps this is because the execution of the sub with the Active X control is nested within another sub, and somehow VBA is reading the Esc command as aborting the Active X control, I'm not sure. At any rate, I have found a workaround that seems to have disabled this glitch: I've inserted Application.EnableCancelKey = xlDisabled at the beginning of my BrowseDirectory function and all seems to be well.

Thank you so much for you attention and help.

Cheers,
Kurt

 

[sijpie]

OK. But non of these use a file dialog, it looks perhaps like it because it is a standard Windows browse dialog, but these are folder pickers.