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

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

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