Combo Box from data in another tab
 

Is it possible to create a combo box from data that's in another tab?
I have a combo box that's from data in hidden columns. But now I need to move
all that hidden data to another tab in the same file. But when I go into
properties and go to the ListFillRange option, it won't let me enter another
tab name.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1

I have called data from another tab with a combo box by doing the
following:

First - name the range of the data on the other tab. This can be done
with the shortcut on the toolbar (left of the formula bar) or by going
to Insert - Name - Define
next - in the combobox where it asks for the input range, enter
"=namedrange"

Hope this helps.


--
barrfly

Excel User - Energy markets
------------------------------------------------------------------------
barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141
View this thread: http://www.excelforum.com/showthread...hreadid=392336

How did you type it in?

sheet1!a1:a10
should work
or
'sheet 1'!a1:a10



"Patty via OfficeKB.com" wrote:

Is it possible to create a combo box from data that's in another tab?
I have a combo box that's from data in hidden columns. But now I need to move
all that hidden data to another tab in the same file. But when I go into
properties and go to the ListFillRange option, it won't let me enter another
tab name.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1

--

Dave Peterson

thanks it worked. I was typing in a "=" before.
Do you know how to automate the LinkedCell option. Do I need to go into each
box individually to change the LinkedCell?


Patty wrote:
Is it possible to create a combo box from data that's in another tab?
I have a combo box that's from data in hidden columns. But now I need to move
all that hidden data to another tab in the same file. But when I go into
properties and go to the ListFillRange option, it won't let me enter another
tab name.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1

For a manual effort, it's one by one.

You could use a macro:

Option Explicit
Sub testme()
Dim OLEObj As OLEObject
Dim wks As Worksheet
Dim DestCell As Range

Set wks = Worksheets("sheet1")

Set DestCell = Worksheets("sheet2").Range("a1")

For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.ListBox Then
OLEObj.LinkedCell = DestCell.Address(external:=True)
DestCell.Offset(0, 1).Value = OLEObj.Name
Set DestCell = DestCell.Offset(1, 0)
End If
Next OLEObj

End Sub


"Patty via OfficeKB.com" wrote:

thanks it worked. I was typing in a "=" before.
Do you know how to automate the LinkedCell option. Do I need to go into each
box individually to change the LinkedCell?

Patty wrote:
Is it possible to create a combo box from data that's in another tab?
I have a combo box that's from data in hidden columns. But now I need to move
all that hidden data to another tab in the same file. But when I go into
properties and go to the ListFillRange option, it won't let me enter another
tab name.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1

--

Dave Peterson

Combo Box from data in another tab
 

"barrfly" wrote:


I have called data from another tab with a combo box by doing the
following:

First - name the range of the data on the other tab. This can be done
with the shortcut on the toolbar (left of the formula bar) or by going
to Insert - Name - Define
next - in the combobox where it asks for the input range, enter
"=namedrange"

Hope this helps.


--
barrfly

Excel User - Energy markets
------------------------------------------------------------------------
barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141
View this thread: http://www.excelforum.com/showthread...hreadid=392336



If you have a combo box in a userform how do you reference a named range
from worksheet? I wanted to do this and then make something that lets me
select option "Other", ask user what should be in there and then append it to
the list.