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

Hi D,

Try replacing:

.LinkedCell = ws.Range("AK5")

with

.LinkedCell = "AK5"

---
Regards,
Norman


"Dkline" wrote in message
...
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

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 wrote:

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

--

Dave Peterson

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

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson

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


"Norman Jones" wrote:

Hi D,

Try replacing:

.LinkedCell = ws.Range("AK5")

with

.LinkedCell = "AK5"

---
Regards,
Norman


"Dkline" wrote in message
...
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

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

ws.Select

The activesheet should not be a problem.


---
Regards,
Norman

I don't understand what this means.

Dkline wrote:

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

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson

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?


---
Regards,
Norman

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" wrote:

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?


---
Regards,
Norman

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.


---
Regards,
Norman

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.

"Norman Jones" wrote:

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.


---
Regards,
Norman

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

Norman Jones wrote:

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

ws.Select

The activesheet should not be a problem.

---
Regards,
Norman

--

Dave Peterson