Listbox RowSource Dynamic from Worksheet 1

[IknowMe]

I have a form with a Listbox that is being populated by a worksheet range. The columns are static at 9 (A-I on the worksheet) but the rows need to adjust based on populated rows that will change. I can get it to work with a fixed range but can't come up with a dynamic solution despite much searching. I'm using Excel 2003 SP2.

Any Help appreciated.

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[SkipVought]

Hi,

You can use one of these techniques -- faq68-1331

You can use the Worksheet_Change event on the sheet the list resides to rename the list using the CurrentRegion property.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[IknowMe]

Skip, your Faq is phenominal I forget the faqs are there at times in my search and I should have found it.

From your Faq

4. If this is a TABLE, for instance, one to use as PivotTable Source Data, same assumptions AND nothing in ROW 1 to the right of the table...

CODE
=OFFSET
(
INDIRECT("Sheet1!$A$1"),
0,
0,
CountA(Sheet1!$A:$A),
CountA(Sheet1!$1:$1)
)

This is exactly what I want as I am using headers. However i cannot get it to fire on the onchange event of the worksheet after multiple attempts to VBA it.


I also got this working and it fires on the onchange event but I cannot get it to start at row 2 so my headers are correct in the listbox.

Next VBA--

1. Write this procedure in a general module

CODE
Sub ResizeTable()
Application.DisplayAlerts = False
With Cells(1, 1).CurrentRegion
.CreateNames _
Top:=True, _
Left:=False, _
Bottom:=False, _
Right:=False
.Name = "Database"
End With
Application.DisplayAlerts = True
End Sub

Can you suggest how I combine the two so to speak? It would be IMO the missing piece in your FAQ as it covers all the other possabilities.

And have a star for getting me this far.





[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[IknowMe]

I ended up using

    MyRowSource = "A2:I" & Str(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row)
    MyRowSource = Replace(MyRowSource, " ", "")
    f_IceMain.l_Inventory.RowSource = MyRowSource

but am still curious about more elegant solutions.

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[SkipVought]

That OFFSET function dynamically names the TABLE range in the Insert > Name > Define... dialog.

You can also use the OFFSET function to name individual ranges, just as the Insert > Name > Create - Create names in top row

"...i cannot get it to fire on the onchange event of the worksheet ..."

Is your Tools > Options > Calculation Tab: Calculation--AUTOMATIC, as COUNTA must calculate.

"...but I cannot get it to start at row 2 so my headers are correct in the listbox."

Sub ResizeTable()
    Application.DisplayAlerts = False
'[b]assumes headings start in ROW 1 COLUMN 1[/b]
    With Cells([b][red]1, 1[/red][/b]).CurrentRegion
'[b]Top:=True uses names in headings for range names[/b]
       .CreateNames _
           Top:=True, _
           Left:=False, _
           Bottom:=False, _
           Right:=False
'[b]This names the entire table range[/b]
       .Name = "Database"
     End With
    Application.DisplayAlerts = True
End Sub

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[IknowMe]

I have a bit more time now to elaborate on the problems I was having for anyone attempting to follow this thread behind me.

When I used the Worksheet Function from Skips FAQ it worked great when the worksheet was first opened. But I had two problems with this:

1. It wouldn't recalculate when a change was made to the sheet so I ended up with a named range that updated on open but reflected no additional changes to the worksheet.

2. I have the column headers turned on in my listbox and since the range included the first row of the worksheet (I'm using the first row for my headers) my listbox looked something like this, literally.

Column A Column B
MyHeader MyHeader
Data Data

What I wanted.

MyHeader MyHeader
Data Data

I've since learned that the listbox recognizes the data right above the named range as your header and if your range actually includes your headers they end up being treated as data in the listbox and gives you the default Column A ex.ex. headers.

Here's the defenition I used.

=OFFSET
(
  INDIRECT("Sheet1!$A$1"),
  0, 
  0,
  CountA(Sheet1!$A:$A),
  CountA(Sheet1!$1:$1)
)

I then moved to the VBA solution in Skips FAQ and It had the same header problem as above but it does update via the change event of the worksheet.

Sub ResizeTable()
    Application.DisplayAlerts = False
    With Cells(1, 1).CurrentRegion
       .CreateNames _
           Top:=True, _
           Left:=False, _
           Bottom:=False, _
           Right:=False
       .Name = "Database"
     End With
    Application.DisplayAlerts = True
End Sub

I ended up using VBA idea but modified as such.

   MyRange = "A2:I" & Str(IceWorkSheet.Cells.SpecialCells(xlCellTypeLastCell).Row)
MyRange = Replace(MyRange, " ", "")
f_IceMain.l_Inventory.RowSource = MyRange

Which does what I needed but I am going to pursue the additional information Skip provided.

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[SkipVought]

Heading are not considered data. You REALLY do not need to have a header. It is an identification convenience. It is a good practice, but headings are NOT required.

If you have a headiing in row 1, for instance, you can NAME the Data Range (data in rows 2 and following) using the OFFSET Function as follows...
[tt]
=OFFSET
(
INDIRECT("Sheet1!$A$1"),
1,
0,
CountA(Sheet1!$A:$A)-1,
CountA(Sheet1!$1:$1)
)
[/tt]
Arg1: The ANCHOR range - no change
Arg2: The ROW offset - your data begins ONE ROW OFFSET from the ANCHOR rnage
Arg3: The COLUMN offset - no change
Arg4: The ROW RANGE COUNT - is the count is all in that column (A:A) MINUS 1 to account for the Heading, which is not data.
Arg5: The COLUMN RANGE COUNT - no change

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[IknowMe]

This should probably be in a different thread but here's the crux of my listbox header.

You can turn on listbox column headers BUT they can only be populated when you use the RowSource property and a named range. They will populate with the information in the row preceeding your range. If your range starts at A1 then you will get default headers. I have header info in my first row and was including it in my range. As Skip says it's not data and I didn't want to display it as such. But I did want to use it to set the Headers in the multi column listbox on my form.

So if you have headers in A1:I1 and your data in A2:I9 you would use.

RowSource: A2:I9

This will default your listbox headers to match A1:I1

As far as I can tell it is impossible to set the Headers either manually or through code.


Back to my range question, with the additional info from Skip my data range now readjusts whenever the worksheet is changed and all is well.

Is your Tools > Options > Calculation Tab: Calculation--AUTOMATIC, as COUNTA must calculate.

The worksheet function IMO is the best way to go to name my data range and the form listbox headers take care of themselves based on the row above data range.

Thanks again Skip, if it will let me give you another star you'll have two.



[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]