Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox ColumnHeads
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("P2:P101") 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox ColumnHeads
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 -- Regards, Tom Ogilvy Dave Baranas wrote in message ... 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("P2:P101") 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox ColumnHeads
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 On Sun, 7 Dec 2003 16:25:38 -0500, "Tom Ogilvy" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox ColumnHeads
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...43&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 Dave Baranas wrote in message ... 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 On Sun, 7 Dec 2003 16:25:38 -0500, "Tom Ogilvy" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox ColumnHeads
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 On Sun, 7 Dec 2003 19:45:30 -0500, "Tom Ogilvy" wrote: 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...43&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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox ColumnHeads
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 On Mon, 08 Dec 2003 17:57:49 GMT, Dave Baranas wrote: 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 On Sun, 7 Dec 2003 19:45:30 -0500, "Tom Ogilvy" wrote: 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...43&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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox ColumnHeads
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. -- Regards, Tom Ogilvy "Dave Baranas" wrote in message ... 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 On Sun, 7 Dec 2003 19:45:30 -0500, "Tom Ogilvy" wrote: 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...43&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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
ListBox ColumnHeads | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |