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

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

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

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

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

got it to work just as I was wanting with all your help. Thanks everyone