Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Programming with Checkbox
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("C11:P210").Validation.ShowInput = False Else ActiveSheet.Range("C11:P210").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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Programming with Checkbox
Perhaps drop ActiveSheet from the Else. Qualify the range the same way in
both condition statements. Mike F "Premanand Sethuraman" wrote in message ... 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("C11:P210").Validation.ShowInput = False Else ActiveSheet.Range("C11:P210").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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Programming with Checkbox
Sub CheckBox31_Click()
Dim c As Range If Cells(6, 8).Value = "True" Then For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = True End If Next "Premanand Sethuraman" wrote: 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("C11:P210").Validation.ShowInput = False Else ActiveSheet.Range("C11:P210").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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Programming with Checkbox
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("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = True End If Next End If End Sub "JLGWhiz" wrote: Sub CheckBox31_Click() Dim c As Range If Cells(6, 8).Value = "True" Then For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = True End If Next "Premanand Sethuraman" wrote: 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("C11:P210").Validation.ShowInput = False Else ActiveSheet.Range("C11:P210").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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Programming with Checkbox
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 "JLGWhiz" wrote: 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("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = True End If Next End If End Sub "JLGWhiz" wrote: Sub CheckBox31_Click() Dim c As Range If Cells(6, 8).Value = "True" Then For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = True End If Next "Premanand Sethuraman" wrote: 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("C11:P210").Validation.ShowInput = False Else ActiveSheet.Range("C11:P210").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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Programming with Checkbox
Both methods work for me using the entire range and the For...Each. On what
line of code are you getting the error? Mike F "Premanand Sethuraman" wrote in message ... 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 "JLGWhiz" wrote: 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("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = True End If Next End If End Sub "JLGWhiz" wrote: Sub CheckBox31_Click() Dim c As Range If Cells(6, 8).Value = "True" Then For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = True End If Next "Premanand Sethuraman" wrote: 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("C11:P210").Validation.ShowInput = False Else ActiveSheet.Range("C11:P210").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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Programming with Checkbox
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("C11:P210") If c.Validation.Value Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") 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" wrote: 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 "JLGWhiz" wrote: 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("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = True End If Next End If End Sub "JLGWhiz" wrote: Sub CheckBox31_Click() Dim c As Range If Cells(6, 8).Value = "True" Then For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = True End If Next "Premanand Sethuraman" wrote: 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("C11:P210").Validation.ShowInput = False Else ActiveSheet.Range("C11:P210").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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Programming with Checkbox
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 "JLGWhiz" wrote: 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("C11:P210") If c.Validation.Value Then c.Validation.Show Input = False End If Next Else For Each c In Range("C11:P210") 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 validation 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" wrote: 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 "JLGWhiz" wrote: 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("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = True End If Next End If End Sub "JLGWhiz" wrote: Sub CheckBox31_Click() Dim c As Range If Cells(6, 8).Value = "True" Then For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = False End If Next Else For Each c In Range("C11:P210") If Not IsEmpty(c) Then c.Validation.ShowInput = True End If Next "Premanand Sethuraman" wrote: 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("C11:P210").Validation.ShowInput = False Else ActiveSheet.Range("C11:P210").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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programming a checkbox in Excel | Excel Programming | |||
Programming a checkbox in Excel | Excel Programming | |||
link a checkbox in a sheet to a checkbox on a userform? | Excel Programming | |||
checkbox on form reset from checkbox on sheet | Excel Programming | |||
programming checkbox properties | Excel Programming |