If a Cell within a range contains "Text" then msgbox

[emmhor]

Hi all,

I got a worksheet with many combo boxes they're linked to a cell and what my idea is to provide a prompt if one of the cell within the range contain text which is not allowed.
So i got combo box S1-S29
The selection user can click within that combo box is : Kies,1,2,3,4,5
So I tried the following,

Code based on, range, if cell cointains "kies" then msgbox.
This work with single cells but not when used in a range.

I tried multiple codes but I bumped into issues where each cell is checked 1 by 1 but only 1 needs to be correct to bypass the msgbox.
It is hard to explain since I don't got the code anymore.

So could anyone write me a sub regarding my question.
Comboxboxes name convention s1-s29
Linked to cell s1-s29 so result of ^^ will be visible in the cells corresponding to the combobox name3

What I want is that a msgbox apear when a cell within the range s1-s29 has the word "kies" or isEmpty.
And a code that scrolls through all the cells 1 by 1 not all at once.

 

[FormR]

Hi, how about something like this:

If Application.CountIf(Range("S1:S29"), "kies") Or Application.CountBlank(Range("S1:S29")) Then
    MsgBox "Not all cells complete"
    Exit Sub
End If

 

[emmhor]

Hey and thank you for your reply,

This did seem to work when I use numbers. but with the work "kies"it didn't seem to work.
Im always getting the prompt"not all cells complete"

But for this is enough thanks allot +1

Hi, how about something like this:

If Application.CountIf(Range("S1:S29"), "kies") Or Application.CountBlank(Range("S1:S29")) Then
    MsgBox "Not all cells complete"
    Exit Sub
End If

 

[FormR]

This did seem to work when I use numbers. but with the work "kies"it didn't seem to work.
Im always getting the prompt"not all cells complete"

Hi, I thought you wanted the message box to appear if any of the cells contained the word "kies" - is that not the case?

 

[emmhor]

Ow haha no it's not like that.
I use combo boxes to give out a score per subject a score from Kies,and then 1-5.
And It is very important that all fields have a score so a value Kies, or no number IS NOT ALLOWED.

So msgbox on "Kies" or a number

Hi, I thought you wanted the message box to appear if any of the cells contained the word "kies" - is that not the case?

 

[FormR]

I'm even more confused now! In plain English - what are the conditions that should trigger the message box?

 

[emmhor]

If Cell S1-S28 cointain the text "kies" or isEmpty so no number/letter then msgbox "XxxxxX"

 

[FormR]

If Cell S1-S28 cointain the text "kies" or isEmpty so no number/letter then msgbox "XxxxxX"

Hi, that is what the code does - are you sure that all the cells are populated and none have "kies"? Is the sheet where this test needs to happen active when you run the code?