Archiving user selection from Drop Down (forms - combo box)

[Meg]

Is there a way when a user makes a selection from a drop down created by a
Forms> combo box that this selection be archived into another excel sheet?

 

[Dave Peterson]

You could assign a macro to the dropdown (from the forms toolbar).

Option Explicit
Sub testme()
Dim NextCell As Range
Dim myDD As DropDown

Set myDD = ActiveSheet.DropDowns(Application.Caller)

With ThisWorkbook.Worksheets("Sheet1")
Set NextCell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(NextCell) Then
'use this one
Else
'else come down one
Set NextCell = NextCell.Offset(1, 0)
End If
End With

If myDD.Value = 0 Then
'do nothing, the dropdown is empty
Else
With NextCell
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
NextCell.Offset(0, 1).Value = Application.UserName
With myDD
NextCell.Offset(0, 2).Value = .List(.ListIndex)
End With
End If
End Sub



If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)