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.

 

[Celly]

I believe the issue may be that you are relying on the IF formula to not even evaluate the true expression if the condition is false, and it may not work that way (it will of course return the false expression).

Function BeepMe(doBeep as Boolean) As String
   
    if doBeep then Beep

    BeepMe = ""
End Function

=BeepMe(F2<30)

 

[6StringJazzer]

The problem is that you are using a function call with a side effect in a worksheet formula. It is beeping every time the formula is calculated, which might happen when you are doing something totally different. The best practice for a function is that it should return a value and do nothing else.

Instead of calling BeepMe from a worksheet formula, you should use the Worksheet_Change event. When an entry is added to column A, this sub will check for a negative value and call BeepMe if it's negative.

 

[6StringJazzer]

@Celly might have the better diagnosis here, but I still recommend a different solution.

 

[Celly]

I think we are both correct @6StringJazzer. Having the function beep after a calculation is odd.


Is it possible to make the BeepMe function call in response to the RFID read? That would be a lot cleaner.

 

[6StringJazzer]

That is basically what I suggested in post #3 but did not write the actual code to do it. It would be something like:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Count = 1 And Target.Column = 1 And Cells(Target.Row, "F") < 30 Then
      BeepMe
   End If

End Sub

 

[turkishjim]

Thank you very much indeed, @6StringJazzer and @Celly, for your prompt responses. I have tried the VBA and Formula suggested by @Celly but I’m afraid this works the same way as my own initial attempt. I dragged the =BeepMe (F2<30) down the column, but basically if there is a value anywhere in column F less than 30 a beep accompanies every input in column A.

Unfortunately @6StringJazzer’s suggestion in post #3 was way above my VBA level and really meant nothing to me. The new code in post #6 looked good but I was unsure exactly what to do with it or whether it was meant to be accompanied by a formula in column F. I tried replacing my own and @Celly’s VBA with this suggestion, but this was clearly not the way forward as no beeps at all were produced.

I have to say that this whole idea is a “would be nice” rather than a “must have” and if it cannot be achieved easily please do not waste any more time on it. Alternatively if it would help I can probably post an anonymised version of the spreadsheet. Just in case it is important I should probably also point out something I forgot to mention in my original post, which is that column B is actually a helper column which uses a VLOOKUP to fetch membership number data from a third sheet as 2 members have been issued with replacement cards not programmed with their original numbers.

Once again many thanks for taking the time to try and help, whether or not you decide to offer any further insights.

Jim

 

[6StringJazzer]

If you have any way to share your file I can put the VBA in it, test it, and give it back.

 

[turkishjim]

@6StringJazzer, that is exceptionally kind of you, but please do not feel obligated to spend a lot of time on it. A zipped version of the anonymised spreadsheet (names are fictitious and contact information has been removed) can be found at www.woodworkprojects.co.uk/anonymised_new_login.zip and I hope you can access this. (Not sure what a Mini-sheet is, that the forum will let me upload.)
Thanks again.
Jim

 

[6StringJazzer]

You have a big problem that has nothing to do with beeping.

You have two columns of dates on the Members sheet in R:S (joined, renew). But these are text, not actually dates. This suggests that you imported them from some other source. So on your login sheet, the formula in column F is always blank, because you can't subtract TODAY from a text value, so an error occurs.

In my revised version, I changed the formatting for dates to Custom "dd/mm/yyyy" then changed the renew date for member 848 to be a true date (yellow cell) and then the result in Days Left on sheet login is correct and I get a beep (it's not exactly a beep, but it's a sound).

Dropbox - File Deleted - Simplify your life

www.dropbox.com