Can the Linked Cell property of an ActiveX control be dynamically changed?

[drmweaver2]

Generally, the question is: Can the Linked Cell property of an ActiveX control be dynamically changed?

Specifically, what I am trying to do relates to this:
I have 2 Worksheets, the first, Sheet1, with a table A3:D1000 where Col 2 is filled with a bunch of different "First Names" but some cells might be ""/blank. On the second Worksheet, Sheet2.Col 2 cells (up to 12,000 of them) are always filled with " First Names", that is the "valid" Names for Sheet1.Col 2 cells.

What I would like to be able to do is:

1. click a blank cell on Sheet1.Col 2, and have that become the Linked Cell
2. pop up a Userform with a combobox or listbox auto-filled with the Sheet2.Col 2 values
3. be able to scroll through & select a Name
4. have that selected Name populate the "empty cell"
5. end/return to 1

(meaning, I want to be able to select another “blank/linked cell” on Worksheet Sheet1
and have that now be the Linked Cell)

I’ve tried various things but gotten nowhere with this.

Any thoughts or help would be appreciated.

 

[Coding4Fun]

I created a workbook and added 2 sheets (Sheet1, Sheet2). I then created a UserForm in the VBA Editor window named "UserForm1".

In Sheet2 Column B - This is where the list is populated to be used for the combobox on the userform.

In the code for the UserForm1:

'Code for the UserForm1 - Named "UserForm1"


Public myChoice As String


' Button on the UserForm1  - Button Named "btnOk"
Private Sub btnOk_Click()


ActiveCell.Value = myChoice ' Update the active Cell with the selected value from the combobox
UserForm1.Hide ' Hide the form


End Sub


' ComboBox on UserForm1 - Named "ComboBox1"
Private Sub ComboBox1_AfterUpdate()


myChoice = ComboBox1.SelText ' Get the selected value from the combobox


End Sub


' ComboBox on UserForm1 - Named "ComboBox1"
Private Sub ComboBox1_DropButt*******() ' This line keeps messing up coming out as ****** should be the option for Drop Button Click


Dim myLastRow As String


myLastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row ' Check Last Row of Sheet2
ComboBox1.List = Sheets("Sheet2").Range("A1:A" & myLastRow).Value ' Fill combo box with list from Sheet2


End Sub

Code in the worksheet - go into the code for Sheet1 and insert this:

'Code in the WorkSheet


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Column = 1 Then ' This is the trigger currently set so if you click a blank cell in Column A then it will open your UserForm


    If Target.Value = "" Then
    
    UserForm1.Show
    
    End If


End If


End Sub

 

[drmweaver2]

ActiveCell.Value = myChoice

Of course! Doh. ...:smacks forehead hard.....3x
..........................:lightbulb finally comes on....

The rest of the solution becomes obvious once that concept is illuminated.

Thanks.

 

[Coding4Fun]

Sorry there was a typo in the previous code

' ComboBox on UserForm1 - Named "ComboBox1"
Private Sub ComboBox1_DropButt*******() ' This line keeps messing up coming out as ****** should be the option for Drop Button Click


Dim myLastRow As String


myLastRow = Sheets("Sheet2").Cells(Rows.Count, "[COLOR=#ff0000]B[/COLOR]").End(xlUp).Row ' Check Last Row of Sheet2
ComboBox1.List = Sheets("Sheet2").Range("[COLOR=#ff0000]B[/COLOR]1:[COLOR=#ff0000]B[/COLOR]" & myLastRow).Value ' Fill combo box with list from Sheet2


End Sub

 

[Coding4Fun]

Haha had I known you just needed one line

Glad it worked out for you

 

[drmweaver2]

Actually, I appreciate ALL of the lines.
The one that I "quoted" above - well, that is the thing that broke me out of my black hole of "how the hell do I do this?" Your code is much better than what I am currently capable of writing, but eventually I would have "gotten" there after your replacing the rock I was using to smack my forehead with a more "generous" dead fish.

Your post was definitely NOT wasted on me. My appreciation cannot be accurately calculated.

Thanks again.