Articles

Solved Drop Down list in excel

May 8, 2012 at 09:08:08
Specs: Windows XP

I would like to run an if statement of off a drop. If the user selects "Other" from the list I want to allow them to enter a description in the next cell. However, if they do not select "Other" from the list I would like to not allow them to enter the description. I know to use the data validation function, but I have not idea what the formula should look like.

See More: Drop Down list in excel

Report •


✔ Best Answer
May 9, 2012 at 19:52:10

1 - Select all of the cells and Unlock them.
2 - Select Column O and Lock the Column.
3 - Protect the sheet...I used "secret" as the password in this example.
4 - You should still be able to enter data in every column except for O.
5 - Right-Click the sheet tab for the sheet you are working in.
6 - Paste the following code into the pane that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 14 Then
   If Target = "Other" Then
    myDesc = Application.InputBox("Enter Description")
      If myDesc = False Or _
         myDesc = "" Then
         Exit Sub
      Else
          Application.EnableEvents = False
            Me.Unprotect Password:="secret"
              Cells(Target.Row, 15) = myDesc
            Me.Protect Password:="secret"
          Application.EnableEvents = True
        Exit Sub
      End If
   End If
          Application.EnableEvents = False
            Me.Unprotect Password:="secret"
              Cells(Target.Row, 15) = ""
            Me.Protect Password:="secret"
          Application.EnableEvents = True
 End If
End Sub

When you chose "Other" from your drop down, an InputBox will allow you to enter a description which will be placed in Column O.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 8, 2012 at 11:52:07

You can't use Data Validation in the manner that you would like.

You can't prevent a user from entering data in a cell based on the value in another cell unless you use a macro.

Basically, you would use a macro to lock and unlock the data entry cell depending on what was chosen from the list. The macro would monitor the cell with the drop down and when it changed, it would look at the value that was chosen. If it was "Other" then the code would unlock the data entry cell and allow the user to enter data. If it saw any other value, it would lock the cell.

Hope that helps!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 8, 2012 at 12:05:41

Would you know how to write this macro ?

Report •

#3
May 8, 2012 at 12:35:26

For instance I have values in column "N" and when the user selects "Other" in the column I want to allow a description to be entered in column "O".

Report •

Related Solutions

#4
Report •

#5
May 9, 2012 at 19:52:10
✔ Best Answer

1 - Select all of the cells and Unlock them.
2 - Select Column O and Lock the Column.
3 - Protect the sheet...I used "secret" as the password in this example.
4 - You should still be able to enter data in every column except for O.
5 - Right-Click the sheet tab for the sheet you are working in.
6 - Paste the following code into the pane that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 14 Then
   If Target = "Other" Then
    myDesc = Application.InputBox("Enter Description")
      If myDesc = False Or _
         myDesc = "" Then
         Exit Sub
      Else
          Application.EnableEvents = False
            Me.Unprotect Password:="secret"
              Cells(Target.Row, 15) = myDesc
            Me.Protect Password:="secret"
          Application.EnableEvents = True
        Exit Sub
      End If
   End If
          Application.EnableEvents = False
            Me.Unprotect Password:="secret"
              Cells(Target.Row, 15) = ""
            Me.Protect Password:="secret"
          Application.EnableEvents = True
 End If
End Sub

When you chose "Other" from your drop down, an InputBox will allow you to enter a description which will be placed in Column O.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question