Conditional beep works when condition is met but then beeps when false in subsequent entries.

turkishjim

New Member
Joined
Jul 16, 2014
Messages
12
I have a spreadsheet used to record attendance at monthly meetings (after Covid obviously) using an RFID scanner to read membership cards input membership number to column A. Columns B to E use VLOOKUP to extract personal details from the current membership list on a second worksheet, the most important being column E which contains the membership expiry date. Column F uses the formula
Excel Formula:
=IFERROR(E2-TODAY()," ")
to give a numerical value of days left until renewal, and conditional highlighting makes prominent any negative values.
Our group consists of retired people of varying IT skills and I want to add an audible warning to the conditional formatting, so that the person checking members in is alerted even if he/she is not looking at the screen.

I have inserted this VBA module:
VBA Code:
Function BeepMe() As String
    Beep
    BeepMe = ""
End Function

and Column G has the formula
Excel Formula:
=IF(F2<30,BeepMe(),"")
.

I am testing the spreadsheet by manually entering membership numbers in column A, and it performs as expected when I enter numbers of current members. It also beeps when I enter the number of a member who has not renewed, but as soon as it has beeped every entry causes another beep even when the condition is false and membership is still extant.

You can probably tell that I am no expert in VBA, so any assistance in sorting this issue would be much appreciated.
 

turkishjim

New Member
Joined
Jul 16, 2014
Messages
12
@6StringJazzer,
Very many thanks for this. I am aware of Excel formatting dates as text but this did not occur to me as the problem. You are also right that the data is imported, from our membership database. I will look into whether we can import this as dates, because some of those who will be preparing the spreadsheet for the next meeting would probably have trouble re-formatting.
I am very much obliged to you, sir.
Jim
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Forum statistics

Threads
1,137,154
Messages
5,679,906
Members
419,862
Latest member
Bluewings666

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