SELECT CASE or something else

[917]

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

 

[My Aswer Is This]

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]

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]

Hi

Maybe

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

 

[Eric W]

Shortening Dave's a bit:

    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]

Shortening Dave's a bit:

    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]

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]

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.

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]

or shorter

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

 

[917]

Perfect SQUIDD - thanks again.

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