Why is my VBA debugging?

[benjinorth]

Hi guys,

I've created a sheet for clients to fill in, some cells are mandatory and require filling in.
I have no problem with the blank cells, however some contain drop down boxes with the title "click here to select" or "click here for more options"
Obviously if the cell remains as "click here to select" etc. then I want it to highlight red.

Here's a part of the code with part that's debugging (highlighted in red).
Any help would be greatly appreciated.

Sub ForceEntry()

ActiveSheet.Unprotect "Admin"
blankCell = "D8,J8,G24,J24,D25,J28,D30,D213,D214"
clickCell = "D3,D10,J10,D12,D24,D35,D37,J37,G39,G40,D42,D44,D46,D51,D55,D59,D63,D68,D74,D76,D79,D80,D81,J81,D84,J84,D85,J85,D86,J86,D90,D91,D92,D93,D98,G100,G101,D103,D105,D111,D112,D113,D114,D115,D174,D176,D177,D179,D180,F185,F200,D205,D207,D209,D216"


If Range(blankCell).Value = "" Then
Range(blankCell).Interior.Color = RGB(255, 0, 0)
End If
If InStr(1, (Range(clickCell).Value), "click") > 0 Then
Range(clickCell).Interior.Color = RGB(255, 0, 0)
MsgBox ("Mandatory Field(s) have not been filled in. Please answer question(s) highlighted in Red as appropriate")
Cancel = True
Range("D3").Select
ActiveSheet.Protect "Admin"
Exit Sub


Call SpellBox


End If
End Sub

 

[Snakehips]

Try remove the space in the clickCell string ,D 90, to ,D90,

 

[Sektor]

Try remove the space in the clickCell string ,D 90, to ,D90,

I think this is due to forum formatting.

 

[Snakehips]

I think this is due to forum formatting.

Could be but if that space is removed the code does not error!

I would question whether or not the code is doing what OP expects ?

@benjinorth
The tests for blankCell = "" and clickCell contains "Click" will only set red if all the test cells are "" or "Click"
So one non-blank will prevent blankCells being made red and one non-Click will prevent clickCells being made red.

Apologies if this is presumptuous and incorrect but maybe code that loops through the ranges checking individual cells is what you need???

Sub ForceEntry()


ActiveSheet.Unprotect "Admin"
blankCell = "D8,J8,G24,J24,D25,J28,D30,D213,D214"
clickCell = "D3,D10,J10,D12,D24,D35,D37,J37,G39,G40,D42,D44,D46,D51,D55,D59,D63,D68,D74,D76,D79,D80,D81,J81,D84,J84,D85,J85,D86,J86,D90,D91,D92,D93,D98,G100,G101,D103,D105,D111,D112,D113,D114,D115,D174,D176,D177,D179,D180,F185,F200,D205,D207,D209,D216"


Complete = True  'use to flag if al cells good
Application.ScreenUpdating = False
For Each cell In Range(blankCell)
If cell.Value = "" Then
cell.Interior.Color = RGB(255, 0, 0)
Complete = False  'flag missing entry
Else
cell.Interior.ColorIndex = -4142  'Format clear if not blank ????
End If
Next cell


For Each cell In Range(clickCell)
If InStr(1, (cell.Value), "Click") > 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Complete = False    'flag missing entry
Else
cell.Interior.ColorIndex = -4142   'Format clear if not contain Click ????
End If
Next cell
If Complete = False Then
MsgBox ("Mandatory Field(s) have not been filled in. Please answer question(s) highlighted in Red as appropriate")
Else
MsgBox ("Mandatory Field(s) are all filled.  Thank You.")  ' remove if not applicable
End If
Cancel = True
Range("D3").Select
ActiveSheet.Protect "Admin"
Exit Sub
Application.ScreenUpdating = True


Call SpellBox


End Sub

 

[benjinorth]

@snakehips thanks, I think you're right with the one formula blocking the other.
I will give this a go, it seems to make more sense.