return text to a selected cell from a user form

rgr

New Member
Joined
Dec 27, 2005
Messages
3
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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

rgr

New Member
Joined
Dec 27, 2005
Messages
3
My apologies for the cross-posting. I'm new at this and forgot that x-posting is against the rules. Won't happen again. :oops:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
rgr

Like Zack said over on VBAExpress more information is needed.

What ranges do you want this to work with?
 

rgr

New Member
Joined
Dec 27, 2005
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,086
Members
412,310
Latest member
mark884
Top