In Cell Drop Down List

[brorick]

I am having a difficult time making an .InCellDropDownList work. I want the user to be able to select (High, Medium, Low, Ignore) from a drop down list in any cell in column c. I would prefer to use VBA. I am not sure how it would look and if the code would go in Sheet(1) or ThisWorkbook. Any help would be greatly appreciated. : pray2:

[Bob Phillips]

Why not just use Data>Validation? IT is so simple.

[brorick]

Hello xld, thank you for your response. I wanted to deliver the workbook without any chance of it being modified by the user. Most importantly I am learning code and I would like to learn how to achieve this with code. I have come up with the following code and it works with one big fault. It keeps on highlighting the entire c column. I know it is because of Range("c:c").Select. It is more of an annoyance. Does anyone know how to prevent the entire column from being highlighted?

Also, I placed the code under Private Sub Workbook_SheetChange and it forces the user to column c after any entry in any cell. Is it possible to display the list only when the user clicks or tabs to column c?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim Choices As String
Choices = "High, Medium, Low, Ignore"

Range("c:c").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Choices
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


End Sub

[Bob Phillips]

I don't understand what you are trying to do. Your code (when corrected) would add DV to every cell in column C if you change anything in a cell in column C. This seems skewed to me.

[brorick]

The easiest way to do things is sometimes the simplest way. I went back to the idea of just utilizing the Data Validation option within Excel. Everything is working as it should. Thanks.