Drop-down list

Microsoft Office excel 2007 home & stude...
September 3, 2010 at 08:13:58
Specs: Windows XP
Hello,

I was searching for instructions on how to create an “other” option and type in box for drop-down menus, and I came across this question (from May 11, 2010):

The drop-down list of valid entries may be compiled from cells elsewhere on the worksheet. But I'd like to have the list of e.g. 4 positions where the last one would be "other" (--> 10,15,20,"other") whereas cliking on it I could type in any value that would be then calculated by the formula.

and this solution:

Private Sub Worksheet_Change(ByVal Target As Range)
'If Drop Down choice is Other, get value
'from user and place it in A1
If Target.Address = "$A$1" Then
If Target = "Other" Then
otherVal = Application.InputBox("Enter Other Value")
If otherVal = False Then Exit Sub
Range("A1").Value = otherVal
End If
End If
End Sub 

I'm new to VBA, and I was wondering how I would need to modify this code to apply to all cells in column A (for example, if "other" is selected in A15, a box would pop up and the response would be placed in box A15).

I'd appreciate any guidance you can give me!

Thanks very much,
Kristen


See More: Drop-down list

Report •


#1
September 3, 2010 at 09:33:00
Try this. The comments should explain how it works.

Private Sub Worksheet_Change(ByVal Target As Range)
'If change was made to Column 1 (A)..
  If Target.Column = 1 Then
'If only one cell was changed...
   If Target.Cells.Count = 1 Then
'If Drop Down choice is Other...
    If Target = "Other" Then
'Get value from user
     otherVal = Application.InputBox("Enter Other Value")
'Quit if User Cancels
      If otherVal = False Then Exit Sub
'Otherwise place value in Target Cell
     Target.Value = otherVal
    End If
   End If
  End If
End Sub


Report •

#2
September 3, 2010 at 12:05:40
Thanks very much- that did work!

Report •

#3
September 3, 2010 at 14:17:16
Hello again,

How would I apply this code (using the word “Multiple” instead of “Other”) to another column in the same workbook? I tried pasting the following code below the one you created, but the input box never popped up when I selected “Multiple” on the drop-down in column H:

Private Sub Worksheet_Change2(ByVal Target As Range)
'If change was made to Column 8 (H)..
  If Target.Column = 8 Then
'If only one cell was changed...
   If Target.Cells.Count = 1 Then
'If Drop Down choice is Multiple...
    If Target = "Multiple" Then
'Get value from user
     otherVal = Application.InputBox("Please enter certificate(s)")
'Quit if User Cancels
      If otherVal = False Then Exit Sub
'Otherwise place value in Target Cell
     Target.Value = otherVal
     
      End If
   End If
  End If
End Sub 

I changed the first line to Worksheet_Change2, since the following message popped up when I did not add the 2.

Compile error. Ambiguous name detected: Worksheet_Change

I also tried writing “multipleVal” instead of “otherVal” in case that was the problem. Thanks very much- I appreciate your help. Also, can you recommend a web resource that provides a good overview of basic coding?

Thank you,
Kristen


Report •

Related Solutions

#4
September 7, 2010 at 14:52:16
Hello,

I have one more follow-up question. This macro is not working on other people's computers. Do you know why this is and how to fix it?

Thanks,
Kristen


Report •

#5
September 7, 2010 at 19:54:41
re: "How would I apply this code (using the word “Multiple” instead of “Other”) to another column in the same workbook?"

I assume you mean "in the same worksheet". A workbook doesn't really have columns. A workbook is an Excel document containing one or more worksheets.

That said, a worksheet can only have one Worksheet_Change macro stored with it. The object is that the Worksheet_Change code monitors the worksheet and performs actions (or doesn't) based on what was changed.

You'll noticed that the code I offered only does what it does if the change was made to Column 1 because the first thing it does is check to see if the change was to Column 1.

If you want it do something different if the change is made to Column 8, then just include your code after mine in the same macro:

Private Sub Worksheet_Change(ByVal Target As Range)
'If change was made to Column 1 (A)..
  If Target.Column = 1 Then
   'blah blah blah
  End If

'If change was made to Column 8 (H)..
  If Target.Column = 8 Then
   'blah blah blah
  End If

End Sub

There are other ways to accomplish the same thing, such as Select Case, but you get the general idea. One Worksheet_Change macro per sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
 Select Case Target.Column
   Case 1
     MsgBox "Column A Changed"
   Case 8
     MsgBox "Column H Changed"
 End Select
End Sub

As far as it not working on other people's computers, one possibility is they don't have Macro's enabled via their Excel Tools...Macros...Security dialog box.

That's a tough thing to control since you can't write code to turn macros on or off on another machine. You can, however, design your workbook to "force" the user to enable Macros. See this site for an explanation:

http://www.vbaexpress.com/kb/getart...

If macros are enabled and the code still doesn't run, I don't know if I can offer any assistance from this far away.



Report •

#6
September 8, 2010 at 09:58:06
Thanks very much, and yes, I did mean worksheet. I followed the instructions on the link you sent to add a sheet about enabling macros, since you were correct in thinking that the reason the macro wasn't working on other computers is that they did not have macros enabled. Thanks again- I really appreciate your help.

Report •


Ask Question