VBA button to check eligibility

[917]

Hi all

Been a long time since I've used VBA and a little rusty, so thought I'd seek some help.

I've got a spreadsheet that contains two tabs:

1: Eligibility
2: More info

Within the Eligibility tab there are four fields called Criteria 1, Criteria 2, Criteria 3 and Criteria 4 that the user enters either a Yes or No into.

I need to insert a button that will check each of these fields and if a No is present in all four, a message box needs to appear explaining they are not eligible.

If a Yes is present in 1 or more (meaning they are eligible) it needs to open the More Info tab for additional data to be input.

I'm ok with the message box and open tab code, but I'm struggling to find a way round the first bit.

Any pointers or help much appreciated!
Thanks

 

[MisterProzilla]

Hi there,

Assuming your criteria are in columns B,C,D & E, and that you've got a header of one row at the top, try this:

Enter the code below into a module...

Sub CheckEligibility()

'first check selection is within table
If ActiveCell.Row < 2 Then
MsgBox "Select a record from table below", vbOKOnly
Exit Sub
End If

'then check eligibility of selected row
If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B" & ActiveCell.Row & ":E" & ActiveCell.Row), "Yes") > 0 Then
Sheets("More Info").Activate
Else
MsgBox "Not eligible", vbOKOnly
End If

End Sub

Add a button anywhere in your sheet that runs this code. To run the check, click anywhere in the same row as the record you want to check, then click the button.

 

[917]

MisterProzilla - thanks so much for the quick response and the code! That works perfectly and I was able to update the msgbox part too. Thanks again

 

[MisterProzilla]

No problem, glad that's helped