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
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:
and Column G has the formula
.
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.
Excel Formula:
=IFERROR(E2-TODAY()," ")
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.