How do I assign "ControlSource" = variable "adr"

[hitch_hiker]

In a combo box in a userform, I want the selection to go into the next available cell in a column , which is the variable adr. So I'm expecting something like

with frmProductList
ControlSource = adr
or maybe
ControlSource.value = adr
or maybe ???

Private Sub cmdAddPart_Click()
Dim adr As String
Dim ws As Worksheet
Set ws = Worksheets("ORDER INPUT")
    ' find blank cell
    'Dim adr As String, adrR1C1 As String
   
  Range("c10").Select
     Do
         If IsEmpty(ActiveCell) = False Then
         ActiveCell.Offset(1, 0).Select
         End If
     Loop Until IsEmpty(ActiveCell) = True
 
    ' load addr with blank cell reference
  adr = ActiveCell.Address(ReferenceStyle:=xlA1)
 'With frmProductList
   ' ControlSource = adr
'End With

 

[AlphaFrog]

Try something like this...

Private Sub cmdAddPart_Click()

    Worksheets("ORDER INPUT").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = ComboBox1.Value

End Sub

 

[hitch_hiker]

thanks Alphfrog,

I do appreciate your reply and your solution would work well in most applications, however, I have only a limited range c10:c26, with other data below that in column "c" which is why the empty cell routine I have works, I will try to rework the .value = combobox.value concept, it might work. I really wanted to assign the variable "adr" to the ControlSource of the combobox.

 

[AlphaFrog]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdAddPart_Click()
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Worksheets("ORDER INPUT").Range("C10:C28")
        [color=darkblue]If[/color] cell.Value = "" [color=darkblue]Then[/color] cell.Value = ComboBox1.Value: [color=darkblue]Exit[/color] [color=darkblue]For[/color]
        [color=green]'If cell.value = "" Then ComboBox1.ControlSource = cell: Exit For[/color]
    [color=darkblue]Next[/color] cell
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[hitch_hiker]

thanks Alphfrog,

Thanks for your help, I think this is the last thing I need before I put it in the 'production' worksheet. so you've tidied it up nicely for me. I won't know until later tonight when I can test it at home. really appreciate your input