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 a 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 a 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 a 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 a 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 a 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 a 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 a 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 a 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 a 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 a 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 a 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 a 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 a 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