VBA Programming with Checkbox

[Premanand Sethuraman]

Dear All,
I am currently doing a program in which I gave inout messages to nearly all
of the cells in a Single sheet. I kept because this message/information will
be useful if the user is new person. But my old users don't want the input
message as it irks always when the entering cells. So I decided to give one
checkbox in such a way that if he click the checkbox, all the input messages
of all cells will be hided. If he want the input message back he can again
click the check box by removing the tick sign in it and after he can see the
input messages in all the cells. I wrote the foll. coding also for it.

Sub CheckBox31_Click()
If Cells(6, 8).Value = "True" Then
Range("C11210").Validation.ShowInput = False
Else
ActiveSheet.Range("C11210").Validation.ShowInput = True
End If
End Sub

But during "Run" it is showing the error as "Application-defined or
object-defined error" which I cannot understand even after entering the
"help" command.
Please help me to sort out the problem.

Regards,
Premanand.S

 

[paulgu]

My guess would be that one or more cells in the range you specify doesn't have validation set up, as this will cause the error you mention. You need to be sure that each cell you apply this to already has validation. You could either choose your range more carefully or cycle through the cells in it individually after setting On Error Resume Next so that it just carries on if it hits one without validation.

 

[JLGWhiz]

Sub CheckBox31_Click()
Dim c As Range
If Cells(6, 8).Value = "True" Then
For Each c In Range("C11210")
If Not IsEmpty(c) Then
c.Validation.ShowInput = False
End If
Next
Else
For Each c In Range("C11210")
If Not IsEmpty(c) Then
c.Validation.ShowInput = True
End If
Next

 

[JLGWhiz]

Last two lines were clipped off.

Sub CheckBox31_Click()
Dim c As Range
If Cells(6, 8).Value = "True" Then
For Each c In Range("C11210")
If Not IsEmpty(c) Then
c.Validation.ShowInput = False
End If
Next
Else
For Each c In Range("C11210")
If Not IsEmpty(c) Then
c.Validation.ShowInput = True
End If
Next
End If
End Sub

 

[Premanand Sethuraman]

Thanks for your reply and also for explaining the application of "For Each "
function.
I run the program with this coding but unfortunately still I am getting the
same error as I explained in my previous post "Application-defined or
object-defined error".
Please guide me whether there should be any requirement error statement in
the above coding.

regards,
premanand.s

 

[paulgu]

Unfortunately the IsEmpty test doesn't work here. I've been unable to find a suitable test for the existence of validation in a cell that does work and I think you have to fall back on On Error Resume Next. Here's code that uses that (and it does work, I've tested it!). 1004 is the error code that you get when attempting to reference the Validation object when there is no validation set for the cell.

Sub CheckBox31_Click()
Dim c As Range
On Error Resume Next
If Cells(6, 8).Value = "True" Then
For Each c In Range("C11210")
c.Validation.ShowInput = False
If Err.Number <> 1004 Then GoTo ErrorHandler
Next
Else
For Each c In Range("C11210")
c.Validation.ShowInput = True
If Err.Number <> 1004 Then GoTo ErrorHandler
Next
End If
Exit Sub
ErrorHandler:
' error code here
End Sub

 

[JLGWhiz]

OK Premanand, let's try this one. It uses a different criteria in the If ..
Then statement.

Sub CheckBox31_Click()
Dim c As Range
If Cells(6, 8).Value = "True" Then
For Each c In Range("C11210")
If c.Validation.Value Then
c.Validation.ShowInput = False
End If
Next
Else
For Each c In Range("C11210")
If c.Validation.Value Then
c.Validation.ShowInput = True
End If
Next
End If
End Sub

Your error is being generated when there is no Validation assigned to the
cell. You need to also be aware that copying from one cell and pasting to a
cell that had valication assigned will remove the validation property from
the receiving cell. So if there is any copying and pasting in your code, you
could be eliminating validation from cells that you think you have it in.
Good luck.

 

[Premanand Sethuraman]

Thanks Sir..
I finally found the result with the help of your suggestions in last mail.
I just enter into those cells (with in the range) in which there is no
validation, I simply type "Info" in all the Title of Input messages. As you
know the title of input message will only come if you type any input messafge
in those cells. Otherwise it do not show any message zlthough if we write
something in Title of Input message.
Hence I typed "Info" in thoise cells. Noe the error is not showing. It is
working fine.
Thanks for your suggestions.
Mr.Mike , I'm thanking you also for alerting me that the commands are
working fine in your workbook which makes me to find out my mistake.

Thanks
Premanand.S

 

[paulgu]

Code solution not using On Error

It may be too late for you now, but while investigating something else I found a solution that doesn't require the use of On Error or creating dummy Titles. It may be useful to someone else even if not to you.
It uses the excellent SpecialCells method to pick out only the cells that have some sort of validation (and a more economical way of coding it). It works with cells that don't have validation, have validation but no input message, and cells with both validation and input message.

Sub CheckBox31_Click()
Dim c As Range, rngVal As Range, bShow As Boolean
Set rngVal = Range("C11210").SpecialCells(xlCellTypeAllValidation)
bShow = Not Cells(6, 8).Value
For Each c In rngVal
If Not IsNull(c.Validation.InputMessage) Then
c.Validation.ShowInput = bShow
End If
Next
End Sub