VBA button to check eligibility

917

New Member
Joined
Apr 5, 2013
Messages
23
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

MisterProzilla

Active Member
Joined
Nov 12, 2015
Messages
263
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...

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

New Member
Joined
Apr 5, 2013
Messages
23
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,516
Messages
5,596,617
Members
414,081
Latest member
Subaru_Steve

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