'Error 380: Could not set the RowSource property. Invalid Property value' for Excel user form to import data from Access to Excel
<p>I am using Excel 2013 in Windows 7. </p>

<p>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. </p>

<p>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.</p>

<p><a href="http://i.stack.imgur.com/04lyH.png" rel="nofollow">Name Manager</a></p>

<p>A screenshot of the userform in Excel:</p>

<p><a href="http://i.stack.imgur.com/DVxKs.png" rel="nofollow">Excel Userform</a></p>

<p>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.</p>

<p>I've also tried changing: </p>

<ol>
<li>Me to UserForm1 and receive Error 424 (Object required) in procedure Import_Data.</li>
<li>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.</li>
<li>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.</li>
<li>Me.listData...Import!DataAccess and I received Compile error: Expected Function or variable. The data did not come over to Sheet2 of Excel.</li>
<li>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.</li>
</ol>

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

<pre><code>Me.lstDataAccess.RowSource = DataAccess
</code></pre>

<p>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. </p>

<p>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)</p>

<p>Still no data appears in listbox on userform in Excel.</p>

<p>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.</p>

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

<pre><code>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
</code></pre>

<hr>

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

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

<p><strong>(1) END OF UPDATE 1 8/5/2016</strong> - (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.)</p>

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

<p>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:</p>

<p>Cell A1:ID | Cell B2:Surname  |  Cell C3:FirstName  |  Cell D4:Address  |  Cell E5:Phone  |  Cell F6:Mobile  |  Cell G7:Email</p>

<p>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.</p>

<p><strong>(3) UPDATE 8/6/2016</strong> 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.</p>

<p>-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.)</p>

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

<p>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.</p>

<p><strong>PLEASE NOTE:</strong> 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.</p>

<ol>
<li><p>I went to the properties of the listbox of the userform and changed:<br>
the column count to 7<br>
the column width to 40 pt<br>
the multiselect to 1-fmmultiselectmulti<br>
the RowSource to Import!DataAccess (I believe this was a big key)  </p></li>
<li><p>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.</p></li>
<li><p>I tried using the Private Sub FillListBoxFromOffsetRange () but received an error with</p>

<p>arr.Data=Application.Range("DataAccess") </p></li>
</ol>

<p>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.</p>

<p><strong>END OF UPDATE 8/6/2016</strong></p>

<p>(below is continuation of original post)</p>

<p>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). </p>

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

<p>Thank you in advance</p>

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

<pre><code>'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=" &amp; dbPath

'Create the SQL statement to retrieve the data from table.
If CheckBox1 = True Then
SQL = "SELECT * FROM PhoneList WHERE SURNAME = '" &amp; var &amp; "'"
Else
SQL = "SELECT * FROM PhoneList WHERE SURNAME LIKE '" &amp; var &amp; "%" &amp; "'"
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 " &amp; Err.Number &amp; " (" &amp; Err.Description &amp; ") in procedure     
Import_Data"
End Sub
</code></pre>