validation list output

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any way to have the (text) output from a drop list on one sheet be placed in consecutive cells in another sheet? I need users to be able to pick five or six consecutive items from a list (one at a time) and have those text items dropped into a series of cells on another sheet. Have tried everything my feeble mind could think of. I am not a skilled VB programmer. Haalp!! I have to have this done by Tuesday.....
 
Hi SusanJane.........

Assuming your Validation Box is in cell A1 of Sheet 1, then on Sheet
(whatever), Cell (whatever), put =Sheet1!A1, and whatever the Validation Box
chooses for Sheet1 Cell A1, will also appear in those other cells where you
put that formula, no matter what sheet......

Vaya con Dios,
Chuck, CABGx3




SusanJane said:
Is there any way to have the (text) output from a drop list on one sheet
Click to expand...
be placed in consecutive cells in another sheet? I need users to be able to
pick five or six consecutive items from a list (one at a time) and have
those text items dropped into a series of cells on another sheet. Have
tried everything my feeble mind could think of. I am not a skilled VB
programmer. Haalp!! I have to have this done by Tuesday.....
 
You can use the Worksheet_Change event to copy the selected value to
another sheet. For example, the following code will copy the value to
the first blank cell, in the same row, on Sheet 2.

To add the code to your workbook, right-click on the sheet tab of the
sheet that has the data validation. Paste in the code where the cursor
is flashing.

'============================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim i As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Application.EnableEvents = False
If Target.Count > 1 Then Exit Sub

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If rngDV Is Nothing Then Exit Sub
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
If Target.Value = "" Then Exit Sub
i = ws.Cells(Target.Row, Columns.Count) _
.End(xlToLeft).Column
If ws.Cells(Target.Row, i).Value = "" Then
i = i
Else
i = i + 1
End If
ws.Cells(Target.Row, i).Value = Target.Value
End If
Application.EnableEvents = True
End Sub
'============================================
 
Hi SusanJane............

Sorry, after reading Debra's suggestion I realized that I didn't read your
original post well enough, I apologize........please ignore my feeble
response.........

Vaya con Dios,
Chuck, CABgx3
 
Debra: Thanks for your quick reply. Here's how it worked: The code places selections from the drop-down in a ROW next to the drop box. Then I linked a series of cells in a COLUMN in my other sheet to these cells. However, seems I can only have one list per sheet, which makes for a lot of sheets. If I had more time I think I'd make this bugger into a database.

Appreciate your assistance!! Especially on a weekend.

SJ
 
How many cells have data validation dropdowns, and where are they located?
Where should the output from each data validation cell go?
 
Back
Top