Greg,
Ran Tom's macro on XL 97, 2000, 2002.
Used steps 1 3, 4 on all.
Also used step 2 on XL 97.
All of above on WindowsXP
My results agree with yours.
Failure is defined as the Msgbox showing "Nothing Selected"
Conclusions/opinions:
I think it's a "bug"
Change your code or scroll the sheet before calling the file open dialog.
MS won't fix it, but if they did issue a fix now, the problem
would still be there in the next release.
Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"Greg Wilson"
wrote in message
Jim, thanks for responding.
I've tested this on 3 computers:
1. xl2000 (9.0.2720 SP3)
2. xl2000 (9.0.6926 SP3)
3. xl2000 (N/A)
I am using Tom's test code:
Sub Test()
Dim arr As Variant
arr = Application.GetOpenFilename( _
"Excel files (*.xls), *.xls", _
MultiSelect:=True)
If Not IsArray(arr) Then
MsgBox "Nothing selected"
Else
MsgBox UBound(arr) - LBound(arr) + 1 _
& " items selected"
End If
End Sub
I have found the following:
1. If you run the code from the VBE it fails if you have a cell or cells
with conditional formats (cf's) that include a worksheet function (wf).
2. If there is no wf then it succeeds when called from the VBE.
3. If you scroll so that the cell(s) are no longer in view then it succeeds
when called from the VBE inclusive of wf's.
4. If you call it through ToolsMacroMacros then it succeeds under all
conditions.
5. If you have a helper cell that holds the desired wf (I used the Today()
function) and the cf references this cell instead then it succeeds. The
helper cell can be in the visible range. This would appear to be the solution
of choice.
6. If you activate another worksheet then it suceeds under all conditions.
7. On the first of the computers listed it fails when the filter is
extended to include *.doc. However, this was tested from within my project
(lots of code including event code and public vars).
8. In contrast, on the second computer listed it succeeds when the filter
is extended to include *.doc.
9. I get inconsistant results when in my project - i.e. it fails after a
while even if NOT run through the VBE. However, the macro is fired through a
right-click event and not through ToolsMacroMacros. The project is complex
and in the process of development and I'm still evaluating the situation.
Hoever, it does not appear to be caused by an simple error state. The problem
appears to be much more subtle.
Your post seems to imply that you don't corroborate the phenomenon. Is this
so?
Thanks again for responding.
Regards,
Greg
"Jim Cone" wrote:
Greg,
I wonder if the problem is restricted to a particular XL version?
What version are you using?
Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"Greg Wilson"
wrote in message
Simply scrolling so that the conditional formatted cells are off-screen also
works.
Greg