VBA Module Calling Userform and Populating

[Theo71]

I am trying to set up a form to display data from various worksheets and to allow data entry to update across worksheets.

The following code resides under module1 but it fails to populate data into the listbox when I run it. I am new to the activeworkbook.names.add function, I am probably not using correctly, can some one help.


Sub present_form()

MsgBox " Hi form process "
Sheet6.Activate
lastrow = Range("A600").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="listCourses", RefersTo:="=Sheet6!$A$2:$A$" & lastrow
MsgBox "lastrow " & lastrow
frmRound.lstCourses.Value = listCourses

frmRound.lblCourse.Caption = "Courses Listed in " & Sheet6.Name

frmRound.Show
End Sub

 

[RoryA]

You need the rowsource:

frmRound.lstCourses.rowsource = "listCourses"

or list:

frmRound.lstCourses.list = [listCourses]

 

[Theo71]

Thanks but no joy.
I get run time errors referring to array index and invalid property value.

When I code the listCourses without the double quotes it executes error free but listbox remains unpopulated.
Debugging reveals that the listCourses is empty? I don't think my list is being picked up even though there are 28 rows of data in sheet6

frmRound.lstCourses.rowsource = listCourses

 

[RoryA]

Is sheet6 the tab name, or the code name?

 

[Theo71]

Sheet6 is the code name.

I have been searching the web, I failed to mention that I am running Excel 2003. I believe rowsource might not be the way to go.

I have created a combo box instead of a list box and managed to load the data in from sheet6.

The new issue is that there are 3 columns on the worksheet, but on the combo text when I select an item off the drop down it only shows the first column.
I will need to pick up all 3 columns in subsequent processing.

Private Sub UserForm_Initialize()
   
    Sheet6.Activate
    
    MsgBox ActiveSheet.CodeName
    cboCourses.List = Range("a2:c28").Value
   
    frmRound.lblCourse.Caption = "Courses Listed in " & Sheet6.Name
    
    
End Sub

 

[Norie]

Have you set the ColumnCount of the combobox to 3?

 

[RoryA]

FYI your original didn't work because the Name requires the tab name, not the code name.

 

[Theo71]

Thanks Rorya the rowsource worked with the tab name.
I wanted to use the code name because the user might change the tab name in future.

Norie, yes I did have the columncount of the combobox = 3
I also set the Style property to 0, and entered Text to appear at the top of the combobox. All other properties set to the defaults.
In the drop down section of the combo box all 3 columns from the worksheet appear for each line.

After clicking one of the lines only the first column appears in the text.
combobox.value only contains the first of 3 items.

I will need to use the other 2 columns after clicking. Maybe my approach is wrong.
In VB6 I used a DataGrid which was like an array and you could work with each column as a separate field. Does Excel VBA have something similar in userforms?

Sorry if my VBA terminology is not good.

 

[RoryA]

To use the code name, you can use:

Sheet6.Range("A2:A" & lastrow).Name = "listSource"

for example.