Private Sub ComboBox1_AfterUpdate()
Dim iRow As Long
Dim iRtn As Integer
iRtn = MsgBox("Keep this?", vbYesNo)
If iRtn = 6 Then
iRow = Sheets("Sheet1").Range("D65536").End(xlUp).Row
Sheets("Sheet1").Range("D" & iRow + 1) = ComboBox1.Value
UserForm_Activate
End If
End Sub

Private Sub UserForm_Activate()
Dim rng As Range
Dim iRow As Long
iRow = Sheets("Sheet1").Range("D65536").End(xlUp).Row
Set rng = Sheets("Sheet1").Range("D2:D" & iRow)
ComboBox1.RowSource = rng.Worksheet.Name & "!" & rng.Address
End Sub

Hth,
Merjet