Scenerio:

I have a workbook with 2 worksheets: Main and Filenmames. On the Main
worksheet I have 2 Command Buttons and 1 ComboBox. Behind one command button
("Refresh Parts List and Create Hyperlinks") is code to search a directory of
jpg files and fill column A in the Filenames worksheet with those filenames.
Then in column B of the same Filenames worksheet, hyperlinks are created for
each of the filenames. So, basically, this button just refreshes the
filenames list and hyperlinks.

The second command button ("List Parts") displays a form with a ListBox of
the part numbers. However, I can populate the ListBox if I use the RowSource
Properies, but I have to manually type in "Filenames!B1:B2588". I dont want
to do it this way because the rows in the Filenames worksheet may be
different as new part jpg files are added.

So, my question is: How do I use VBA to define the dynamic variable
RowSource property?

Thank you for your help.

--
Other programming languages I''ve used are: Cobol, C++, Informix Database,
and Unix.

hi
something like this might work. I tested on a sheet list box which uses
listfillrange.
a forms listbox uses row source so change that in the code. i also used
generic sheet names (just for test) it should work for the forms also. you
may have to tweek it some since i did you a sheet listbox but the basic
sentax is there.

Sub testlist()
Dim lr As Long
Dim r As Range
'find last row on other sheet
lr = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
'set the range
Set r = Range("A2:A" & lr)
'set the listfillrange(rowsource)
Sheet1.ListBox1.ListFillRange = "Sheet2!" & r.Address

End Sub

regards
FSt1

"CAMoore" wrote:

> Scenerio:
>
> I have a workbook with 2 worksheets: Main and Filenmames. On the Main
> worksheet I have 2 Command Buttons and 1 ComboBox. Behind one command button
> ("Refresh Parts List and Create Hyperlinks") is code to search a directory of
> jpg files and fill column A in the Filenames worksheet with those filenames.
> Then in column B of the same Filenames worksheet, hyperlinks are created for
> each of the filenames. So, basically, this button just refreshes the
> filenames list and hyperlinks.
>
> The second command button ("List Parts") displays a form with a ListBox of
> the part numbers. However, I can populate the ListBox if I use the RowSource
> Properies, but I have to manually type in "Filenames!B1:B2588". I dont want
> to do it this way because the rows in the Filenames worksheet may be
> different as new part jpg files are added.
>
> So, my question is: How do I use VBA to define the dynamic variable
> RowSource property?
>
> Thank you for your help.
>
> --
> Other programming languages I''ve used are: Cobol, C++, Informix Database,
> and Unix.

Thank you for your reply. I appologize that I posted ListBox instead of
ComboBox.

Here's the code I have in a module to search a directory and list jpg
filenames and then create hyperlinks. And in my UserForm2 I have a ListBox1
and I can see the list of filenames from the Filenames worksheet if I
hardcode "Filenames!A1:A2588" in the RowSource properties, but I dont want to
hardcode it as the list of jpg file names will grow.

How and where do I put the vba code to get the RowSource?

Public Sub ListFilenames()
'=========================================================
'Initialize variables
'=========================================================
Dim Directory As String
Dim FileName As String
Dim IndexSheet As Worksheet
Dim rw As Long
Dim LastRow As Long
Dim picCnt As Integer

picCnt = 0

'=========================================================
'Activate Filenames worksheet
'=========================================================
ThisWorkbook.Worksheets("Filenames").Activate
Set IndexSheet = ThisWorkbook.ActiveSheet

'=========================================================
'Delete columns A and B
'=========================================================
IndexSheet.Columns("A:B").Delete Shift:=xlToLeft

'=========================================================
'Change the directory below as needed
'=========================================================
Directory = "N:\Parts\"
If Left(Directory, 1) <> "\" Then
Directory = Directory & "\"
End If

FileName = Dir(Directory & "*.jpg")

'=========================================================
'Populate column A with filenames
'=========================================================
rw = 1
Do While FileName <> ""
IndexSheet.Cells(rw, 1).Value = FileName
rw = rw + 1
FileName = Dir
picCnt = picCnt + 1
Loop

'=========================================================
'Find the row number of the last record
'=========================================================
LastRow = Worksheets("Filenames").Range("A65536").End(xlUp).Row

