return text to a selected cell from a user form

[rgr]

Not sure really how to begin:

I have a populated Combo box in a user form, works just fine.
I would like to be able to bring up the userform by doubleclicking on any cell in 4 non contiguous columns which are each 36 rows ( if not possible, no problem, I use a keyboard sequence to bring it up now). In any case, once the cell is selected, I need to be able to transfer the selected text, or in some cases, text which is typed into the list, to that 1 particular cell.

Also, am I correct in understanding that a user can type a new entry into the combo box and that the entry will be automatically added to the existing list?

Any one kind enough to help a clueless newbie would be greatly appreciated.

 

[Zack Barresse]

Cross-posted: http://vbaexpress.com/forum/showthread.php?t=6529

 

[rgr]

My apologies for the cross-posting. I'm new at this and forgot that x-posting is against the rules. Won't happen again.

 

[Norie]

rgr

Like Zack said over on VBAExpress more information is needed.

What ranges do you want this to work with?

 

[rgr]

Thanks for responding. The exact ranges are; B5:B36, D5:D36, H5:H36, and J5:J36. The list is populated from a named range called "Members" which is about 250 names and growing. I have used data validation in the past which is nice with the droplist and picking a name, and also adding a new name is done right in the droplist, but, I like the userform combobox because of the autofill feature, which is becoming more important as the list grows.

I can probably figure out how to code adding a new member to the dynamic range in a userform, but I can't seem to figure out how to simply select 1 cell and grab a member's name from a droplist in a userform.

This is what I use with the data validation method, but need a way to integrate it with a userform:


Option Explicit

Private Sub Worksheet_Change(ByVal target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Members")
If target.column = 2 Or target.column = 4 Or target.column = 8 Or target.column = 10 And target.Row > 4 Then
If Application.WorksheetFunction.CountIf(ws.Range("NameList"), target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub