Repost of listbox losing linked cell

[Dkline]

I apologize if this is a duplicate of a message from an hour ago. Ithas not
been posted yet and I am desperate.

I have a listbox with a linked cell of AK5. The listbox is not in a
userform. It is taken from the Control Toobox bar and placed on the worksheet
itself.

I have named it lbFinCarrier. In the Formula box it shows
"=EMBED("Forms.ListBox.1","")"

For some reason, it loses its connection to the linked cell AK5. I can
scroll through the listbox and the value in the lnked cell does not change.

Sub FillListBoxCarriers()
Dim wb As Workbook
Dim ws As Worksheet
Dim strListRange As String
Dim OLEObj As OLEObject

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Inputs")
ws.Select
strListRange = "AK6:AK39"

Set OLEObj = ws.OLEObjects("lbFinCarrier")
With OLEObj
.LinkedCell = ws.Range("AK5")
.ListFillRange =
ActiveSheet.Range(strListRange).Address(external:=True)
End With

Set OLEObj = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub

 

[Norman Jones]

Hi D,

Try replacing:

.LinkedCell = ws.Range("AK5")

with

.LinkedCell = "AK5"

 

[Dave Peterson]

I'd use that same syntax as you used in the .listfillrange

..LinkedCell = ws.Range("AK5").Address(external:=True)

Just in case Inputs wasn't the activesheet.

 

[Dkline]

Thanks for the reply. Now the problem is the function doesn't work. This
isn't a coding problem anymore.

 

[Dkline]

Thanks for the reply. Now the problem is the function doesn't work. This
isn't a coding problem anymore.

 

[Norman Jones]

Hi Dave,

'----------------
I'd use that same syntax as you used in the .listfillrange

..LinkedCell = ws.Range("AK5").Address(external:=True)

Just in case Inputs wasn't the activesheet.
'----------------

I certainly agre that it is better to be explicit.

However, given Dkline's

The activesheet should not be a problem.

 

[Dave Peterson]

I don't understand what this means.

Thanks for the reply. Now the problem is the function doesn't work. This
isn't a coding problem anymore.

 

[Norman Jones]

Hi D,

----------------
Now the problem is the function doesn't work. This
isn't a coding problem anymore.
'----------------

The code works for me, although I would adopt Dave's
suggestion

In what way does the code not work and, if it does not
work, why is this not a code related problem?

 

[Dkline]

Gentlemen,

The code now works perfectly. The new problem is when I or the other users
actually use the list box, make a selection, the value does not change in the
linked cell - AK5.

So the worksheet function doesn't function as intended.

 

[Norman Jones]

Hi D,

'------------
The code now works perfectly. The new problem is when I or the other users
actually use the list box, make a selection, the value does not change in
the
linked cell - AK5.

So the worksheet function doesn't function as intended
'------------

In my test workbook, the code works as expected and
the linked cell's value changes in response to a change in
the ListBox selection.

 

[Dkline]

I figured it out. It is the item highlighted in blue that is the selected
value. Simply scrolling through the selection to another item does not change
the selection.

Excuse me while I slap myself upside the head.

Thank for your help.

 

[Dave Peterson]

Maybe...

just in case Inputs wasn't the activesheet -- or the code was located behind a
different worksheet so that that unqualified range wouldn't refer to ws.

<vbg>