One final question

[legepe]

I would just like to thank everyone that responded to my questions ove
the past few days, and putting up with my ignorance!
The program that I was working on is finally finished, except for on
thing-
When i activate the macro below, it will save all the worksheets, an
then display the save-as window as it is suppose to. If i cancel it a
this point and don't name it, it will close the program and save i
with its base name in a value format, which means i loose all th
formulas in the program.
Is there a way to safe guide the base program and this from happening?
Secondly, I would like to learn more about Visual Basics, could anyon
advise me on what would be a good way to learn the basics
legepe


Sub special()
Dim wsheet As Worksheet
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Dialogs(xlDialogSaveAs).Show
For Each wsheet In Worksheets
wsheet.Unprotect
With wsheet.UsedRange
.Copy
.PasteSpecial xlPasteValues
End With
wsheet.Protect
Next wsheet
.ScreenUpdating = True
.DisplayAlerts = True
End With
ActiveWorkbook.Close True
End Su

 

[Myrna Larson]

From Help (I searched for Dialogs, got the Dialogs collection, then clicked on
the link to the Dialog object)

"Using the Dialog Object
Use Dialogs(index), where index is a built-in constant identifying the dialog
box, to return a single Dialog object. The following example runs the built-in
Open dialog box (File menu). The Show method returns True if Microsoft Excel
successfully opens a file; it returns False if the user cancels the dialog
box.

dlgAnswer = Application.Dialogs(xlDialogOpen).Show
"

Applying that to your situation (and adding the missing dots inside the
With/End With blocks):

Sub Special()
Dim wsheet As Worksheet
Dim Rsp As Boolean

With Application
.ScreenUpdating = False
.DisplayAlerts = False
Rsp = .Dialogs(xlDialogSaveAs).Show
End With

If Rsp = True Then
For Each wsheet In Worksheets
wsheet.Unprotect
With wsheet.UsedRange
.Copy
.PasteSpecial xlPasteValues
End With
wsheet.Protect
Next wsheet
ActiveWorkbook.Close True
End If

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

You might also want to look at the GetOpenFileName method. It also returns a
value that you can check to determine whether the user cancelled the dialog
box.