CommonDialog Error

[Syed Zeeshan Haider]

Hello Experts,
I have Excel 97 Pro on Win98SE.

I am trying to display CommonDialog box using following code:

Dim cdbForLST As CommonDialog
Set cdbForLST = New CommonDialog
Dim ffNo As Integer
ffNo = FreeFile()
With cdbForLST
.Filter = "GetRight URL Lists (*.lst)|*.lst|All Files (*.*)|*.*"
.FilterIndex = 1
.InitDir = "C:\My Documents"
.DialogTitle = "Save as *.lst"
.ShowSave ' execution never goes beyond this point
If .FileName <> "" Then
MsgBox "OK"
' this line is for checking if execution
' reaches this point or not.
End If
End With

This code stops executing at ".ShowSave" (see comments in code) and when I
click "OK" in CommonDialog box, following error occurs:
=================================
Run-time error '20476':

Method 'ShowSave' of object 'ICommonDialog' failed
=================================
I have VB6 EE too in my computer. I have searched the help files of both,
VBA for Excel and VB6 EE, but haven't got any clue about the meaning of this
error. I have been using CommonDialog box in VB6 very successfully with same
type of code. But in VBA for Excel it is causing above mentioned error.

What is the meaning of this error?

Is newer version of CommonDialog box not compatible with VBA for Excel 97?

Thank you,

 

[Harald Staff]

Hi Syed

You should not use an external component for this. Try instead:

Sub test()
Dim F As Variant
ChDrive ("C:\Temp")
ChDir ("C:\Temp")
F = Application.GetSaveAsFilename( _
InitialFilename:=Format(Date, "yyyymmdd") & ".lst", _
filefilter:="GetRight URL Lists (*.lst), *.lst, " & _
"All Files (*.*), *.*", _
filterindex:=1, _
Title:="Pick a cool location")
If F = False Then
MsgBox "You cancelled"
Else
MsgBox "let us save it as " & _
CStr(F)
End If
End Sub

 

[Syed Zeeshan Haider]

Thank you very much, Harald!

It worked very well.
I love simple examples like you posted. They make the complex tasks easier.

But I am still wondering why doesn't CommonDialog box work with VBA for
Excel 97 Pro?

Thanks again!
--
Syed Zeeshan Haider.
http://szh.20m.com/


-----------------------------------
Allah says to Mankind:
"Then which of the favours of your Lord will ye deny?"



in message news:[email protected]...

 

[Harald Staff]

Hi again Syed

That is a mystery. It should in your case, since you are licensed to use it in both
runtime and designtime mode. But there are bugs in'97, and there are potential version
conflicts in all instances of external components -my very best tools recently crashed
during some "service pack". Anyway, it would probably not work on another computer or with
another user with fewer licenses and priviledges.

... a very long "I have no idea" reply