'=========================================================
'Create Hyperlinks and also a Named Range for the RowSource
'=========================================================
With Worksheets("Filenames")
With Range("B1")
.FormulaR1C1 = "=HYPERLINK(""N:\Parts\""&RC[-1])"
.AutoFill Destination:=Range("B1:B" & LastRow)
End With
End With

'=========================================================
'Format worksheet and wrapup
'=========================================================

Columns("A:B").EntireColumn.AutoFit
MsgBox "Number of pics: " & picCnt, vbOKOnly

'=========================================================
'Clean up
'=========================================================
Set IndexSheet = Nothing

End Sub


Thank you again for your reply.

--
Other programming languages I''''ve used are: Cobol, C++, Informix Database,
and Unix.


"FSt1" wrote:

> hi
> something like this might work. I tested on a sheet list box which uses
> listfillrange.
> a forms listbox uses row source so change that in the code. i also used
> generic sheet names (just for test) it should work for the forms also. you
> may have to tweek it some since i did you a sheet listbox but the basic
> sentax is there.
>
> Sub testlist()
> Dim lr As Long
> Dim r As Range
> 'find last row on other sheet
> lr = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
> 'set the range
> Set r = Range("A2:A" & lr)
> 'set the listfillrange(rowsource)
> Sheet1.ListBox1.ListFillRange = "Sheet2!" & r.Address
>
> End Sub
>
> regards
> FSt1
>
> "CAMoore" wrote:
>
> > Scenerio:
> >
> > I have a workbook with 2 worksheets: Main and Filenmames. On the Main
> > worksheet I have 2 Command Buttons and 1 ComboBox. Behind one command button
> > ("Refresh Parts List and Create Hyperlinks") is code to search a directory of
> > jpg files and fill column A in the Filenames worksheet with those filenames.
> > Then in column B of the same Filenames worksheet, hyperlinks are created for
> > each of the filenames. So, basically, this button just refreshes the
> > filenames list and hyperlinks.
> >
> > The second command button ("List Parts") displays a form with a ListBox of
> > the part numbers. However, I can populate the ListBox if I use the RowSource
> > Properies, but I have to manually type in "Filenames!B1:B2588". I dont want
> > to do it this way because the rows in the Filenames worksheet may be
> > different as new part jpg files are added.
> >
> > So, my question is: How do I use VBA to define the dynamic variable
> > RowSource property?
> >
> > Thank you for your help.
> >
> > --
> > Other programming languages I''ve used are: Cobol, C++, Informix Database,
> > and Unix.

hi
i was thinking more along the line of putting the code in the form's
initialazation code.
you would have to run your listfilenames macro first to get the list of
names. but loading a combo box is pretty much the same as loading a list box
just different names. as to loading the combo box, you could use the add item
method but that would require a loop. seting the row source works just as
well and if done at form initialize then it would be reset each time the form
is opened, assuming that you run the listfilenames macro first each time. if
not then it would just reset to the old list.
post your form's initialize code and the name or your combo box.

regards
FSt1

"CAMoore" wrote:

> Thank you for your reply. I appologize that I posted ListBox instead of
> ComboBox.
>
> Here's the code I have in a module to search a directory and list jpg
> filenames and then create hyperlinks. And in my UserForm2 I have a ListBox1
> and I can see the list of filenames from the Filenames worksheet if I
> hardcode "Filenames!A1:A2588" in the RowSource properties, but I dont want to
> hardcode it as the list of jpg file names will grow.
>
> How and where do I put the vba code to get the RowSource?
>
> Public Sub ListFilenames()
> '=========================================================
> 'Initialize variables
> '=========================================================
> Dim Directory As String
> Dim FileName As String
> Dim IndexSheet As Worksheet
> Dim rw As Long
> Dim LastRow As Long
> Dim picCnt As Integer
>
> picCnt = 0
>
> '=========================================================
> 'Activate Filenames worksheet
> '=========================================================
> ThisWorkbook.Worksheets("Filenames").Activate
> Set IndexSheet = ThisWorkbook.ActiveSheet
>
> '=========================================================
> 'Delete columns A and B
> '=========================================================
> IndexSheet.Columns("A:B").Delete Shift:=xlToLeft
>
> '=========================================================
> 'Change the directory below as needed
> '=========================================================
> Directory = "N:\Parts\"
> If Left(Directory, 1) <> "\" Then
> Directory = Directory & "\"
> End If
>
> FileName = Dir(Directory & "*.jpg")
>
> '=========================================================
> 'Populate column A with filenames
> '=========================================================
> rw = 1
> Do While FileName <> ""
> IndexSheet.Cells(rw, 1).Value = FileName
> rw = rw + 1
> FileName = Dir
> picCnt = picCnt + 1
> Loop
>
> '=========================================================
> 'Find the row number of the last record
> '=========================================================
> LastRow = Worksheets("Filenames").Range("A65536").End(xlUp).Row
>
> '=========================================================
> 'Create Hyperlinks and also a Named Range for the RowSource
> '=========================================================
> With Worksheets("Filenames")
> With Range("B1")
> .FormulaR1C1 = "=HYPERLINK(""N:\Parts\""&RC[-1])"
> .AutoFill Destination:=Range("B1:B" & LastRow)
> End With
> End With
>
> '=========================================================
> 'Format worksheet and wrapup
> '=========================================================
>
> Columns("A:B").EntireColumn.AutoFit
> MsgBox "Number of pics: " & picCnt, vbOKOnly
>
> '=========================================================
> 'Clean up
> '=========================================================
> Set IndexSheet = Nothing
>
> End Sub
>
>
> Thank you again for your reply.
>
> --
> Other programming languages I''''ve used are: Cobol, C++, Informix Database,
> and Unix.
>
>
> "FSt1" wrote:
>
> > hi
> > something like this might work. I tested on a sheet list box which uses
> > listfillrange.
> > a forms listbox uses row source so change that in the code. i also used
> > generic sheet names (just for test) it should work for the forms also. you
> > may have to tweek it some since i did you a sheet listbox but the basic
> > sentax is there.
> >
> > Sub testlist()
> > Dim lr As Long
> > Dim r As Range
> > 'find last row on other sheet
> > lr = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
> > 'set the range
> > Set r = Range("A2:A" & lr)
> > 'set the listfillrange(rowsource)
> > Sheet1.ListBox1.ListFillRange = "Sheet2!" & r.Address
> >
> > End Sub
> >
> > regards
> > FSt1
> >
> > "CAMoore" wrote:
> >
> > > Scenerio:
> > >
> > > I have a workbook with 2 worksheets: Main and Filenmames. On the Main
> > > worksheet I have 2 Command Buttons and 1 ComboBox. Behind one command button
> > > ("Refresh Parts List and Create Hyperlinks") is code to search a directory of
> > > jpg files and fill column A in the Filenames worksheet with those filenames.
> > > Then in column B of the same Filenames worksheet, hyperlinks are created for
> > > each of the filenames. So, basically, this button just refreshes the
> > > filenames list and hyperlinks.
> > >
> > > The second command button ("List Parts") displays a form with a ListBox of
> > > the part numbers. However, I can populate the ListBox if I use the RowSource
> > > Properies, but I have to manually type in "Filenames!B1:B2588". I dont want
> > > to do it this way because the rows in the Filenames worksheet may be
> > > different as new part jpg files are added.
> > >
> > > So, my question is: How do I use VBA to define the dynamic variable
> > > RowSource property?
> > >
> > > Thank you for your help.
> > >
> > > --
> > > Other programming languages I''ve used are: Cobol, C++, Informix Database,
> > > and Unix.

