excel vba ms-access userform named-ranges

'Error 380: Could not set the RowSource property. Invalid Property value' for Excel user form to import data from Access to Excel

I am using Excel 2013 in Windows 7.

I’ve created a userform in Excel, using VBA in Excel to import data from Access into Excel after clicking a button per onlinepclearning(dot)com/append-and-delete-ms-access-record-sets-from-an-excel-userform/. This site is where he amends his Import subroutine.

After hitting the Import button, data from Access should appear in a listbox on the userform. Data is not to be imported directly into the userform but imported onto the worksheet and referenced as a named range in the worksheet (called DataAccess with a named range formula of: OFFSET(Import!$A$1,1,,COUNTA(Import!$A$2:$A$10000),7)--there are 7 fields to the table we are transporting from Access to Excel) in the user form.

Name Manager

A screenshot of the userform in Excel:

Excel Userform

When I click the Import button on the userform I get 'Error 380: Could not set the RowSource property. Invalid Property value'. Is it having a problem with the named range or the list box? I’ve looked through so many tuts and no answer.

I've also tried changing:

Me to UserForm1 and receive Error 424 (Object required) in procedure Import_Data.
Me.listData...="Sheet2!DataAccess" and I received the original Error 380 but the data imported onto the Excel Sheet2 just not the listbox on the Excel userform.
Me.listData...=Sheet2!DataAccess without quotes and I received Error 438 (Object doesn't support this property or method) in procedure Import_Data but the data imported onto the Excel Sheet2 just not the listbox on the Excel userform.
Me.listData...Import!DataAccess and I received Compile error: Expected Function or variable. The data did not come over to Sheet2 of Excel.
Me.listData...Sheet2.Range("DataAccess") but received Error 1004 (Method 'Range' object '_Worksheet' failed) in procedure Import_Data but the data imported onto the Excel Sheet2 just not the listbox on the Excel userform.

(1) UPDATE 1 8/5/2016 - I've removed the quotes from around DataAccess without referring to Sheet2! so that the one line of code in the Sub ImportUserForm() reads

Me.lstDataAccess.RowSource = DataAccess

and I will now get no error msg but a msgbox saying that the data has been imported; however, the data appears only on the spreadsheet (Sheet2! aka Import!) that the OFFSET/Named Range refers to and not my listbox.

Other tutorials say to set column width (which I set to 40 pt;40pt, etc...7 times total) and column count (which I set to 7)

Still no data appears in listbox on userform in Excel.

Here is the code for the listbox which is called lstDataAccess along with the code to initialize the userform. I don't know why they made this a double click (not sure what this means) b/c all he does on the tutorial is hit the import button and all of the data from the Access table will import to the Excel userform.

(Arec1-7 is the name of the text boxes on the userform, which correspond to the field names in Access--Surname, FirstName, Address, etc.)
(lstDataAccess is the name of the listbox on the userform.)

Private Sub lstDataAccess_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'dim the variables
Dim i As Integer

'find the selected list item
i = Me.lstDataAccess.ListIndex
Me.lstDataAccess.Selected(i) = True

'add the values to the text boxes
Me.Arec1.Value = Me.lstDataAccess.Column(0, i)
Me.Arec2.Value = Me.lstDataAccess.Column(1, i)
Me.Arec3.Value = Me.lstDataAccess.Column(2, i)
Me.Arec4.Value = Me.lstDataAccess.Column(3, i)
Me.Arec5.Value = Me.lstDataAccess.Column(4, i)
Me.Arec6.Value = Me.lstDataAccess.Column(5, i)
Me.Arec7.Value = Me.lstDataAccess.Column(6, i)
End Sub

Private Sub UserForm_Initialize()
'does not get assigned to any button
Me.Arec1 = Sheet1.Range("J3").Value
End Sub

(Again, all of this code is in the userform and not the module for the spreadsheet portion of the tutorial.)

(1) END OF UPDATE 1 8/5/2016 - (I'm going to try the Private Sub Import_Click() in the answer below, but there is no other code that I see would be applicable to the import part of this project. The rest is to close, open the userform and then append and delete the data but I need to import the data to the listbox first before I can tackle append/delete.)

(2) UPDATE 8/5/2016 - I've replaced the code for Sub ImportUserForm () with the entire code for clarity.

I can only add two links b/c I need 10 reputations per the forum rules, but I've already added the named range's scope and a shot of the user form. The data is imported from the Access table into Sheet2! of the spreadsheet per the OFFSET formula in the named range adding it to A2 and going over 7 columns all the why down to A10000 if ever needed. It currently looks like this:

It's wrapping but the headers go across the 7 columns in Excel with data starting in Row 2 (A2 to G2) all the down to Row 7 b/c I only have 7 rows of data in Access.

(3) UPDATE 8/6/2016 Below is a link to download the file from Google Docs. See PLEASE NOTE below b/c it semi works but I would like to use the OFFSET function in the name mgr rather than the fix I use now of using an absolute range.

-Excel wkbk and userform - (I'm not allowed to include more links so see below) https(colon)//docs(dot)google(dot)com/spreadsheets/d/1tRF3bJf4ng-bM6Kiefptpbrw9jr4T_BGZ0gKm9S9614/edit?usp=sharing (Just click on the button on the Export wksht, Click for Userform, to activate userform. I don't need help with any of the other buttons on either worksheet, incl. the Import button on the Import wksht.)

-Access DB with PhoneList table - https(colon)//drive(dot)google(dot)com/file/d/0B9IyKJSJ52ghNEFfYVNWUE1wcEU/view?usp=sharing

HOW IT WORKS: The reason I want to import is b/c I need to see if the append/delete buttons will work on the userform. You are supposed to be able to import data from the Access table PhoneList into the listbox on the Excel userform, which it actually imports into the Import wksht and gets reflected into the listbox via the named range DataAccess. You should then be able to select a line of imported data from the listbox where it will appear in the text boxes above so you can change the data if needed and then append the table in Access or you can just delete the line of data via the Append or Delete buttons.

PLEASE NOTE: The Import on the userform now imports data to the listbox on the userform but only after I close and open the userform. I am supposed to be able to click the Import button, even with nothing in the search box next to it, and import all of the data from the Access database. You have to delete the data from the Import worksheet to clear the data on the userform and start over.

I went to the properties of the listbox of the userform and changed:
the column count to 7
the column width to 40 pt
the multiselect to 1-fmmultiselectmulti
the RowSource to Import!DataAccess (I believe this was a big key)
for the above to work I had to change the DataAccess named range and make it an absolute range and remove the OFFSET function. (Named ranges with OFFSET don't appear in the range selector box, by the way, so now it appears in the range selector). The Named Range in the name mgr called KEEP is the old named range that was DataAccess using the OFFSET function. I would like to use this, if I can.
I tried using the Private Sub FillListBoxFromOffsetRange () but received an error with

arr.Data=Application.Range("DataAccess")

highlighted yellow when try to debug the code. I also tried calling it from my ImportUserForm code but it didn't work (maybe I was doing it wrong), but the internet says you need to call to a Private Sub from a 2nd module. I gave up trying that and started working on step 1. above and have semi-success, but still need help.

END OF UPDATE 8/6/2016

(below is continuation of original post)

P.S. I didn’t double click the Import button on my form before adding the new code to the userform, but I didn’t do that w/ another button and it works on the userform. Also, I added the code to the userform and not the module (which is what we used to import and export btw Access and Excel via some buttons on an Excel worksheet).

I’m new to VBA and userforms. Here is his correction per the website along with excerpted code:

Thank you in advance

(1) UPDATE 2 8/5/2016 - I added the entire code for Sub ImportUserForm () below.

'Import button click event
Private Sub cmdImport_Click()
ImportUserForm
'Inform the user that the macro was executed successfully.
MsgBox "Congratulation the data has been successfully Imported",  
vbInformation, "Import successful"
End Sub
_____

Sub ImportUserForm()
'we are not importing the data directly into user form but importing onto     
'the worksheet and referencing _
'it as a named range in the user form
'he made a slight change to the code per his Append and Delete webpage b/c     
'once we delete and append data we want to import back into _
'list box again to show new values have been removed or appended
'Declaring the necessary variables.
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rs As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim SQL As String
Dim i As Integer
Dim var
'add error handling
On Error GoTo errHandler:
'Disable screen flickering.
Application.ScreenUpdating = False
'clear the values from the worksheet
Sheet2.Range("A2:G10000").ClearContents
'get the path to the database
dbPath = Sheet1.Range("I3").Value
'set the search variable
var = Me.txtSearch
Set cnn = New ADODB.Connection ' Initialise the collection class variable

'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

'Create the SQL statement to retrieve the data from table.
If CheckBox1 = True Then
SQL = "SELECT * FROM PhoneList WHERE SURNAME = '" & var & "'"
Else
SQL = "SELECT * FROM PhoneList WHERE SURNAME LIKE '" & var & "%" & "'"
End If

'Create the ADODB recordset object.
Set rs = New ADODB.Recordset 'assign memory to the recordset

'ConnectionString Open '—-5 aguments—-
'Source, ActiveConnection, CursorType, LockType, Options
rs.Open SQL, cnn

'Check if the recordset is empty. 'EOF is end of file
If rs.EOF And rs.BOF Then
'Close the recordet and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
'DataAccess is a named range in the worksheet _
'OFFSET(Import!$A$1,1,,COUNTA(Import!$A$2:$A$10000),7)
MsgBox "There are no records in the recordset!", vbCritical, "No Records"
Me.lstDataAccess.RowSource = ""
Exit Sub
End If

'Write the reocrdset values in the sheet.
Sheet2.Range("A2").CopyFromRecordset rs

'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing

'Enable the screen.
Application.ScreenUpdating = True

Me.lstDataAccess.RowSource = DataAccess
'Me.lstDataAccess.ColumnCount =     
Sheets("Import").Range("DataAccess").Columns.Count

'error handler
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure     
Import_Data"
End Sub

Solution

We've been beating our heads for an answer that was hidden behind all the details. It is your range definition that is the problem.

Even though you're using a Named Range - you can see the issue immediately if you look at the values in the Name Manager. They're not defined.

Similarly if you run the named range thru using VBA, you can immediately see the problem. VBA does not see the Offset function as returning a range - it returns a reference to a range - and the format is actually a Variant array.

In a test function you can see what your range holds by adding a watch to [DataAccess]. It is a two dimensional Variant array.

So the simple answer is:

You CANNOT DIRECTLY set the RowSource to a Named Range that is using the Offset function

There may be ways to convert the variant array to a real range, but you cannot treat this type of Range Name as a true range.

Just try using Debug.Print Range("DataAccess").Cells.Address or adding a watch to Range("DataAccess") and it will give you an error unlike other basic range names that refer to cells.

You can access this type of range's values is by wrapping the range name in square brackets eg. [DataAccess]

EDIT - You can fill the ListBox using the OFFSET range by using the AddItem method on the resulting array.

I still believe there's a problem with your OFFSET formula, because from my tests you only end up with 1 filled column in the array.

However - without confirming your listbox properties, seeing a screenshot of your data, viewing actual recordset fields, or your code that fills the spreadsheet (instead of "etc.etc.etc"), it's hard to test or even guess.

But this code will fill your ListBox based on a Range names that use an OFFSET function:

Replace the line that sets .Rowsource = DataAccess with FillListBoxFromOffsetRange

Add this code to your UserForm module

Private Sub FillListBoxFromOffsetRange()

    Dim arrData     As Variant

    Dim intRow      As Integer
    Dim intCol      As Integer
    Dim strRowData  As String

    With ListBox1
        .Clear              'Make sure the Listbox is empty
        .ColumnCount = 7    'Set number of columns

        ' In order to access Workbook ranges need to use Application Object
        arrData = Application.Range("DataAccess")

        For intRow = LBound(arrData, 1) To UBound(arrData, 1)

            ' Process first column of new row
            intCol = 1
            strRowData = arrData(intRow, intCol)
            .AddItem strRowData ' Add the first Column of the row

            ' Append remaining columns to end of row
            For intCol = LBound(arrData, 2) + 1 To UBound(arrData, 2)
                strRowData = arrData(intRow, intCol)
                ' List rows have zero-based index
                .List(intRow - 1, intCol) = strRowData
            Next intCol

        Next intRow
    End With

End Sub

No Problem - Works as it should - your data - your simulated userform

All I changed was using my above function exactly as I suggested and commented out your code

FillListBoxFromOffsetRange

'Me.lstDataAccess.RowSource = DataAccess
'Me.lstDataAccess.ColumnCount =
'Sheets("Import").Range("DataAccess").Columns.Count

And changed the With ListBox1 to match actual Listbox name With lstDataAccess

Results in: