ComboBox RowSource Dynamic

[CAMoore]

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.

 

[FSt1]

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]

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.

 

[FSt1]

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

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.

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]

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.

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

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.

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

:

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.

 

[CAMoore]

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.

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.

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

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.


:

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

:

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.

 

[FSt1]

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

 

[CAMoore]

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.

 

[CAMoore]

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

 

[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]

Sorry. I realize I went back and forth with ComboBox and ListBox. What I'm
trying to get working with RowSource right now is the ListBox. Here is the
code you last posted and I edited as per my Sheet and UserForm name, and it
didnt work. I'm using Excel 2007. Maybe I could email my spreadsheet to
you???

Private Sub UserForm2_Initialize()
'TextBox2.Value = Date
Dim lr As Long
Dim r As Range

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

 

[FSt1]

hi
not sure but 2007 may be a factor. i'm using 2003. so sending me the file
wouldn't work since i can't open it. sorry. also the code does work in 2003
and i do know they have changed some things in 2007 in order to add some of
the new features but i am not sure if this is the cause of this code not
working on your end. i am reading up on 2007 so i do know some but my direct
experience is nil and any new code would be beyond me at this point since i
can't run or test in 2007.

perhaps a mvp could step in now and bail both of us out. if not sorry for
any bum steers. repost and state that you are using 2007 up front.

Regards
FSt1

 

[CAMoore]

Thank you. I appreciate all your time and effort in trying to help me.
Maybe I'll give it a rest and try another approach and/or a light bulb will
go off in my head tomorrow. That's the way it usually works. I was really
happy to get the code to work that lists all the file names and create
hyperlinks to them though. I'm just trying to make it user friendly now so
users will have an interface to work with.

Thanks again.

 

[CAMoore]

Oh, I just thought....I'm using Excel 2007, but Ive this workbook saved and
working with it as an .xls version, not as .xlsx (2007) version. So, in that
case you would be able to open it.

 

[FSt1]

hi
well if it is an xls file, send it to me, i'll see what i can do.
(e-mail address removed)

regards
FSt1