P
phancey
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 procedure:
******************
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
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 procedure:
******************
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