VBA CountIf logic checking Text Box Input

[Publicpenguin]

Hi Everyone,

Well I didn't think that I would be stopped here, but I'm having issues understanding the logic associated with basic excel functions when their used in VBA code. I'm trying to use the "CountIf" function inside my VBA code to count the total text boxes that have no value entered into them. I need to run this total through a later equation to account for the total number survey questions not answered. The logic should look like this:

• Count if "textbox A" has no value

+

• Count if "textbox B" has no value

+ .....N = 20 text boxes.

At the moment my attempts look like this:

EvalNotScored = (Application.WorksheetFunction.CountIf(PennShoulderEvalQ1TextBox, "") + Application.WorksheetFunction.CountIf(PennShoulderEvalQ2TextBox, "") +....

The full equation would be: (60 - ((Total number empty text boxes) * 3))

I've added my worksheet in question in case I'm not explaining clearly enough. The code is under our calculate button for our "Penn Shoulder" user form.

Thank you Everyone!

[alansidman]

I think that you issue may be that line ** code is to long. You will need to amend with line breaks. Try this:

EvalNotScored = (Application.WorksheetFunction.CountIf(PennShoulderEvalQ1TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ2TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ3TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ4TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ5TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ6TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ7TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ8TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ9TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ10TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ11TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ12TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ13TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ14TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ15TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ16TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ17TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ18TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ19TextBox, "") + _
    Application.WorksheetFunction.CountIf(PennShoulderEvalQ20TextBox, ""))

VBA code only allows so many characters per line. IN any event, it is easier to read and analyze if the lines are broken. To make them continuous, you place a space and an underscore and then the enter key and the code continues to the next line.

Alan

[Publicpenguin]

Hi Alan,

Thank you for the insight into the underscore and enter key aspect. I had seen and incorporated that before, but I was at a loss as to understanding it. Unfortunately that alone does not seem to be correcting my CountIf function. I'm receiving the following error:

Runtime Error 13.
Type mismash.

I've updated the VBA to reflect a more clearly identifiable codeset in the attachement. I hope this helps which is the same below:

EvalNotScored = (Application.WorksheetFunction.CountIf(PennShoulderEvalQ1TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ2TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ3TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ4TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ5TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ6TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ7TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ8TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ9TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ10TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ11TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ12TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ13TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ14TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ15TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ16TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ17TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ18TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ19TextBox, "") + _

    Application.WorksheetFunction.CountIf(PennShoulderEvalQ20TextBox, ""))    

    MsgBox EvalNotScored

[Sbarro79]

I think it doesn't work, because the first parameter in CountIf function must be Range

[Publicpenguin]

Hi Everyone,

I've gone back and cleaned up my code and tried declaring my "EvalNotScored" variable as a range, but to no avail. If anyone has any further suggestions on how to develop a functioning CountIf statement inside of VBA, I would greatly appreciate it. I've included my updated version of this workbook below. Again, the User form in question is the "Penn Shoulder" and the button that will execute the CountIf statement could the the "PennShoulderCalculateButton" button.

And of course, thank you for the suggestions thus far.