Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One final question
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 -- legep ----------------------------------------------------------------------- legepe's Profile: http://www.excelforum.com/member.php...fo&userid=1548 View this thread: http://www.excelforum.com/showthread.php?threadid=27255 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One final question
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. On Tue, 26 Oct 2004 13:47:49 -0500, legepe wrote: I would just like to thank everyone that responded to my questions over the past few days, and putting up with my ignorance! The program that I was working on is finally finished, except for one thing- When i activate the macro below, it will save all the worksheets, and then display the save-as window as it is suppose to. If i cancel it at this point and don't name it, it will close the program and save it with its base name in a value format, which means i loose all the 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 anyone 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 Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Final attempt save as binary question | Excel Discussion (Misc queries) | |||
Final day of month | Excel Worksheet Functions | |||
My Final #DIV/0! that I'd like to say Goodbye to! | Excel Worksheet Functions | |||
some final help | Excel Programming | |||
Help Finding the Final Row | Excel Programming |