Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting user to answer a question before saving and closing wkbo
Hi,
I would like for the users of my excel workbook to provide a percent complete update before saving and closing the file. Is there a way to have a custom dialog box pop up when they attempt to save or close? Can this box ask them to select a percentage complete choice from either a drop down box or radio button selection? I would appreciate any assistance in pointing me in the right direction or getting me started on this idea I'm trying to implement. TIA, Tanya |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting user to answer a question before saving and closing wkbo
Build a form with a combobox and a button, set the button visible property to
false and place this code in the thisworkbook Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) UserForm1.Show End Sub and place this in the form code Private Sub ComboBox1_Change() If ComboBox1.Value < "" Then CommandButton1.Visible = True End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Activate() ComboBox1.AddItem "10" ComboBox1.AddItem "20" ComboBox1.AddItem "30" ComboBox1.AddItem "40" ComboBox1.AddItem "50" ComboBox1.AddItem "60" ComboBox1.AddItem "70" ComboBox1.AddItem "80" ComboBox1.AddItem "90" ComboBox1.AddItem "100" End Sub -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "JustBreathe" wrote: Hi, I would like for the users of my excel workbook to provide a percent complete update before saving and closing the file. Is there a way to have a custom dialog box pop up when they attempt to save or close? Can this box ask them to select a percentage complete choice from either a drop down box or radio button selection? I would appreciate any assistance in pointing me in the right direction or getting me started on this idea I'm trying to implement. TIA, Tanya |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting user to answer a question before saving and closing wkbo
You need to add a workbook close subroutine. the function should be put in
the Thisworkbook object. go to any tab on the bottom of the worksheet and right click. Select View Code. You will see the VBA Project window on the left. double click on ThisWorkbook and paste the code show below. After it works make changes as necessary. Private Sub App_WorkbookBeforeClose(ByVal Wb as Workbook, _ Cancel as Boolean) a = MsgBox("Do you really want to close the workbook?", _ vbYesNo) If a = vbNo Then Cancel = True End Sub "JustBreathe" wrote: Hi, I would like for the users of my excel workbook to provide a percent complete update before saving and closing the file. Is there a way to have a custom dialog box pop up when they attempt to save or close? Can this box ask them to select a percentage complete choice from either a drop down box or radio button selection? I would appreciate any assistance in pointing me in the right direction or getting me started on this idea I'm trying to implement. TIA, Tanya |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting user to answer a question before saving and closing
Joel: Just a point to watch out for it is better to use the before save event
as this is picked up on the close event if the user has made changes as saves. Tanya: You also need to deal with the processing the reply by putting the answer somewhere. Look at John suggestion and use an ok button and them use activeworkbook.worksheet("Sheet1").range("a1") = the result. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Joel" wrote: You need to add a workbook close subroutine. the function should be put in the Thisworkbook object. go to any tab on the bottom of the worksheet and right click. Select View Code. You will see the VBA Project window on the left. double click on ThisWorkbook and paste the code show below. After it works make changes as necessary. Private Sub App_WorkbookBeforeClose(ByVal Wb as Workbook, _ Cancel as Boolean) a = MsgBox("Do you really want to close the workbook?", _ vbYesNo) If a = vbNo Then Cancel = True End Sub "JustBreathe" wrote: Hi, I would like for the users of my excel workbook to provide a percent complete update before saving and closing the file. Is there a way to have a custom dialog box pop up when they attempt to save or close? Can this box ask them to select a percentage complete choice from either a drop down box or radio button selection? I would appreciate any assistance in pointing me in the right direction or getting me started on this idea I'm trying to implement. TIA, Tanya |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting user to answer a question before saving and closing
Thanks everyone for your expertise! I am about to start attempting to work
these suggestions into my file and will defintiely follow-up here if I have problems or further questions while working through this. Thanks again! Tanya |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting user to answer a question before saving and closing
got it to work just as I was wanting with all your help. Thanks everyone
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompting user to enable a link | Excel Discussion (Misc queries) | |||
Prompting User | Excel Programming | |||
Double prompting of an over-write confirmation when saving a fileof the same name | Excel Programming | |||
Prompting user before saving | Excel Programming | |||
Prompting user before saving | Excel Programming |