userform help

[JasonLeVan]

Is there a way to make say data in rows 1-10 show up in a userform box

 

[DauntingGecko]

What kind of "box"?

A drop down menu? A grid?

 

[JasonLeVan]

i woud say a grid, i want all the rows and column of the worksheet to appear in a userform,, long story why

What kind of "box"?

A drop down menu? A grid?

 

[BrianExcel]

How may columns are you looking at? Is all the data continuous so there are no spaces?

 

[JasonLeVan]

it varies but anywhere from 90 to maybe 120 rows with about 8 columns.

How may columns are you looking at? Is all the data continuous so there are no spaces?

 

[BrianExcel]

On your userform, insert a listbox.

Within the sheet where your data is held, name the range by selecting the entire range of data and then in the upper-left creating a unique range name such as "ListboxRange" or whatever you can remember.

Within the design mode (in the VBA Window (Alt + F11)), select the listbox, right click and select properties. Within that properties there will be a "ListFillRange" field. In that field enter the same name "ListboxRange" name you had set earlier.

Once you have done this, set the columncount to 8, or however many columns you have and you are set. You can also set column widths if desired for each column by entering them in the field. 1"; 1"; 2"; etc.

 

[JasonLeVan]

Great idea i can not find that listfillrange box to save my life, ill keep looking

On your userform, insert a listbox.

Within the sheet where your data is held, name the range by selecting the entire range of data and then in the upper-left creating a unique range name such as "ListboxRange" or whatever you can remember.

Within the design mode (in the VBA Window (Alt + F11)), select the listbox, right click and select properties. Within that properties there will be a "ListFillRange" field. In that field enter the same name "ListboxRange" name you had set earlier.

Once you have done this, set the columncount to 8, or however many columns you have and you are set. You can also set column widths if desired for each column by entering them in the field. 1"; 1"; 2"; etc.

 

[Norie]

If it's a listbox on a userform try this - put it in the userform's module.

Option Explicit
Private Sub Userform_Initialize()
Dim rng As Range
Dim LastRow As Long
Dim LastCol As Long
Dim NoCols As Long
Dim colWidth As Long
Dim AllCols As String
 
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
 
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
 
    Set rng = Range("A1").Resize(LastRow, LastCol)
 
    NoCols = rng.Columns.Count
 
    colWidth = (ListBox1.Width - 10) / NoCols
 
    AllCols = String(NoCols, "X")
 
    AllCols = Replace(AllCols, "X", colWidth & ";")
 
    ListBox1.ColumnCount = NoCols
 
    ListBox1.ColumnWidths = AllCols
 
    ListBox1.List = rng.Value
 
End Sub

It assumes the data starts in A1 and should populate the listbox, set the no of columns, set the column widths etc programatically.

You probably don't need all that code eg if you know how may columns there are you can set that in design mode.