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

[turkishjim]

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

=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:

Function BeepMe() As String
    Beep
    BeepMe = ""
End Function

and Column G has the 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]

@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