List
I have a code that inserts a validation list into a cell.
With ycell.Offset(num, 0).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=costitemrng" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = False .ShowError = True End With This works great except for on the excel worksheet I can right click on the cell where I have the list and a window with a list of options for that cell pops up. One of the options is 'pick from drop_down List...' and in this list are contents that aren't in list that I have set for that cell. How do I make it so that that option doesn't appear in the pop up window, or make it so that if something is selected that isn't in the list that I have for that cell, it won't allow it in the cell? Thanks |
List
Hi,
You can stop the user right clicking all together, which stops them doing a lot of things sometime they shouldnt be. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Cancel = True End Sub This code is on the sheet you want to stop. So you can allow on a single page but not on the rest the choice is yours. You could also add other code here to diable pop up menu options etc... Regards Anthony "ranswert" wrote in message ... I have a code that inserts a validation list into a cell. With ycell.Offset(num, 0).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=costitemrng" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = False .ShowError = True End With This works great except for on the excel worksheet I can right click on the cell where I have the list and a window with a list of options for that cell pops up. One of the options is 'pick from drop_down List...' and in this list are contents that aren't in list that I have set for that cell. How do I make it so that that option doesn't appear in the pop up window, or make it so that if something is selected that isn't in the list that I have for that cell, it won't allow it in the cell? Thanks |
List
Thank You
"Anthony" wrote: Hi, You can stop the user right clicking all together, which stops them doing a lot of things sometime they shouldnt be. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Cancel = True End Sub This code is on the sheet you want to stop. So you can allow on a single page but not on the rest the choice is yours. You could also add other code here to diable pop up menu options etc... Regards Anthony "ranswert" wrote in message ... I have a code that inserts a validation list into a cell. With ycell.Offset(num, 0).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=costitemrng" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = False .ShowError = True End With This works great except for on the excel worksheet I can right click on the cell where I have the list and a window with a list of options for that cell pops up. One of the options is 'pick from drop_down List...' and in this list are contents that aren't in list that I have set for that cell. How do I make it so that that option doesn't appear in the pop up window, or make it so that if something is selected that isn't in the list that I have for that cell, it won't allow it in the cell? Thanks |
All times are GMT +1. The time now is 03:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com