SELECT CASE or something else

917

New Member
Joined
Apr 5, 2013
Messages
23
Hi all

I have a worksheet that has a number of cells that is used to effectively act as a way of checking eligibility for a service.

These cells are, say;

A2, C2, E2

A8, C8, E8

A14, C14, E14

At present, the user has to manually check that two or more of the above cells has a value/text in it, before pressing an existing command button that opens another sheet for more info to be input.

I'm trying to find a way of adding some code to the existing button that checks each of these cells to see if more than one of them has a value/text in. Should less than two contain a value/text, I want a msgbox to appear (I'm ok with that bit lol) informing the user they are not eligible. They will then not be allowed to access the other sheet.

After doing some reading, I thought a SELECT CASE statement might help but have no idea what I'm doing, what code I should add, or whether there is a better way.

Any ideas gratefully received. Thanks
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,266
Office Version
  1. 2013
Platform
  1. Windows
I do not understand this:
I want a msgbox to appear (I'm ok with that bit lol)

You want the message box to say "I'm ok with that bit lol"
Is that what you want. And to not allow access to the
Other Sheet means you have to protect hide or do something else to not allow them to access the other sheet.
 

917

New Member
Joined
Apr 5, 2013
Messages
23
Sorry...I meant that I'm ok with coding the message box bit, probably shouldn't have put that in brackets. ;)

Msgbox needs to say something like 'More than two criteria are required in order to be eligible to proceed'.

There is code already in the workbook (not mine originally) that does prevent them accessing other sheets, so I planned to follow suit on that. It was just the ability to check the cells that I was struggling with. Many thanks
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
Hi

Maybe
Code:
Sub count_criteria()

COUNTDATA = Application.WorksheetFunction.CountA(Range("A2")) + Application.WorksheetFunction.CountA(Range("C2")) _
+ Application.WorksheetFunction.CountA(Range("E2")) + Application.WorksheetFunction.CountA(Range("A8")) + Application.WorksheetFunction.CountA(Range("C8")) _
+ Application.WorksheetFunction.CountA(Range("E8")) + Application.WorksheetFunction.CountA(Range("A14")) + Application.WorksheetFunction.CountA(Range("C14")) _
+ Application.WorksheetFunction.CountA(Range("E14"))

If COUNTDATA < 2 Then
MsgBox ("CRITERIA NOT MET")
Exit Sub
Else

'do your stuff
MsgBox ("ok")

End If

End Sub


Dave
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,645

ADVERTISEMENT

Shortening Dave's a bit:

Code:
    If WorksheetFunction.CountA(Range("A2,C2,E2,A8,C8,E8,A14,C14,E14")) < 2 Then
        MsgBox ("You need at least 2 criteria")
    Else
    
    ' Do stuff
    End If
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
Shortening Dave's a bit:

Code:
    If WorksheetFunction.CountA(Range("A2,C2,E2,A8,C8,E8,A14,C14,E14")) < 2 Then
        MsgBox ("You need at least 2 criteria")
    Else
    
    ' Do stuff
    End If


looks better eric LOL.
 

917

New Member
Joined
Apr 5, 2013
Messages
23

ADVERTISEMENT

Ah, thanks very much guys - that worked a treat.

What I hadn't catered for was a value that might appear in those cells of 'no help needed'. I did think of changing the validation list for those cells and removing this value, but unfortunately it is required. Is there a way to amend the above code to discount any cells that feature that text, whilst still checking for less than two?

Thanks
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
your welcome

try this, although, within the range A2:E14, Will there lightly be the words 'no help needed' anywhere else other than your specified cells A2,C2,E2,A8,C8,E8,A14,C14,E14.
if so, the code needs to be adjusted to account for this.

Code:
Sub count()

 count1 = WorksheetFunction.CountA(Range("A2,C2,E2,A8,C8,E8,A14,C14,E14"))
 count2 = WorksheetFunction.CountIf(Range("A2:E14"), "no help needed")
 
    If count1 - count2 < 2 Then
        MsgBox ("You need at least 2 criteria")
            Else
                MsgBox ("ok")
                'Do stuff
    End If
    
End Sub
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,690
or shorter

Code:
Sub count()

 If WorksheetFunction.CountA(Range("A2,C2,E2,A8,C8,E8,A14,C14,E14")) - _
            WorksheetFunction.CountIf(Range("A2:E14"), "no help needed") < 2 Then
                MsgBox ("You need at least 2 criteria")
                    Else
                    'Do stuff
  End If
    
End Sub

Dave
 
Last edited:

917

New Member
Joined
Apr 5, 2013
Messages
23
Perfect SQUIDD - thanks again.

Thankfully 'no help needed' only applies to those cells so it all works like a charm. Very much appreciated! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,508
Messages
5,602,072
Members
414,498
Latest member
jordanmiller7890

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