Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A save question:
Is there a way to attach a macro to the Excel save button
and have it work exclusively for an uniquely identified workbook, but operate normally otherwise. Or is the answer to put a save button (control) on each worksheet? Thanks, Phil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A save question:
Hi,
For that specific workbook, use its Before_save event in the thisworkbook module: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if msgbox("Save ?",vbYesNo)=vbNo then Msgbox "I'm not saving myself!!" Cancel=True Else msgbox "Saving!!" end if End Sub Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Is there a way to attach a macro to the Excel save button and have it work exclusively for an uniquely identified workbook, but operate normally otherwise. Or is the answer to put a save button (control) on each worksheet? Thanks, Phil . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A save question:
Jan Karel, Thanks for your answer. Could we take this a
step further... My workbook is named Scorecard, and its worksheets are entitled Scorecard, Customer, Finance, and Employee. When the user clicks the Save icon, I want the cursor in each worksheet to be placed in Cell A1, and the worksheet named Scorecard to be the the last worksheet changed. The object is that when a user opens the Workbook, the worksheet named Scorecard comes up first, and as each of the worksheets is opened, it shows the top of the worksheet - thus the force of cursor to cell A1. What would the code be, and just where would I put it? Phil -----Original Message----- Hi, For that specific workbook, use its Before_save event in the thisworkbook module: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if msgbox("Save ?",vbYesNo)=vbNo then Msgbox "I'm not saving myself!!" Cancel=True Else msgbox "Saving!!" end if End Sub Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Is there a way to attach a macro to the Excel save button and have it work exclusively for an uniquely identified workbook, but operate normally otherwise. Or is the answer to put a save button (control) on each worksheet? Thanks, Phil . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A save question:
Phil,
Here is a more convoluted macro that works. (it cycles through a large workbook quickly) Note that just selecting A1 does not guarantee cell A1 will be at the top of the screen - that is the reason for the ScrollRow & ScrollColumn lines. be fun... steve ============================================== Sub SaverWB() ' Save workbook - No Alerts ' Select A1 on each sheet ' Select Sheet(1) Application.ScreenUpdating = False Application.CutCopyMode = False '''Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next For x = 1 To ActiveWorkbook.Worksheets.COUNT Sheets(x).Select Range("a1").Select ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 1 Next On Error GoTo 0 If IsError(Sheets(1).Select) Then Sheets(2).Select Else Sheets(1).Select End If Cells(1, 1).Select Application.EnableEvents = True Application.ScreenUpdating = True Sheets("Scorecard").Select ActiveWorkbook.Close True Application.DisplayAlerts = True End Sub ========================================= "Phil Hageman" wrote in message ... Jan Karel, Thanks for your answer. Could we take this a step further... My workbook is named Scorecard, and its worksheets are entitled Scorecard, Customer, Finance, and Employee. When the user clicks the Save icon, I want the cursor in each worksheet to be placed in Cell A1, and the worksheet named Scorecard to be the the last worksheet changed. The object is that when a user opens the Workbook, the worksheet named Scorecard comes up first, and as each of the worksheets is opened, it shows the top of the worksheet - thus the force of cursor to cell A1. What would the code be, and just where would I put it? Phil -----Original Message----- Hi, For that specific workbook, use its Before_save event in the thisworkbook module: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if msgbox("Save ?",vbYesNo)=vbNo then Msgbox "I'm not saving myself!!" Cancel=True Else msgbox "Saving!!" end if End Sub Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Is there a way to attach a macro to the Excel save button and have it work exclusively for an uniquely identified workbook, but operate normally otherwise. Or is the answer to put a save button (control) on each worksheet? Thanks, Phil . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A save question:
Didn't realize that this thread preceded the "other" thread that I
mentioned in my last post. Please disregard my last post. John John Wilson wrote: Phil, Myself and Trevor helped you out with this in another thread. From what I can gather from this (and the other) thread, you want a particular scenario set up when a user first opens a workbook. The coding that Jan Karel will work fine (and it does the same thing as the code in that other thread) but the caveat to it is where you use it. When used in the Before_Save event, if a user is working on a workbook and decides to save it in the middle of what they were doing, you're going to rearrange the sheets on them. If you use this same code in the Workbook_Open Event (or an Auto_Open sub), whenever the workbook is opened, it'll be set up as you wish, but if a user saves it, it won't rearrange the sheets on them. It's all a matter of preference. Just my two cents...... John Phil Hageman wrote: Jan Karel, Thanks for your answer. Could we take this a step further... My workbook is named Scorecard, and its worksheets are entitled Scorecard, Customer, Finance, and Employee. When the user clicks the Save icon, I want the cursor in each worksheet to be placed in Cell A1, and the worksheet named Scorecard to be the the last worksheet changed. The object is that when a user opens the Workbook, the worksheet named Scorecard comes up first, and as each of the worksheets is opened, it shows the top of the worksheet - thus the force of cursor to cell A1. What would the code be, and just where would I put it? Phil -----Original Message----- Hi, For that specific workbook, use its Before_save event in the thisworkbook module: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if msgbox("Save ?",vbYesNo)=vbNo then Msgbox "I'm not saving myself!!" Cancel=True Else msgbox "Saving!!" end if End Sub Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Is there a way to attach a macro to the Excel save button and have it work exclusively for an uniquely identified workbook, but operate normally otherwise. Or is the answer to put a save button (control) on each worksheet? Thanks, Phil . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error and Save as question | Excel Discussion (Misc queries) | |||
Desktop Save Question | Excel Discussion (Misc queries) | |||
Another Save Question | Excel Worksheet Functions | |||
save as text question | Excel Discussion (Misc queries) | |||
Save Macro - yet another question | Excel Discussion (Misc queries) |