| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull Down Menu - Code not working
I'm trying to modify the code below so that 1) when using the pull down menu,
it populates the cell below it , and 2) it only applies to one column. Right now, when you use the pull down menu it just populates that same cell, not the one below it. Any help is greatly appreciated. Thanks, Laura Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long lCol = Target.Column 'column with data validation cell 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 If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Select Case Target.Column Case 7 If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1 End If Cells(lRow, lCol).Value = Target.Value End Select End If exitHandler: Application.EnableEvents = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pull Down Menu - Code not working
Laura,
I tried to code what your words were describing, not what your code was seeming to do... Private Sub Worksheet_Change(ByVal Target As Range) Dim myVal As Variant If Target.Value = "" Then Exit Sub If Target.Column < 7 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, _ Cells.SpecialCells(xlCellTypeAllValidation)) _ Is Nothing Then Exit Sub myVal = Target.Value Application.EnableEvents = False Application.Undo Target.Offset(1, 0).Value = myVal Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Laura L" wrote in message ... I'm trying to modify the code below so that 1) when using the pull down menu, it populates the cell below it , and 2) it only applies to one column. Right now, when you use the pull down menu it just populates that same cell, not the one below it. Any help is greatly appreciated. Thanks, Laura Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long lCol = Target.Column 'column with data validation cell 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 If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Select Case Target.Column Case 7 If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1 End If Cells(lRow, lCol).Value = Target.Value End Select End If exitHandler: Application.EnableEvents = True End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pull Down Menu - Code not working
Hi, this does help but I also need it to populate each row beneath the pull
down menu, that's why I thought I needed the "+1" string. How do I add that to it? Thanks again! "Bernie Deitrick" wrote: Laura, I tried to code what your words were describing, not what your code was seeming to do... Private Sub Worksheet_Change(ByVal Target As Range) Dim myVal As Variant If Target.Value = "" Then Exit Sub If Target.Column < 7 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, _ Cells.SpecialCells(xlCellTypeAllValidation)) _ Is Nothing Then Exit Sub myVal = Target.Value Application.EnableEvents = False Application.Undo Target.Offset(1, 0).Value = myVal Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Laura L" wrote in message ... I'm trying to modify the code below so that 1) when using the pull down menu, it populates the cell below it , and 2) it only applies to one column. Right now, when you use the pull down menu it just populates that same cell, not the one below it. Any help is greatly appreciated. Thanks, Laura Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long lCol = Target.Column 'column with data validation cell 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 If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Select Case Target.Column Case 7 If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1 End If Cells(lRow, lCol).Value = Target.Value End Select End If exitHandler: Application.EnableEvents = True End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pull Down Menu - Code not working
Laura,
The line Target.Offset(1, 0).Value = myVal does just that - it fills in the row below the cell with the pulldown. (Though it would be just as easy to fill in that cell directly...) So, I'm not sure what you want besides that... HTH, Bernie MS Excel MVP "Laura L" wrote in message ... Hi, this does help but I also need it to populate each row beneath the pull down menu, that's why I thought I needed the "+1" string. How do I add that to it? Thanks again! "Bernie Deitrick" wrote: Laura, I tried to code what your words were describing, not what your code was seeming to do... Private Sub Worksheet_Change(ByVal Target As Range) Dim myVal As Variant If Target.Value = "" Then Exit Sub If Target.Column < 7 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, _ Cells.SpecialCells(xlCellTypeAllValidation)) _ Is Nothing Then Exit Sub myVal = Target.Value Application.EnableEvents = False Application.Undo Target.Offset(1, 0).Value = myVal Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Laura L" wrote in message ... I'm trying to modify the code below so that 1) when using the pull down menu, it populates the cell below it , and 2) it only applies to one column. Right now, when you use the pull down menu it just populates that same cell, not the one below it. Any help is greatly appreciated. Thanks, Laura Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long lCol = Target.Column 'column with data validation cell 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 If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Select Case Target.Column Case 7 If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1 End If Cells(lRow, lCol).Value = Target.Value End Select End If exitHandler: Application.EnableEvents = True End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pull Down Menu - Code not working
Hi Bernie,
How do you get it to populate cells beneath the pull down? Not just the cell below it? (one at a time) Laura "Bernie Deitrick" wrote: Laura, I tried to code what your words were describing, not what your code was seeming to do... Private Sub Worksheet_Change(ByVal Target As Range) Dim myVal As Variant If Target.Value = "" Then Exit Sub If Target.Column < 7 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, _ Cells.SpecialCells(xlCellTypeAllValidation)) _ Is Nothing Then Exit Sub myVal = Target.Value Application.EnableEvents = False Application.Undo Target.Offset(1, 0).Value = myVal Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Laura L" wrote in message ... I'm trying to modify the code below so that 1) when using the pull down menu, it populates the cell below it , and 2) it only applies to one column. Right now, when you use the pull down menu it just populates that same cell, not the one below it. Any help is greatly appreciated. Thanks, Laura Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long lCol = Target.Column 'column with data validation cell 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 If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Select Case Target.Column Case 7 If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1 End If Cells(lRow, lCol).Value = Target.Value End Select End If exitHandler: Application.EnableEvents = True End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pull Down Menu - Code not working
Hi Bernie,
If the pull down menu is in G3, I need to be able to use the pull down for all the cells below G3, not just G4.... is it possible? "Bernie Deitrick" wrote: Laura, The line Target.Offset(1, 0).Value = myVal does just that - it fills in the row below the cell with the pulldown. (Though it would be just as easy to fill in that cell directly...) So, I'm not sure what you want besides that... HTH, Bernie MS Excel MVP "Laura L" wrote in message ... Hi, this does help but I also need it to populate each row beneath the pull down menu, that's why I thought I needed the "+1" string. How do I add that to it? Thanks again! "Bernie Deitrick" wrote: Laura, I tried to code what your words were describing, not what your code was seeming to do... Private Sub Worksheet_Change(ByVal Target As Range) Dim myVal As Variant If Target.Value = "" Then Exit Sub If Target.Column < 7 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, _ Cells.SpecialCells(xlCellTypeAllValidation)) _ Is Nothing Then Exit Sub myVal = Target.Value Application.EnableEvents = False Application.Undo Target.Offset(1, 0).Value = myVal Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Laura L" wrote in message ... I'm trying to modify the code below so that 1) when using the pull down menu, it populates the cell below it , and 2) it only applies to one column. Right now, when you use the pull down menu it just populates that same cell, not the one below it. Any help is greatly appreciated. Thanks, Laura Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long lCol = Target.Column 'column with data validation cell 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 If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Select Case Target.Column Case 7 If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1 End If Cells(lRow, lCol).Value = Target.Value End Select End If exitHandler: Application.EnableEvents = True End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pull Down Menu - Code not working
Laura,
Try Cells(Rows.Count, 7).End(xlUp)(2).Value = myVal to fill in the first empty cell below G3 (G4, then G5, then G6, etc.) HTH, Bernie MS Excel MVP "Laura L" wrote in message ... Hi Bernie, If the pull down menu is in G3, I need to be able to use the pull down for all the cells below G3, not just G4.... is it possible? "Bernie Deitrick" wrote: Laura, The line Target.Offset(1, 0).Value = myVal does just that - it fills in the row below the cell with the pulldown. (Though it would be just as easy to fill in that cell directly...) So, I'm not sure what you want besides that... HTH, Bernie MS Excel MVP "Laura L" wrote in message ... Hi, this does help but I also need it to populate each row beneath the pull down menu, that's why I thought I needed the "+1" string. How do I add that to it? Thanks again! "Bernie Deitrick" wrote: Laura, I tried to code what your words were describing, not what your code was seeming to do... Private Sub Worksheet_Change(ByVal Target As Range) Dim myVal As Variant If Target.Value = "" Then Exit Sub If Target.Column < 7 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, _ Cells.SpecialCells(xlCellTypeAllValidation)) _ Is Nothing Then Exit Sub myVal = Target.Value Application.EnableEvents = False Application.Undo Target.Offset(1, 0).Value = myVal Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Laura L" wrote in message ... I'm trying to modify the code below so that 1) when using the pull down menu, it populates the cell below it , and 2) it only applies to one column. Right now, when you use the pull down menu it just populates that same cell, not the one below it. Any help is greatly appreciated. Thanks, Laura Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long lCol = Target.Column 'column with data validation cell 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 If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Select Case Target.Column Case 7 If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1 End If Cells(lRow, lCol).Value = Target.Value End Select End If exitHandler: Application.EnableEvents = True End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pull Down Menu - Code not working
THANK YOU!!!!!!
"Bernie Deitrick" wrote: Laura, Try Cells(Rows.Count, 7).End(xlUp)(2).Value = myVal to fill in the first empty cell below G3 (G4, then G5, then G6, etc.) HTH, Bernie MS Excel MVP "Laura L" wrote in message ... Hi Bernie, If the pull down menu is in G3, I need to be able to use the pull down for all the cells below G3, not just G4.... is it possible? "Bernie Deitrick" wrote: Laura, The line Target.Offset(1, 0).Value = myVal does just that - it fills in the row below the cell with the pulldown. (Though it would be just as easy to fill in that cell directly...) So, I'm not sure what you want besides that... HTH, Bernie MS Excel MVP "Laura L" wrote in message ... Hi, this does help but I also need it to populate each row beneath the pull down menu, that's why I thought I needed the "+1" string. How do I add that to it? Thanks again! "Bernie Deitrick" wrote: Laura, I tried to code what your words were describing, not what your code was seeming to do... Private Sub Worksheet_Change(ByVal Target As Range) Dim myVal As Variant If Target.Value = "" Then Exit Sub If Target.Column < 7 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, _ Cells.SpecialCells(xlCellTypeAllValidation)) _ Is Nothing Then Exit Sub myVal = Target.Value Application.EnableEvents = False Application.Undo Target.Offset(1, 0).Value = myVal Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Laura L" wrote in message ... I'm trying to modify the code below so that 1) when using the pull down menu, it populates the cell below it , and 2) it only applies to one column. Right now, when you use the pull down menu it just populates that same cell, not the one below it. Any help is greatly appreciated. Thanks, Laura Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long lCol = Target.Column 'column with data validation cell 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 If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Select Case Target.Column Case 7 If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1 End If Cells(lRow, lCol).Value = Target.Value End Select End If exitHandler: Application.EnableEvents = True End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pull Down Menu - Code not working
THANK YOU!!!!!! Awesome!
"Bernie Deitrick" wrote: Laura, Try Cells(Rows.Count, 7).End(xlUp)(2).Value = myVal to fill in the first empty cell below G3 (G4, then G5, then G6, etc.) HTH, Bernie MS Excel MVP "Laura L" wrote in message ... Hi Bernie, If the pull down menu is in G3, I need to be able to use the pull down for all the cells below G3, not just G4.... is it possible? "Bernie Deitrick" wrote: Laura, The line Target.Offset(1, 0).Value = myVal does just that - it fills in the row below the cell with the pulldown. (Though it would be just as easy to fill in that cell directly...) So, I'm not sure what you want besides that... HTH, Bernie MS Excel MVP "Laura L" wrote in message ... Hi, this does help but I also need it to populate each row beneath the pull down menu, that's why I thought I needed the "+1" string. How do I add that to it? Thanks again! "Bernie Deitrick" wrote: Laura, I tried to code what your words were describing, not what your code was seeming to do... Private Sub Worksheet_Change(ByVal Target As Range) Dim myVal As Variant If Target.Value = "" Then Exit Sub If Target.Column < 7 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, _ Cells.SpecialCells(xlCellTypeAllValidation)) _ Is Nothing Then Exit Sub myVal = Target.Value Application.EnableEvents = False Application.Undo Target.Offset(1, 0).Value = myVal Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Laura L" wrote in message ... I'm trying to modify the code below so that 1) when using the pull down menu, it populates the cell below it , and 2) it only applies to one column. Right now, when you use the pull down menu it just populates that same cell, not the one below it. Any help is greatly appreciated. Thanks, Laura Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long lCol = Target.Column 'column with data validation cell 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 If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Select Case Target.Column Case 7 If Target.Offset(0, 1).Value = "" Then lRow = Target.Row Else lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1 End If Cells(lRow, lCol).Value = Target.Value End Select End If exitHandler: Application.EnableEvents = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help w/ modifying code... pull down menu | Excel Programming | |||
| Excel pull down menu | Excel Worksheet Functions | |||
| Pull Down Menu Bar | Excel Discussion (Misc queries) | |||
| Select pull down menu | Excel Programming | |||
| pull down menu | Excel Programming | |||
![Laura L[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
