Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop Up Form Based Workbook Close()
I want to create a user form based on Workbook_BeforeClose statement. If the
If statement below finds a "No" in the cell x2 it will open a "Report Does Not Reconcile" user form with two options based on closing the workbook. The "Yes" button will allow the user to go straight to a SaveAs option that will use the filename from cell a2. The "No" button will prevent closing the workbook and go to cell V105 (which I want to give a Name Range of "OutcomeTotal") to allow the numbers to be adjusted and reconciled. Currently I am using the following routine which prevents the workbook closing if it doesn't reconcile. Sometimes the user needs to be able to close without reconciling. Private Sub Workbook_BeforeClose(Cancel As Boolean) If Worksheets("DIOU Stats").Range("x2").Value = "no" Then Application.Goto Worksheets("DIOU Stats").Range("u105") Cancel = True End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop Up Form Based Workbook Close()
Possibly something like this:
Private Sub Workbook_BeforeClose(Cancel As Boolean) If Worksheets("DIOU Stats").Range("x2").Value = "no" Then ans = Msgbox("Report does not reconcile",vbyesNo) if ans = vbYes then ThisWorkbook.SaveAs FileName:= _ Worksheets("DIOU stats").Range("A1").value else Application.Goto Worksheets("DIOU Stats").Range("u105") Cancel = True end if End If End Sub -- Regards, Tom Ogilvy "TonyD" wrote: I want to create a user form based on Workbook_BeforeClose statement. If the If statement below finds a "No" in the cell x2 it will open a "Report Does Not Reconcile" user form with two options based on closing the workbook. The "Yes" button will allow the user to go straight to a SaveAs option that will use the filename from cell a2. The "No" button will prevent closing the workbook and go to cell V105 (which I want to give a Name Range of "OutcomeTotal") to allow the numbers to be adjusted and reconciled. Currently I am using the following routine which prevents the workbook closing if it doesn't reconcile. Sometimes the user needs to be able to close without reconciling. Private Sub Workbook_BeforeClose(Cancel As Boolean) If Worksheets("DIOU Stats").Range("x2").Value = "no" Then Application.Goto Worksheets("DIOU Stats").Range("u105") Cancel = True End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop Up Form Based Workbook Close()
Tom, the functionality of the routine works except if I say "yes" and then
pick "no" or "Cancel" when the message "A file named XXX already exists in this location. Do you want to replace it?". If I press either "No" or "Cancel", then I get a "run time error 1004" message and then if I click "End" it closes. Any way of avoiding the Run Time error message? "Tom Ogilvy" wrote: Possibly something like this: Private Sub Workbook_BeforeClose(Cancel As Boolean) If Worksheets("DIOU Stats").Range("x2").Value = "no" Then ans = Msgbox("Report does not reconcile",vbyesNo) if ans = vbYes then ThisWorkbook.SaveAs FileName:= _ Worksheets("DIOU stats").Range("A1").value else Application.Goto Worksheets("DIOU Stats").Range("u105") Cancel = True end if End If End Sub -- Regards, Tom Ogilvy "TonyD" wrote: I want to create a user form based on Workbook_BeforeClose statement. If the If statement below finds a "No" in the cell x2 it will open a "Report Does Not Reconcile" user form with two options based on closing the workbook. The "Yes" button will allow the user to go straight to a SaveAs option that will use the filename from cell a2. The "No" button will prevent closing the workbook and go to cell V105 (which I want to give a Name Range of "OutcomeTotal") to allow the numbers to be adjusted and reconciled. Currently I am using the following routine which prevents the workbook closing if it doesn't reconcile. Sometimes the user needs to be able to close without reconciling. Private Sub Workbook_BeforeClose(Cancel As Boolean) If Worksheets("DIOU Stats").Range("x2").Value = "no" Then Application.Goto Worksheets("DIOU Stats").Range("u105") Cancel = True End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop Up Form Based Workbook Close()
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Worksheets("DIOU Stats").Range("x2").Value = "no" Then ans = Msgbox("Report does not reconcile",vbyesNo) if ans = vbYes then Application.DisplayAlerts = False ThisWorkbook.SaveAs FileName:= _ Worksheets("DIOU stats").Range("A1").value Application.DisplayAlerts = True else Application.Goto Worksheets("DIOU Stats").Range("u105") Cancel = True end if End If End Sub Doesn't offer a choice. If you want a choice, then use Dir to see if such a file already exists and if it does offer your own choice which you design into and handle with your own code. If the user chooses to overwrite, you can then use the KILL command to remove the file before you write a new version (or use the displayalerts approach I have already shown). -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: Possibly something like this: Private Sub Workbook_BeforeClose(Cancel As Boolean) If Worksheets("DIOU Stats").Range("x2").Value = "no" Then ans = Msgbox("Report does not reconcile",vbyesNo) if ans = vbYes then ThisWorkbook.SaveAs FileName:= _ Worksheets("DIOU stats").Range("A1").value else Application.Goto Worksheets("DIOU Stats").Range("u105") Cancel = True end if End If End Sub -- Regards, Tom Ogilvy "TonyD" wrote: I want to create a user form based on Workbook_BeforeClose statement. If the If statement below finds a "No" in the cell x2 it will open a "Report Does Not Reconcile" user form with two options based on closing the workbook. The "Yes" button will allow the user to go straight to a SaveAs option that will use the filename from cell a2. The "No" button will prevent closing the workbook and go to cell V105 (which I want to give a Name Range of "OutcomeTotal") to allow the numbers to be adjusted and reconciled. Currently I am using the following routine which prevents the workbook closing if it doesn't reconcile. Sometimes the user needs to be able to close without reconciling. Private Sub Workbook_BeforeClose(Cancel As Boolean) If Worksheets("DIOU Stats").Range("x2").Value = "no" Then Application.Goto Worksheets("DIOU Stats").Range("u105") Cancel = True End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula (off form) that completes form data based on a result | Excel Worksheet Functions | |||
Close form automatically upon opening workbook | Excel Discussion (Misc queries) | |||
Close form... | Excel Programming | |||
close .chm with form | Excel Programming | |||
Close Form with VBA | Excel Programming |