Listbox ColumnHeads

[Dave Baranas]

Hi All

I am working from the other side trying to get back to Excel where I
have lists for my Listboxes.

I have the code below working to do this

Sub PopulateLstBoxFromExcel()

Dim MfrRange As Range, MfrVal As Variant
Dim ObjXL As Object
Dim ObjActiveWkb As Object

Set ObjXL = GetObject(, "Excel.Application")
Set ObjActiveWkb = ObjXL.Application.ActiveWorkbook

With ObjActiveWkb

'Mfr Order
Set MfrRange = .Worksheets("List").Range("P2101")

MfrVal = MfrRange.Value
Mn.CboMfrOrd.List = MfrVal

End With

Set ObjActiveWkb = Nothing: Set ObjXL = Nothing

End Sub

I was not able to use the rowsource property like this below when I
was inside Excel

Mn.LstWlInd.RowSource = Mn.LstHsNm.RowSource = "List!Y2:Z102"

I am wondering if there is a way to use the Rowsource property on a
listbox when you are coming back into excel to get a list.

If not then I wonder if there is a way to get the variant to somehow
convert back to a string so I can feed it into the rowsource.

If not then I wonder if there is some black magic voodoo stuff to get
column heads turned on with having to use the rowsource property.

Any help in this direction would go a long way to conquer these evil
beasts for everyone

Regards,

Dave Baranas

 

[Tom Ogilvy]

Mn.CboMfrOrd.Rowsource = MfrRange.Address(External:=True)
Mn.CboMfrOrd.ColumnHeads = True



This should work as well:
Mn.LstHsNm.RowSource = "List!Y2:Z102"
Mn.LstWlInd.RowSource = Mn.LstHsNm.RowSource

 

[Dave Baranas]

Hi Tom

I stripped down what I had for the last post and I messed up and
distilled it down to a combo...but anyway I think they should work the
same way here is what I have got and its so close but stil giving me
Runtime error 380 "Invalid Property value"

Where the error is happening the tool tip is displaying

LstRange.Address(External:=True)="[myfile.xls]Calcwin!$A$2:$A$AI$101"

So it looks like I am still missing something, I wonder if it knows
the path to myfile.xls

The last example you gave I tried myself before I started this thread
with no luck.

If you could help me on this one I would really appreciate it.

Thanks,

Dave

Sub SetList()

Dim ObjXL As Object
Dim ObjActiveWkb As Object

Set ObjXL = GetObject(, "Excel.Application")
Set ObjActiveWkb = ObjXL.Application.ActiveWorkbook

'Set Main List
With ObjActiveWkb

'Form Initialize will pass through here
Set LstRange = .Worksheets("CalcWin").Range("A2:AI101")

LstVal = LstRange.Value
Mn.LstMain.ColumnCount = LstRange.Columns.count
Mn.LstMain.RowSource = LstRange.Address(External:=True) ERROR
Mn.LstMain.ColumnHeads = True

End With

Set ObjActiveWkb = Nothing: Set ObjXL = Nothing

End Sub

 

[Tom Ogilvy]

I ran this from Excel with a userform named Mn

Sub SetList()

Dim ObjXL As Object
Dim ObjActiveWkb As Object
Dim lstRange As Excel.Range
Dim lstValue As Variant
Set ObjXL = GetObject(, "Excel.Application")
Set ObjActiveWkb = ObjXL.Application.ActiveWorkbook

'Set Main List
With ObjActiveWkb
'Form Initialize will pass through here
Set lstRange = .Worksheets("CalcWin").Range("A2:AI101")

LstVal = lstRange.Value ' not needed
Mn.lstMain.ColumnCount = lstRange.Columns.Count
Mn.lstMain.RowSource = lstRange.Address(External:=True)
Mn.lstMain.ColumnHeads = True
End With
Mn.Show
Set ObjActiveWkb = Nothing: Set ObjXL = Nothing
End Sub


worked fine for me. However, you talk about being outside Excel without
being specific. According to this article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;164343&Product=wrd

Word, Powerpoint, and Outlook do not have a rowsource property for the
listbox and don't support columnheads. I suspect you will have to go back
to using the array approach and do without the column heads.

--
Regards,
Tom Ogilvy

Hi Tom

I stripped down what I had for the last post and I messed up and
distilled it down to a combo...but anyway I think they should work the
same way here is what I have got and its so close but stil giving me
Runtime error 380 "Invalid Property value"

Where the error is happening the tool tip is displaying

LstRange.Address(External:=True)="[myfile.xls]Calcwin!$A$2:$A$AI$101"

So it looks like I am still missing something, I wonder if it knows
the path to myfile.xls

The last example you gave I tried myself before I started this thread
with no luck.

If you could help me on this one I would really appreciate it.

Thanks,

Dave

Sub SetList()

Dim ObjXL As Object
Dim ObjActiveWkb As Object

Set ObjXL = GetObject(, "Excel.Application")
Set ObjActiveWkb = ObjXL.Application.ActiveWorkbook

'Set Main List
With ObjActiveWkb

'Form Initialize will pass through here
Set LstRange = .Worksheets("CalcWin").Range("A2:AI101")

LstVal = LstRange.Value
Mn.LstMain.ColumnCount = LstRange.Columns.count
Mn.LstMain.RowSource = LstRange.Address(External:=True) ERROR
Mn.LstMain.ColumnHeads = True

End With

Set ObjActiveWkb = Nothing: Set ObjXL = Nothing

End Sub

Mn.CboMfrOrd.Rowsource = MfrRange.Address(External:=True)
Mn.CboMfrOrd.ColumnHeads = True



This should work as well:
Mn.LstHsNm.RowSource = "List!Y2:Z102"
Mn.LstWlInd.RowSource = Mn.LstHsNm.RowSource

 

[Dave Baranas]

Hi Tom,

Hi Tom

I am now over in Autocad trying to hook back up with Excel. If Autocad
supports VBA just like Microsoft Excel supports it and both objects
expose the Rowsource property, then I think I am now driving a 1973
Ford Torino that has the Rowsourse light on the dashboard but does not
come on until you buy the LTD version. In other words There are a lot
of idiot lights in VBA that do not really work the way they are
supposed to.

Regards Dave

 

[Dave Baranas]

Hi Tom,

I take some of that back, just getting really frustrated with these
listboxes, and it seems there are volumes of listbox questions in this
newsgroup alone. I have probably read all of them by now and just to
try to do something simple opens a world of pain.

Flamintg myself in advance

Dave

 

[Tom Ogilvy]

Word looks like it has a rowsource property if you look in the object
browser, but it apparently doesn't. I doubt autocad does either.