The Listbox Initialization code is below. The Listbox name is ListBox1 and
the combo box name is ComboBox1. The ListFillRange on the ComboBox is
Filenames!A1:A2588, and the RowSource on the ListBox1 is also
Filenames!A1:A2588. So, basically, if I can figure out how to dynamically
reference the RowSource range for the ListBox, then it's probably pretty much
the same syntax to reference the FillListRange for the ComboBox is what I'm
guessing. Thanks for your help.

Private Sub UserForm2_Initialize()
ColCnt = ActiveSheet.UsedRange.Columns.Count
Set Rng = ActiveSheet.UsedRange

With ListBox1
.ColumnCount = ColCnt
.RowSource = Rng.Address
cw = ""

For c = 1 To .ColumnCount
cw = cw & Rng.Columns(c).Width & ";"
Next c

.ColumnWidths = cw
.ListIndex = 0
End With
End Sub


--
Other programming languages I''''ve used are: Cobol, C++, Informix Database,
and Unix.


"FSt1" wrote:

> hi
> i was thinking more along the line of putting the code in the form's
> initialazation code.
> you would have to run your listfilenames macro first to get the list of
> names. but loading a combo box is pretty much the same as loading a list box
> just different names. as to loading the combo box, you could use the add item
> method but that would require a loop. seting the row source works just as
> well and if done at form initialize then it would be reset each time the form
> is opened, assuming that you run the listfilenames macro first each time. if
> not then it would just reset to the old list.
> post your form's initialize code and the name or your combo box.
>
> regards
> FSt1
>
> "CAMoore" wrote:
>
> > Thank you for your reply. I appologize that I posted ListBox instead of
> > ComboBox.
> >
> > Here's the code I have in a module to search a directory and list jpg
> > filenames and then create hyperlinks. And in my UserForm2 I have a ListBox1
> > and I can see the list of filenames from the Filenames worksheet if I
> > hardcode "Filenames!A1:A2588" in the RowSource properties, but I dont want to
> > hardcode it as the list of jpg file names will grow.
> >
> > How and where do I put the vba code to get the RowSource?
> >
> > Public Sub ListFilenames()
> > '=========================================================
> > 'Initialize variables
> > '=========================================================
> > Dim Directory As String
> > Dim FileName As String
> > Dim IndexSheet As Worksheet
> > Dim rw As Long
> > Dim LastRow As Long
> > Dim picCnt As Integer
> >
> > picCnt = 0
> >
> > '=========================================================
> > 'Activate Filenames worksheet
> > '=========================================================
> > ThisWorkbook.Worksheets("Filenames").Activate
> > Set IndexSheet = ThisWorkbook.ActiveSheet
> >
> > '=========================================================
> > 'Delete columns A and B
> > '=========================================================
> > IndexSheet.Columns("A:B").Delete Shift:=xlToLeft
> >
> > '=========================================================
> > 'Change the directory below as needed
> > '=========================================================
> > Directory = "N:\Parts\"
> > If Left(Directory, 1) <> "\" Then
> > Directory = Directory & "\"
> > End If
> >
> > FileName = Dir(Directory & "*.jpg")
> >
> > '=========================================================
> > 'Populate column A with filenames
> > '=========================================================
> > rw = 1
> > Do While FileName <> ""
> > IndexSheet.Cells(rw, 1).Value = FileName
> > rw = rw + 1
> > FileName = Dir
> > picCnt = picCnt + 1
> > Loop
> >
> > '=========================================================
> > 'Find the row number of the last record
> > '=========================================================
> > LastRow = Worksheets("Filenames").Range("A65536").End(xlUp).Row
> >
> > '=========================================================
> > 'Create Hyperlinks and also a Named Range for the RowSource
> > '=========================================================
> > With Worksheets("Filenames")
> > With Range("B1")
> > .FormulaR1C1 = "=HYPERLINK(""N:\Parts\""&RC[-1])"
> > .AutoFill Destination:=Range("B1:B" & LastRow)
> > End With
> > End With
> >
> > '=========================================================
> > 'Format worksheet and wrapup
> > '=========================================================
> >
> > Columns("A:B").EntireColumn.AutoFit
> > MsgBox "Number of pics: " & picCnt, vbOKOnly
> >
> > '=========================================================
> > 'Clean up
> > '=========================================================
> > Set IndexSheet = Nothing
> >
> > End Sub
> >
> >
> > Thank you again for your reply.
> >
> > --
> > Other programming languages I''''ve used are: Cobol, C++, Informix Database,
> > and Unix.
> >
> >
> > "FSt1" wrote:
> >
> > > hi
> > > something like this might work. I tested on a sheet list box which uses
> > > listfillrange.
> > > a forms listbox uses row source so change that in the code. i also used
> > > generic sheet names (just for test) it should work for the forms also. you
> > > may have to tweek it some since i did you a sheet listbox but the basic
> > > sentax is there.
> > >
> > > Sub testlist()
> > > Dim lr As Long
> > > Dim r As Range
> > > 'find last row on other sheet
> > > lr = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
> > > 'set the range
> > > Set r = Range("A2:A" & lr)
> > > 'set the listfillrange(rowsource)
> > > Sheet1.ListBox1.ListFillRange = "Sheet2!" & r.Address
> > >
> > > End Sub
> > >
> > > regards
> > > FSt1
> > >
> > > "CAMoore" wrote:
> > >
> > > > Scenerio:
> > > >
> > > > I have a workbook with 2 worksheets: Main and Filenmames. On the Main
> > > > worksheet I have 2 Command Buttons and 1 ComboBox. Behind one command button
> > > > ("Refresh Parts List and Create Hyperlinks") is code to search a directory of
> > > > jpg files and fill column A in the Filenames worksheet with those filenames.
> > > > Then in column B of the same Filenames worksheet, hyperlinks are created for
> > > > each of the filenames. So, basically, this button just refreshes the
> > > > filenames list and hyperlinks.
> > > >
> > > > The second command button ("List Parts") displays a form with a ListBox of
> > > > the part numbers. However, I can populate the ListBox if I use the RowSource
> > > > Properies, but I have to manually type in "Filenames!B1:B2588". I dont want
> > > > to do it this way because the rows in the Filenames worksheet may be
> > > > different as new part jpg files are added.
> > > >
> > > > So, my question is: How do I use VBA to define the dynamic variable
> > > > RowSource property?
> > > >
> > > > Thank you for your help.
> > > >
> > > > --
> > > > Other programming languages I''ve used are: Cobol, C++, Informix Database,
> > > > and Unix.

