hi,

I am using Excel 2003. My job is to import data and then use that data
in a UserForm application with the ability to export the changed data
at the end. The data will be supplied in a file and consists of
multiple tables.

My plan is to use XML Map to import the data into a number of XML
Lists (though I haven't used this before). I have already found some
restrictions like "list of lists" etc but hope to be able to work
around these.

Anyway, I have started to just see if I can work with these lists.For
example I have 2 comboboxes on my form. The contents of one is
dependent on the selected value of the other. So on the change event
of the first I run the following procedu
******************
Dim ws As Worksheet
Dim listObj As ListObject
Dim ddl As ComboBox

Set ws = Worksheets("Static Data")
Set ddl = UserForm1.ddlElement
Set listObj = ws.ListObjects("lstSubElement")

If ddl.Value < "" Then
'listObj.Range.AutoFilter 3, ddl.Value
Set ddl = UserForm1.ddlSubElement
With listObj.DataBodyRange
ddl.RowSource = .SpecialCells(xlCellTypeVisible).Address
End With
Else
Set ddl = UserForm1.ddlSubElement
ddl.RowSource = ""
End If
********************

I have commented out the autofilter part to test it. If there is no
filter, it works fine, I get the proper values. If a filter has been
set (either programmatically OR manually), it doesn't work. It looks
like it might have the right number of records but the values I am
interested in are not displayed. The RowSource shows "$A$5:$H$5,$A$7:$H
$7" when I look at it in the debugger. My BoundColumn is 1. My
ColumnCount is 5 with ColumnWidths = "0 pt;0 pt;0 pt;0 pt;141.75 pt"

The only obvious difference between when it works and when it doesn't
is that RowSource when it does work = "$A$5:$H$7"

Why would that be? If it is a limitation of Excel 2003 or something -
is there a workaround? Is there a more sensible way for me to be doing
this?

thanks
Phil

If you use the rowsource property, you are limited to one contigous
rectangular area of cells, not several separate areas as you show in the
example that doesn't work.

the alternative is to use AddItem, adding your entries one at a time and not
binding to the cells themselves.

--
Regards,
Tom Ogilvy


"phancey" wrote:

hi,

I am using Excel 2003. My job is to import data and then use that data
in a UserForm application with the ability to export the changed data
at the end. The data will be supplied in a file and consists of
multiple tables.

My plan is to use XML Map to import the data into a number of XML
Lists (though I haven't used this before). I have already found some
restrictions like "list of lists" etc but hope to be able to work
around these.

Anyway, I have started to just see if I can work with these lists.For
example I have 2 comboboxes on my form. The contents of one is
dependent on the selected value of the other. So on the change event
of the first I run the following procedu
******************
Dim ws As Worksheet
Dim listObj As ListObject
Dim ddl As ComboBox

Set ws = Worksheets("Static Data")
Set ddl = UserForm1.ddlElement
Set listObj = ws.ListObjects("lstSubElement")

If ddl.Value < "" Then
'listObj.Range.AutoFilter 3, ddl.Value
Set ddl = UserForm1.ddlSubElement
With listObj.DataBodyRange
ddl.RowSource = .SpecialCells(xlCellTypeVisible).Address
End With
Else
Set ddl = UserForm1.ddlSubElement
ddl.RowSource = ""
End If
********************

I have commented out the autofilter part to test it. If there is no
filter, it works fine, I get the proper values. If a filter has been
set (either programmatically OR manually), it doesn't work. It looks
like it might have the right number of records but the values I am
interested in are not displayed. The RowSource shows "$A$5:$H$5,$A$7:$H
$7" when I look at it in the debugger. My BoundColumn is 1. My
ColumnCount is 5 with ColumnWidths = "0 pt;0 pt;0 pt;0 pt;141.75 pt"

The only obvious difference between when it works and when it doesn't
is that RowSource when it does work = "$A$5:$H$7"

Why would that be? If it is a limitation of Excel 2003 or something -
is there a workaround? Is there a more sensible way for me to be doing
this?

thanks
Phil