Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop-Down Macro
I found the following macro during some internet scrounging. If you have a
drop-down data-validation list in Column C, it builds an accumulative list in a cell from items selected from the list. (IE, if my validation list is of Jeeves, Wooster, and Tuppy, each time I select Jeeves from the drop down, it will add it to the cell with a comma: "Jeeves, Jeeves, Jeeves, Jeeves, Jeevesr": Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 3 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub Unfortunately, I really know almost (in fact, maybe exactly) nothing about VBA. I DO know that I have a workbook of 10-odd sheets, each one of which has three to four columns which have drop-down validations which I need to apply this macro to. I know how to change the column the macro refers to (changing "3" in the line "If Target.Column = 3 if" to the correct column number), but I don't know how to make it so thiat macro works for multiple columns in each sheet (say, Columns 4, 8, 10, and 18) all the time. What is the best way to do this? I know this part of the newsgroup is for users who know what they're doing, but any help you can provide to a total neophyte would be much appreciated! Desperate in Columbus |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop-Down Macro
Michael,
Try the version below. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim oldVal As String Dim newVal As String Dim myType As Variant If Target.Count 1 Then Exit Sub 'Make it only work on columns 4, 8, 10, and 18 If Target.Column < 4 And _ Target.Column < 8 And _ Target.Column < 10 And _ Target.Column < 18 Then Exit Sub On Error GoTo exitHandler myType = Target.Validation.Type Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If exitHandler: Application.EnableEvents = True End Sub "Michael Link" wrote in message ... I found the following macro during some internet scrounging. If you have a drop-down data-validation list in Column C, it builds an accumulative list in a cell from items selected from the list. (IE, if my validation list is of Jeeves, Wooster, and Tuppy, each time I select Jeeves from the drop down, it will add it to the cell with a comma: "Jeeves, Jeeves, Jeeves, Jeeves, Jeevesr": Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 3 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub Unfortunately, I really know almost (in fact, maybe exactly) nothing about VBA. I DO know that I have a workbook of 10-odd sheets, each one of which has three to four columns which have drop-down validations which I need to apply this macro to. I know how to change the column the macro refers to (changing "3" in the line "If Target.Column = 3 if" to the correct column number), but I don't know how to make it so thiat macro works for multiple columns in each sheet (say, Columns 4, 8, 10, and 18) all the time. What is the best way to do this? I know this part of the newsgroup is for users who know what they're doing, but any help you can provide to a total neophyte would be much appreciated! Desperate in Columbus |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop-Down Macro
Thanks for your suggestion! It looks like that will work! interestingly, I
received other suggestions from some other folks not on the newsgroup, and they all seem to work. Definitely a "more than one way to skin a cat" sort of moment. "Bernie Deitrick" wrote: Michael, Try the version below. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim oldVal As String Dim newVal As String Dim myType As Variant If Target.Count 1 Then Exit Sub 'Make it only work on columns 4, 8, 10, and 18 If Target.Column < 4 And _ Target.Column < 8 And _ Target.Column < 10 And _ Target.Column < 18 Then Exit Sub On Error GoTo exitHandler myType = Target.Validation.Type Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If exitHandler: Application.EnableEvents = True End Sub "Michael Link" wrote in message ... I found the following macro during some internet scrounging. If you have a drop-down data-validation list in Column C, it builds an accumulative list in a cell from items selected from the list. (IE, if my validation list is of Jeeves, Wooster, and Tuppy, each time I select Jeeves from the drop down, it will add it to the cell with a comma: "Jeeves, Jeeves, Jeeves, Jeeves, Jeevesr": Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 3 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub Unfortunately, I really know almost (in fact, maybe exactly) nothing about VBA. I DO know that I have a workbook of 10-odd sheets, each one of which has three to four columns which have drop-down validations which I need to apply this macro to. I know how to change the column the macro refers to (changing "3" in the line "If Target.Column = 3 if" to the correct column number), but I don't know how to make it so thiat macro works for multiple columns in each sheet (say, Columns 4, 8, 10, and 18) all the time. What is the best way to do this? I know this part of the newsgroup is for users who know what they're doing, but any help you can provide to a total neophyte would be much appreciated! Desperate in Columbus |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro with Drop Down Box | Excel Discussion (Misc queries) | |||
Run macro from drop list | Excel Discussion (Misc queries) | |||
Drop Down Box & Macro | Excel Discussion (Misc queries) | |||
Macro Drop Down Box | Excel Programming | |||
Macro that changes Drop Down Box | Excel Programming |