Additional info:

....and then ultimately what my plans are is for the user to select an
item(s) from the ListBox and it will open up that jpg picture (hyperlink).
The same goes for the ComboBox...Search for an item via the combo box and hit
enter and it open up the jpg picture (hyperlink).

Why do I have a ListBox and a ComboBox that basically is doing the same
thing? I dont know really. I'm just trying to get something to work for the
people in the shop so they can easily type in a part number and look at a
picture on a monitor see if the part they have in their hand is actually the
same part number as what is written on their sheet. Probably either way will
work (a list box or combo box), but I've been told they would like to type in
the part number and have the combo box "autofill" as they type in a number or
so.
--
Other programming languages I''''ve used are: Cobol, C++, Informix Database,
and Unix.


"CAMoore" wrote:

> The Listbox Initialization code is below. The Listbox name is ListBox1 and
> the combo box name is ComboBox1. The ListFillRange on the ComboBox is
> Filenames!A1:A2588, and the RowSource on the ListBox1 is also
> Filenames!A1:A2588. So, basically, if I can figure out how to dynamically
> reference the RowSource range for the ListBox, then it's probably pretty much
> the same syntax to reference the FillListRange for the ComboBox is what I'm
> guessing. Thanks for your help.
>
> Private Sub UserForm2_Initialize()
> ColCnt = ActiveSheet.UsedRange.Columns.Count
> Set Rng = ActiveSheet.UsedRange
>
> With ListBox1
> .ColumnCount = ColCnt
> .RowSource = Rng.Address
> cw = ""
>
> For c = 1 To .ColumnCount
> cw = cw & Rng.Columns(c).Width & ";"
> Next c
>
> .ColumnWidths = cw
> .ListIndex = 0
> End With
> End Sub
>
>
> --
> Other programming languages I''''ve used are: Cobol, C++, Informix Database,
> and Unix.
>
>
> "FSt1" wrote:
>
> > hi
> > i was thinking more along the line of putting the code in the form's
> > initialazation code.
> > you would have to run your listfilenames macro first to get the list of
> > names. but loading a combo box is pretty much the same as loading a list box
> > just different names. as to loading the combo box, you could use the add item
> > method but that would require a loop. seting the row source works just as
> > well and if done at form initialize then it would be reset each time the form
> > is opened, assuming that you run the listfilenames macro first each time. if
> > not then it would just reset to the old list.
> > post your form's initialize code and the name or your combo box.
> >
> > regards
> > FSt1
> >
> > "CAMoore" wrote:
> >
> > > Thank you for your reply. I appologize that I posted ListBox instead of
> > > ComboBox.
> > >
> > > Here's the code I have in a module to search a directory and list jpg
> > > filenames and then create hyperlinks. And in my UserForm2 I have a ListBox1
> > > and I can see the list of filenames from the Filenames worksheet if I
> > > hardcode "Filenames!A1:A2588" in the RowSource properties, but I dont want to
> > > hardcode it as the list of jpg file names will grow.
> > >
> > > How and where do I put the vba code to get the RowSource?
> > >
> > > Public Sub ListFilenames()
> > > '=========================================================
> > > 'Initialize variables
> > > '=========================================================
> > > Dim Directory As String
> > > Dim FileName As String
> > > Dim IndexSheet As Worksheet
> > > Dim rw As Long
> > > Dim LastRow As Long
> > > Dim picCnt As Integer
> > >
> > > picCnt = 0
> > >
> > > '=========================================================
> > > 'Activate Filenames worksheet
> > > '=========================================================
> > > ThisWorkbook.Worksheets("Filenames").Activate
> > > Set IndexSheet = ThisWorkbook.ActiveSheet
> > >
> > > '=========================================================
> > > 'Delete columns A and B
> > > '=========================================================
> > > IndexSheet.Columns("A:B").Delete Shift:=xlToLeft
> > >
> > > '=========================================================
> > > 'Change the directory below as needed
> > > '=========================================================
> > > Directory = "N:\Parts\"
> > > If Left(Directory, 1) <> "\" Then
> > > Directory = Directory & "\"
> > > End If
> > >
> > > FileName = Dir(Directory & "*.jpg")
> > >
> > > '=========================================================
> > > 'Populate column A with filenames
> > > '=========================================================
> > > rw = 1
> > > Do While FileName <> ""
> > > IndexSheet.Cells(rw, 1).Value = FileName
> > > rw = rw + 1
> > > FileName = Dir
> > > picCnt = picCnt + 1
> > > Loop
> > >
> > > '=========================================================
> > > 'Find the row number of the last record
> > > '=========================================================
> > > LastRow = Worksheets("Filenames").Range("A65536").End(xlUp).Row
> > >
> > > '=========================================================
> > > 'Create Hyperlinks and also a Named Range for the RowSource
> > > '=========================================================
> > > With Worksheets("Filenames")
> > > With Range("B1")
> > > .FormulaR1C1 = "=HYPERLINK(""N:\Parts\""&RC[-1])"
> > > .AutoFill Destination:=Range("B1:B" & LastRow)
> > > End With
> > > End With
> > >
> > > '=========================================================
> > > 'Format worksheet and wrapup
> > > '=========================================================
> > >
> > > Columns("A:B").EntireColumn.AutoFit
> > > MsgBox "Number of pics: " & picCnt, vbOKOnly
> > >
> > > '=========================================================
> > > 'Clean up
> > > '=========================================================
> > > Set IndexSheet = Nothing
> > >
> > > End Sub
> > >
> > >
> > > Thank you again for your reply.
> > >
> > > --
> > > Other programming languages I''''ve used are: Cobol, C++, Informix Database,
> > > and Unix.
> > >
> > >
> > > "FSt1" wrote:
> > >
> > > > hi
> > > > something like this might work. I tested on a sheet list box which uses
> > > > listfillrange.
> > > > a forms listbox uses row source so change that in the code. i also used
> > > > generic sheet names (just for test) it should work for the forms also. you
> > > > may have to tweek it some since i did you a sheet listbox but the basic
> > > > sentax is there.
> > > >
> > > > Sub testlist()
> > > > Dim lr As Long
> > > > Dim r As Range
> > > > 'find last row on other sheet
> > > > lr = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
> > > > 'set the range
> > > > Set r = Range("A2:A" & lr)
> > > > 'set the listfillrange(rowsource)
> > > > Sheet1.ListBox1.ListFillRange = "Sheet2!" & r.Address
> > > >
> > > > End Sub
> > > >
> > > > regards
> > > > FSt1
> > > >
> > > > "CAMoore" wrote:
> > > >
> > > > > Scenerio:
> > > > >
> > > > > I have a workbook with 2 worksheets: Main and Filenmames. On the Main
> > > > > worksheet I have 2 Command Buttons and 1 ComboBox. Behind one command button
> > > > > ("Refresh Parts List and Create Hyperlinks") is code to search a directory of
> > > > > jpg files and fill column A in the Filenames worksheet with those filenames.
> > > > > Then in column B of the same Filenames worksheet, hyperlinks are created for
> > > > > each of the filenames. So, basically, this button just refreshes the
> > > > > filenames list and hyperlinks.
> > > > >
> > > > > The second command button ("List Parts") displays a form with a ListBox of
> > > > > the part numbers. However, I can populate the ListBox if I use the RowSource
> > > > > Properies, but I have to manually type in "Filenames!B1:B2588". I dont want
> > > > > to do it this way because the rows in the Filenames worksheet may be
> > > > > different as new part jpg files are added.
> > > > >
> > > > > So, my question is: How do I use VBA to define the dynamic variable
> > > > > RowSource property?
> > > > >
> > > > > Thank you for your help.
> > > > >
> > > > > --
> > > > > Other programming languages I''ve used are: Cobol, C++, Informix Database,
> > > > > and Unix.

hi
accually that's userform2 initialazation code. listboxs and combobox don't
have an initialation evert. forms do.
try this....
Private Sub UserForm2_Initialize()
ColCnt = ActiveSheet.UsedRange.Columns.Count
Set Rng = ActiveSheet.UsedRange
dim lr as long
dim r as range

With ListBox1
.ColumnCount = ColCnt
.RowSource = Rng.Address
cw = ""

For c = 1 To .ColumnCount
cw = cw & Rng.Columns(c).Width & ";"
Next c

.ColumnWidths = cw
.ListIndex = 0
End With

lr = Sheets("FileNames").Cells(Rows.Count, "A").End(xlUp).Row
Set r = Range("A2:A" & lr)
Me.ComboBox1.rowsource = "Filenames!" & r.Address

End Sub

untested. watch for typos
regards
FSt1

Hi Fst1,

I just got done trying this code and it didnt work--I dont see anything in
the list box when I click on the button to open Form2 and display the listbox.

Public Sub UserForm2_Initialize()

Dim cell As Range
Dim Rng As Range
Dim LastRow As Long

LastRow = Worksheets("Filenames").Range("A65536").End(xlUp).Row

With ThisWorkbook.Sheets("Filenames")
Set Rng = .Range("A1:A" & LastRow)
End With

For Each cell In Rng.Cells
Me.ListBox1.AddItem cell.Value
Next cell
End Sub

I will copy and paste in the code you suggest and give that a try.

--
Other programming languages I''''ve used are: Cobol, C++, Informix Database,
and Unix.


