Why is my VBA debugging?

benjinorth

New Member
Joined
Jul 25, 2016
Messages
13
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.

Rich (BB code):
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
 
Last edited by a moderator:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try remove the space in the clickCell string ,D 90, to ,D90,
 
Upvote 0
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???

Code:
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
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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
Back
Top