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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Celly

Board Regular
Joined
Jan 29, 2015
Messages
81
Office Version
  1. 2016
Platform
  1. Windows
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).

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

    BeepMe = ""
End Function

Excel Formula:
=BeepMe(F2<30)
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
857
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 27, 2010
Messages
857
Office Version
  1. 365
Platform
  1. Windows
@Celly might have the better diagnosis here, but I still recommend a different solution.
 

Celly

Board Regular
Joined
Jan 29, 2015
Messages
81
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jan 27, 2010
Messages
857
Office Version
  1. 365
Platform
  1. Windows
That is basically what I suggested in post #3 but did not write the actual code to do it. It would be something like:

VBA Code:
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

New Member
Joined
Jul 16, 2014
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Jan 27, 2010
Messages
857
Office Version
  1. 365
Platform
  1. Windows
If you have any way to share your file I can put the VBA in it, test it, and give it back.
 

turkishjim

New Member
Joined
Jul 16, 2014
Messages
12
@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

Well-known Member
Joined
Jan 27, 2010
Messages
857
Office Version
  1. 365
Platform
  1. Windows
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).

 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,133,244
Messages
5,657,587
Members
418,401
Latest member
B_A_M155

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