"FSt1" wrote:

> hi
> accually that's userform2 initialazation code. listboxs and combobox don't
> have an initialation evert. forms do.
> try this....
> Private Sub UserForm2_Initialize()
> ColCnt = ActiveSheet.UsedRange.Columns.Count
> Set Rng = ActiveSheet.UsedRange
> dim lr as long
> dim r as range
>
> With ListBox1
> .ColumnCount = ColCnt
> .RowSource = Rng.Address
> cw = ""
>
> For c = 1 To .ColumnCount
> cw = cw & Rng.Columns(c).Width & ";"
> Next c
>
> .ColumnWidths = cw
> .ListIndex = 0
> End With
>
> lr = Sheets("FileNames").Cells(Rows.Count, "A").End(xlUp).Row
> Set r = Range("A2:A" & lr)
> Me.ComboBox1.rowsource = "Filenames!" & r.Address
>
> End Sub
>
> untested. watch for typos
> regards
> FSt1

I've tried two different ways in UserForm2_Initialization and neither one
worked

***********
** Try #1: **
***********
Public Sub UserForm2_Initialize()
ColCnt = ActiveSheet.UsedRange.Columns.Count
Set rng = ActiveSheet.UsedRange
Dim lr As Long
Dim r As Range

With ListBox1
.ColumnCount = ColCnt
.RowSource = rng.Address

