Modify VBA code - Workbook_BeforeClose Event

Myproblem

Board Regular
Joined
May 24, 2010
Messages
198
I need to send some reports to users, and ask them to fill some data into.
They should send me back that report.
The problem is they do not always fill data and back it to me, so I want to make mandatory cells using BeforeClose event.
The main idea is that cell in my case is actually difference of values of two other cells, F5=C5-D5; F5 must be zero ("0"). they should fill in C5 and D5.

I found on link some maybe usefull vba, but I do nt know what is need to be done to make it happen (http://www.ozgrid.com/forum/showthread.php?t=20231).
So wanted value in F5 should be zero (0).
thx

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim shtTemp As Object

' add your 5 sheetnames to the array list
For Each shtTemp In Worksheets(Array("Sheet1"))
If shtTemp.Range("F5").Value = "0" Then
MsgBox "You must complete an entry in Cell F5 on sheet " _
& shtTemp.Name, vbOKOnly Or vbExclamation
Cancel = True
Exit For
End If
Next
End Sub
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim shtTemp As Worksheet


    For Each shtTemp In Worksheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"))
        If WorksheetFunction.CountA(shtTemp.Range("C5:D5")) < 2 Then
            MsgBox "You must complete an entry in Cell C5 and D5 on sheet " & shtTemp.Name, vbExclamation
            Cancel = True
            Exit For
        End If
    Next


End Sub


PS: Please use code tags when you paste VBA code on the forum. To add code tags, you can click the # icon and paste your code.
Or, a manual approach is, to type in your post:

Code:
then paste your code, and lastly, type:
['/code]

WITHOUT the single quotation mark ' in front of the /

Thanks for the consideration.
 

Myproblem

Board Regular
Joined
May 24, 2010
Messages
198
thx for quick reply and consideration (I will next time apply it), but this proposed solution is not what I need. It is true that cells C5 and D5 must be filled, but there is problem that I need zero as difference in cell F5. According to this solution any number can be filled into and VBA will works and enable users to close workbook, this is wrong it should chech whatever combination of numbers in C5 and D5, as difference and give result zero (0).
any tips
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Then, add your own IF formula that you already have.
But your logic is wrong: you launch a MsgBox when F5 = 0. It should be when F5 <> 0.
And leave out the quotes around "0". You are dealing with numbers, not text.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top