|
Protecting Data Validation List from being modified in the formulabar
Hi all,
I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? |
Protecting Data Validation List from being modified in theformulabar
If those data|validation rules are still with that cell, the user won't be able
to make a change to the value. As soon as the user hits enter, the Data|Validation rules will check to see if that value is on that list. If it is, then it's ok. If it isn't, then they'll get the message that you defined in the data|validation dialog. About the only thing that the user could change is the case of the string: Cat CAT caT would all be valid if "cat" is on that list. Maddoktor wrote: Hi all, I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? -- Dave Peterson |
Protecting Data Validation List from being modified in theformulabar
Hi Dave,
Maybe it's me but it still doesn't seem to be working. The Data Validation List is compiled from a listing with the range name "Numbers". When the user selects one on the items from the list, it updates a number of other cells. Which works correctly. If the user then selects the formula bar once the Data Validation List is selected and modifies the item in the formula bar, then somehow the item in the cell with the Data Validation List is modified. Can I protect it in a way for this not to occur? Thanks. Dave Peterson wrote: If those data|validation rules are still with that cell, the user won't be able to make a change to the value. As soon as the user hits enter, the Data|Validation rules will check to see if that value is on that list. If it is, then it's ok. If it isn't, then they'll get the message that you defined in the data|validation dialog. About the only thing that the user could change is the case of the string: Cat CAT caT would all be valid if "cat" is on that list. Maddoktor wrote: Hi all, I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? |
Protecting Data Validation List from being modified in theformulabar
I'm confused about what's being selected and what's changing.
Say you have your list used for Data|Validation in Sheet2 A1:A10. And you use that list to validate A1 of Sheet1. Are you saying that if the user changes A1 of Sheet1, then the list in Sheet2 A1:A10 changes? If you don't have formulas or macros doing this, then I don't understand how this could happen. Maddoktor wrote: Hi Dave, Maybe it's me but it still doesn't seem to be working. The Data Validation List is compiled from a listing with the range name "Numbers". When the user selects one on the items from the list, it updates a number of other cells. Which works correctly. If the user then selects the formula bar once the Data Validation List is selected and modifies the item in the formula bar, then somehow the item in the cell with the Data Validation List is modified. Can I protect it in a way for this not to occur? Thanks. Dave Peterson wrote: If those data|validation rules are still with that cell, the user won't be able to make a change to the value. As soon as the user hits enter, the Data|Validation rules will check to see if that value is on that list. If it is, then it's ok. If it isn't, then they'll get the message that you defined in the data|validation dialog. About the only thing that the user could change is the case of the string: Cat CAT caT would all be valid if "cat" is on that list. Maddoktor wrote: Hi all, I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? -- Dave Peterson |
Protecting Data Validation List from being modified in theformulabar
Can you type invalid items directly in the cell?
If so, perhaps the Numbers range contains a blank cell, which would permit you to enter any value in the cell with data validation. Maddoktor wrote: Hi Dave, Maybe it's me but it still doesn't seem to be working. The Data Validation List is compiled from a listing with the range name "Numbers". When the user selects one on the items from the list, it updates a number of other cells. Which works correctly. If the user then selects the formula bar once the Data Validation List is selected and modifies the item in the formula bar, then somehow the item in the cell with the Data Validation List is modified. Can I protect it in a way for this not to occur? Thanks. Dave Peterson wrote: If those data|validation rules are still with that cell, the user won't be able to make a change to the value. As soon as the user hits enter, the Data|Validation rules will check to see if that value is on that list. If it is, then it's ok. If it isn't, then they'll get the message that you defined in the data|validation dialog. About the only thing that the user could change is the case of the string: Cat CAT caT would all be valid if "cat" is on that list. Maddoktor wrote: Hi all, I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Protecting Data Validation List from being modified in theformulabar
Doh!
I never remember to include blank cells in my list. To the OP. It may be better to use a range that expands/contracts with the number of items in that list. Debra explains it he http://contextures.com/xlNames01.html#Dynamic Debra Dalgleish wrote: Can you type invalid items directly in the cell? If so, perhaps the Numbers range contains a blank cell, which would permit you to enter any value in the cell with data validation. Maddoktor wrote: Hi Dave, Maybe it's me but it still doesn't seem to be working. The Data Validation List is compiled from a listing with the range name "Numbers". When the user selects one on the items from the list, it updates a number of other cells. Which works correctly. If the user then selects the formula bar once the Data Validation List is selected and modifies the item in the formula bar, then somehow the item in the cell with the Data Validation List is modified. Can I protect it in a way for this not to occur? Thanks. Dave Peterson wrote: If those data|validation rules are still with that cell, the user won't be able to make a change to the value. As soon as the user hits enter, the Data|Validation rules will check to see if that value is on that list. If it is, then it's ok. If it isn't, then they'll get the message that you defined in the data|validation dialog. About the only thing that the user could change is the case of the string: Cat CAT caT would all be valid if "cat" is on that list. Maddoktor wrote: Hi all, I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Dave Peterson |
Protecting Data Validation List from being modified in theformulabar
Maybe that's not it though. I missed the part about the entry altering
the Number list. Dave Peterson wrote: Doh! I never remember to include blank cells in my list. To the OP. It may be better to use a range that expands/contracts with the number of items in that list. Debra explains it he http://contextures.com/xlNames01.html#Dynamic Debra Dalgleish wrote: Can you type invalid items directly in the cell? If so, perhaps the Numbers range contains a blank cell, which would permit you to enter any value in the cell with data validation. Maddoktor wrote: Hi Dave, Maybe it's me but it still doesn't seem to be working. The Data Validation List is compiled from a listing with the range name "Numbers". When the user selects one on the items from the list, it updates a number of other cells. Which works correctly. If the user then selects the formula bar once the Data Validation List is selected and modifies the item in the formula bar, then somehow the item in the cell with the Data Validation List is modified. Can I protect it in a way for this not to occur? Thanks. Dave Peterson wrote: If those data|validation rules are still with that cell, the user won't be able to make a change to the value. As soon as the user hits enter, the Data|Validation rules will check to see if that value is on that list. If it is, then it's ok. If it isn't, then they'll get the message that you defined in the data|validation dialog. About the only thing that the user could change is the case of the string: Cat CAT caT would all be valid if "cat" is on that list. Maddoktor wrote: Hi all, I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Protecting Data Validation List from being modified in theformulabar
Wanna bet?
$10 USAian vs $10 Cubic Furlongs (isn't that the Candian currency???) Debra Dalgleish wrote: Maybe that's not it though. I missed the part about the entry altering the Number list. Dave Peterson wrote: Doh! I never remember to include blank cells in my list. To the OP. It may be better to use a range that expands/contracts with the number of items in that list. Debra explains it he http://contextures.com/xlNames01.html#Dynamic Debra Dalgleish wrote: Can you type invalid items directly in the cell? If so, perhaps the Numbers range contains a blank cell, which would permit you to enter any value in the cell with data validation. Maddoktor wrote: Hi Dave, Maybe it's me but it still doesn't seem to be working. The Data Validation List is compiled from a listing with the range name "Numbers". When the user selects one on the items from the list, it updates a number of other cells. Which works correctly. If the user then selects the formula bar once the Data Validation List is selected and modifies the item in the formula bar, then somehow the item in the cell with the Data Validation List is modified. Can I protect it in a way for this not to occur? Thanks. Dave Peterson wrote: If those data|validation rules are still with that cell, the user won't be able to make a change to the value. As soon as the user hits enter, the Data|Validation rules will check to see if that value is on that list. If it is, then it's ok. If it isn't, then they'll get the message that you defined in the data|validation dialog. About the only thing that the user could change is the case of the string: Cat CAT caT would all be valid if "cat" is on that list. Maddoktor wrote: Hi all, I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Dave Peterson |
Protecting Data Validation List from being modified in theformulabar
Sorry Dave,
I will try and explain it a little clearer. List used for Data|Validation is Sheet2 A1:A10 (range name "numbers") When I change A1, then it correctly recalculate all the necessary cells. Data|Validation works fine. If I was to then to select the formula bar and modify whatever is in the formula bar (while the Data|Validation (A1) is still selected)then the Data|Validation (A1) automatically modifies to whatever I type in the formula bar. Hope this is somewhat clearer. If not, I will work on a different way to give me the same result as Data|Validation. Thanks for your help. Dave Peterson wrote: I'm confused about what's being selected and what's changing. Say you have your list used for Data|Validation in Sheet2 A1:A10. And you use that list to validate A1 of Sheet1. Are you saying that if the user changes A1 of Sheet1, then the list in Sheet2 A1:A10 changes? If you don't have formulas or macros doing this, then I don't understand how this could happen. Maddoktor wrote: Hi Dave, Maybe it's me but it still doesn't seem to be working. The Data Validation List is compiled from a listing with the range name "Numbers". When the user selects one on the items from the list, it updates a number of other cells. Which works correctly. If the user then selects the formula bar once the Data Validation List is selected and modifies the item in the formula bar, then somehow the item in the cell with the Data Validation List is modified. Can I protect it in a way for this not to occur? Thanks. Dave Peterson wrote: If those data|validation rules are still with that cell, the user won't be able to make a change to the value. As soon as the user hits enter, the Data|Validation rules will check to see if that value is on that list. If it is, then it's ok. If it isn't, then they'll get the message that you defined in the data|validation dialog. About the only thing that the user could change is the case of the string: Cat CAT caT would all be valid if "cat" is on that list. Maddoktor wrote: Hi all, I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? |
Protecting Data Validation List from being modified in theformulabar
I fixed it .... I had a blank cell in the range name.
Sorry Dave. Was having just one of those days :-) Thanks again. Dave Peterson wrote: I'm confused about what's being selected and what's changing. Say you have your list used for Data|Validation in Sheet2 A1:A10. And you use that list to validate A1 of Sheet1. Are you saying that if the user changes A1 of Sheet1, then the list in Sheet2 A1:A10 changes? If you don't have formulas or macros doing this, then I don't understand how this could happen. Maddoktor wrote: Hi Dave, Maybe it's me but it still doesn't seem to be working. The Data Validation List is compiled from a listing with the range name "Numbers". When the user selects one on the items from the list, it updates a number of other cells. Which works correctly. If the user then selects the formula bar once the Data Validation List is selected and modifies the item in the formula bar, then somehow the item in the cell with the Data Validation List is modified. Can I protect it in a way for this not to occur? Thanks. Dave Peterson wrote: If those data|validation rules are still with that cell, the user won't be able to make a change to the value. As soon as the user hits enter, the Data|Validation rules will check to see if that value is on that list. If it is, then it's ok. If it isn't, then they'll get the message that you defined in the data|validation dialog. About the only thing that the user could change is the case of the string: Cat CAT caT would all be valid if "cat" is on that list. Maddoktor wrote: Hi all, I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? |
Protecting Data Validation List from being modified in theformulabar
Hmmm...having read the answer, I'll save my money for a safer bet,
thanks. <g Dave Peterson wrote: Wanna bet? $10 USAian vs $10 Cubic Furlongs (isn't that the Candian currency???) Debra Dalgleish wrote: Maybe that's not it though. I missed the part about the entry altering the Number list. Dave Peterson wrote: Doh! I never remember to include blank cells in my list. To the OP. It may be better to use a range that expands/contracts with the number of items in that list. Debra explains it he http://contextures.com/xlNames01.html#Dynamic Debra Dalgleish wrote: Can you type invalid items directly in the cell? If so, perhaps the Numbers range contains a blank cell, which would permit you to enter any value in the cell with data validation. Maddoktor wrote: Hi Dave, Maybe it's me but it still doesn't seem to be working. The Data Validation List is compiled from a listing with the range name "Numbers". When the user selects one on the items from the list, it updates a number of other cells. Which works correctly. If the user then selects the formula bar once the Data Validation List is selected and modifies the item in the formula bar, then somehow the item in the cell with the Data Validation List is modified. Can I protect it in a way for this not to occur? Thanks. Dave Peterson wrote: If those data|validation rules are still with that cell, the user won't be able to make a change to the value. As soon as the user hits enter, the Data|Validation rules will check to see if that value is on that list. If it is, then it's ok. If it isn't, then they'll get the message that you defined in the data|validation dialog. About the only thing that the user could change is the case of the string: Cat CAT caT would all be valid if "cat" is on that list. Maddoktor wrote: Hi all, I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Protecting Data Validation List from being modified in theformulabar
Debra is pretty smart, eh?
Maddoktor wrote: I fixed it .... I had a blank cell in the range name. Sorry Dave. Was having just one of those days :-) Thanks again. Dave Peterson wrote: I'm confused about what's being selected and what's changing. Say you have your list used for Data|Validation in Sheet2 A1:A10. And you use that list to validate A1 of Sheet1. Are you saying that if the user changes A1 of Sheet1, then the list in Sheet2 A1:A10 changes? If you don't have formulas or macros doing this, then I don't understand how this could happen. Maddoktor wrote: Hi Dave, Maybe it's me but it still doesn't seem to be working. The Data Validation List is compiled from a listing with the range name "Numbers". When the user selects one on the items from the list, it updates a number of other cells. Which works correctly. If the user then selects the formula bar once the Data Validation List is selected and modifies the item in the formula bar, then somehow the item in the cell with the Data Validation List is modified. Can I protect it in a way for this not to occur? Thanks. Dave Peterson wrote: If those data|validation rules are still with that cell, the user won't be able to make a change to the value. As soon as the user hits enter, the Data|Validation rules will check to see if that value is on that list. If it is, then it's ok. If it isn't, then they'll get the message that you defined in the data|validation dialog. About the only thing that the user could change is the case of the string: Cat CAT caT would all be valid if "cat" is on that list. Maddoktor wrote: Hi all, I have created a Data Validation List where the source is being taken from a separate worksheet. Once the worksheet is protected, the user will select something from the Data Validation List. I have noticed that the formula bar will display whatever the user selects and it is then possible for the user to modify this selection. Is is possible to protect the contents in the formula bar from being modified with VBA code? Thanks in advance? -- Dave Peterson |
| All times are GMT +1. The time now is 08:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com