| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple choices from pulldown... how to apply to several pulldown
Hi,
Follow up: I first added to original question, but it's really a new question. After using the code Debra was kind enough to share, I need to apply the action (choose from a pull down menu and place each entry into a new row) to 4 or 5 specific columns. I tried the following but it didn't work.(see row surrounded by *****) Thanks for any help. Here's the code: (the line with all the stars is what I tried). ------ Public Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long ********lCol = 7 or 14 or 18 or 12 'column with data validation cell**** (I also tried using "And") 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 If Target.Column = lCol Then If Target.Value = "" Then GoTo exitHandler If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1 End If Cells(lRow, lCol + 1).Value = Target.Value End If End If exitHandler: Application.EnableEvents = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
multiple choices from pulldown... how to apply to several pulldown
change
If Target.Column = lCol Then to If Target.Column = 7 or Target.column = 12 or _ Target.column = 14 or Target.column = 18 then Icol = Target.column -- Regards, Tom Ogilvy "DianeN" wrote: Hi, Follow up: I first added to original question, but it's really a new question. After using the code Debra was kind enough to share, I need to apply the action (choose from a pull down menu and place each entry into a new row) to 4 or 5 specific columns. I tried the following but it didn't work.(see row surrounded by *****) Thanks for any help. Here's the code: (the line with all the stars is what I tried). ------ Public Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long ********lCol = 7 or 14 or 18 or 12 'column with data validation cell**** (I also tried using "And") 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 If Target.Column = lCol Then If Target.Value = "" Then GoTo exitHandler If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1 End If Cells(lRow, lCol + 1).Value = Target.Value End If End If exitHandler: Application.EnableEvents = True End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
multiple choices from pulldown... how to apply to several pull
Hi Tom, thanks so much. I guess when I pasted the code, the "1col" and
"1row" came out looking like "I" or something... or are you saying I need to change them from "1" to "I" ? Sorry to be dense. When I copied and pasted your code below, it didn't give me an error message, it just didn't work on any of the columns. Thanks "Tom Ogilvy" wrote: change If Target.Column = lCol Then to If Target.Column = 7 or Target.column = 12 or _ Target.column = 14 or Target.column = 18 then Icol = Target.column -- Regards, Tom Ogilvy "DianeN" wrote: Hi, Follow up: I first added to original question, but it's really a new question. After using the code Debra was kind enough to share, I need to apply the action (choose from a pull down menu and place each entry into a new row) to 4 or 5 specific columns. I tried the following but it didn't work.(see row surrounded by *****) Thanks for any help. Here's the code: (the line with all the stars is what I tried). ------ Public Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long ********lCol = 7 or 14 or 18 or 12 'column with data validation cell**** (I also tried using "And") 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 If Target.Column = lCol Then If Target.Value = "" Then GoTo exitHandler If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1 End If Cells(lRow, lCol + 1).Value = Target.Value End If End If exitHandler: Application.EnableEvents = True End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
multiple choices from pulldown... how to apply to several pull
Tom, nevermind. I changed the "1" to "i" on both row and column and it works!!
Thanks so much! "DianeN" wrote: Hi Tom, thanks so much. I guess when I pasted the code, the "1col" and "1row" came out looking like "I" or something... or are you saying I need to change them from "1" to "I" ? Sorry to be dense. When I copied and pasted your code below, it didn't give me an error message, it just didn't work on any of the columns. Thanks "Tom Ogilvy" wrote: change If Target.Column = lCol Then to If Target.Column = 7 or Target.column = 12 or _ Target.column = 14 or Target.column = 18 then Icol = Target.column -- Regards, Tom Ogilvy "DianeN" wrote: Hi, Follow up: I first added to original question, but it's really a new question. After using the code Debra was kind enough to share, I need to apply the action (choose from a pull down menu and place each entry into a new row) to 4 or 5 specific columns. I tried the following but it didn't work.(see row surrounded by *****) Thanks for any help. Here's the code: (the line with all the stars is what I tried). ------ Public Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long ********lCol = 7 or 14 or 18 or 12 'column with data validation cell**** (I also tried using "And") 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 If Target.Column = lCol Then If Target.Value = "" Then GoTo exitHandler If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1 End If Cells(lRow, lCol + 1).Value = Target.Value End If End If exitHandler: Application.EnableEvents = True End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
multiple choices from pulldown... how to apply to several pull
I wasn't suggesting that you change 1 to I, as you are correct, the original
looked like I's to me. But if you have changed them and it works, then no reason to change back that I can think of. -- Regards, Tom Ogilvy "DianeN" wrote: Tom, nevermind. I changed the "1" to "i" on both row and column and it works!! Thanks so much! "DianeN" wrote: Hi Tom, thanks so much. I guess when I pasted the code, the "1col" and "1row" came out looking like "I" or something... or are you saying I need to change them from "1" to "I" ? Sorry to be dense. When I copied and pasted your code below, it didn't give me an error message, it just didn't work on any of the columns. Thanks "Tom Ogilvy" wrote: change If Target.Column = lCol Then to If Target.Column = 7 or Target.column = 12 or _ Target.column = 14 or Target.column = 18 then Icol = Target.column -- Regards, Tom Ogilvy "DianeN" wrote: Hi, Follow up: I first added to original question, but it's really a new question. After using the code Debra was kind enough to share, I need to apply the action (choose from a pull down menu and place each entry into a new row) to 4 or 5 specific columns. I tried the following but it didn't work.(see row surrounded by *****) Thanks for any help. Here's the code: (the line with all the stars is what I tried). ------ Public Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long ********lCol = 7 or 14 or 18 or 12 'column with data validation cell**** (I also tried using "And") 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 If Target.Column = lCol Then If Target.Value = "" Then GoTo exitHandler If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1 End If Cells(lRow, lCol + 1).Value = Target.Value End If End If exitHandler: Application.EnableEvents = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Autofilter Pulldown | Excel Programming | |||
| Using value from pulldown box in macro | Excel Programming | |||
| pulldown boxes | Excel Programming | |||
| pulldown boxes | Excel Programming | |||
| pulldown boxes | Excel Programming | |||
Linear Mode