cw = ""

For c = 1 To .ColumnCount
cw = cw & rng.Columns(c).Width & ";"
Next c

.ColumnWidths = cw
.ListIndex = 0
End With

lr = Sheets("FileNames").Cells(Rows.Count, "A").End(xlUp).Row
Set r = Range("A2:A" & lr)
Me.ListBox1.RowSource = "Filenames!" & r.Address

End Sub

***********
** Try #2: **
***********
Public Sub UserForm2_Initialize()
Dim rng As Range
Dim LastRow As Long

ThisWorkbook.Worksheets("Main").Activate

LastRow = Worksheets("Filenames").Range("A65536").End(xlUp).Row

With ThisWorkbook.Sheets("Filenames")
Set rng = .Range("A1:A" & LastRow)
End With

With ListBox1
For Each cell In rng.Cells
AddItem cell.Value
Next cell
End With

End Sub


--
Other programming languages I''''ve used are: Cobol, C++, Informix Database,
and Unix.


"FSt1" wrote:

> hi
> accually that's userform2 initialazation code. listboxs and combobox don't
> have an initialation evert. forms do.
> try this....
> Private Sub UserForm2_Initialize()
> ColCnt = ActiveSheet.UsedRange.Columns.Count
> Set Rng = ActiveSheet.UsedRange
> dim lr as long
> dim r as range
>
> With ListBox1
> .ColumnCount = ColCnt
> .RowSource = Rng.Address
> cw = ""
>
> For c = 1 To .ColumnCount
> cw = cw & Rng.Columns(c).Width & ";"
> Next c
>
> .ColumnWidths = cw
> .ListIndex = 0
> End With
>
> lr = Sheets("FileNames").Cells(Rows.Count, "A").End(xlUp).Row
> Set r = Range("A2:A" & lr)
> Me.ComboBox1.rowsource = "Filenames!" & r.Address
>
> End Sub
>
> untested. watch for typos
> regards
> FSt1

hi
now i'm confused. you said you made a mistake about posted list box vs.
combo box so now i'm not sure which one we are working with. no matter. the
two work very similar in code.
you first try should have worked. I didn't test second try. not sure at this
point why it didn't work so i drug out my play form, put a list box(?) on it,
put my code in it and it loaded right up at initialization. here is my code
from my play form.
Private Sub UserForm_Initialize()
TextBox2.Value = Date
Dim lr As Long
Dim r As Range

lr = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set r = Range("A2:A" & lr)
Me.ListBox1.RowSource = "Sheet2!" & r.Address
CommandButton1.SetFocus
End Sub

again, i use generic sheet names but the list box loaded with the list i
have on sheet 2. i doubled the list length and it loaded the new list. i
opened the form from sheet 1.
I did assume that your list was in column A. if not change the code in this
line to the column your list is in....
lr = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row

works in xp.
regards
FSt1

"CAMoore" wrote:

> I've tried two different ways in UserForm2_Initialization and neither one
> worked
>
> ***********
> ** Try #1: **
> ***********
> Public Sub UserForm2_Initialize()
> ColCnt = ActiveSheet.UsedRange.Columns.Count
> Set rng = ActiveSheet.UsedRange
> Dim lr As Long
> Dim r As Range
>
> With ListBox1
> .ColumnCount = ColCnt
> .RowSource = rng.Address
>
> cw = ""
>
> For c = 1 To .ColumnCount
> cw = cw & rng.Columns(c).Width & ";"
> Next c
>
> .ColumnWidths = cw
> .ListIndex = 0
> End With
>
> lr = Sheets("FileNames").Cells(Rows.Count, "A").End(xlUp).Row
> Set r = Range("A2:A" & lr)
> Me.ListBox1.RowSource = "Filenames!" & r.Address
>
> End Sub
>
> ***********
> ** Try #2: **
> ***********
> Public Sub UserForm2_Initialize()
> Dim rng As Range
> Dim LastRow As Long
>
> ThisWorkbook.Worksheets("Main").Activate
>
> LastRow = Worksheets("Filenames").Range("A65536").End(xlUp).Row
>
> With ThisWorkbook.Sheets("Filenames")
> Set rng = .Range("A1:A" & LastRow)
> End With
>
> With ListBox1
> For Each cell In rng.Cells
> AddItem cell.Value
> Next cell
> End With
>
> End Sub
>
>
> --
> Other programming languages I''''ve used are: Cobol, C++, Informix Database,
> and Unix.
>
>
> "FSt1" wrote:
>
> > hi
> > accually that's userform2 initialazation code. listboxs and combobox don't
> > have an initialation evert. forms do.
> > try this....
> > Private Sub UserForm2_Initialize()
> > ColCnt = ActiveSheet.UsedRange.Columns.Count
> > Set Rng = ActiveSheet.UsedRange
> > dim lr as long
> > dim r as range
> >
> > With ListBox1
> > .ColumnCount = ColCnt
> > .RowSource = Rng.Address
> > cw = ""
> >
> > For c = 1 To .ColumnCount
> > cw = cw & Rng.Columns(c).Width & ";"
> > Next c
> >
> > .ColumnWidths = cw
> > .ListIndex = 0
> > End With
> >
> > lr = Sheets("FileNames").Cells(Rows.Count, "A").End(xlUp).Row
> > Set r = Range("A2:A" & lr)
> > Me.ComboBox1.rowsource = "Filenames!" & r.Address
> >
> > End Sub
> >
> > untested. watch for typos
> > regards
> > FSt1