Error 70 when trying to load a listbox from a range.

Wizard13

New member
Joined
Oct 19, 2012
Messages
3
Reaction score
0
Points
0
Age
71
Location
God's Own County
Hi....
In MS Office 2010 I'm trying to use the following code to populate a listbox in Excel 2010.
Code:
Private Sub UserForm_Initialize()
'*
'** Populate a multi-column listbox'with data from
'** an Excel worksheet range
'*
Dim lbxTarget               As MSForms.ListBox
Dim rngSource               As Range
'Set reference to the range of data to be filled.
  Set rngSource = Worksheets("SBP").Range("SBPValAndText")
  Set lbxTarget = frmSBP.lbxSBP
  With lbxTarget
    .ColumnCount = 2
    .BoundColumn = 1
    .ColumnWidths = "0;100"
    .List = rngSource.Cells.Value
  End With
End Sub
I'm trying to set up the listbox so it shows some text descriptions (Col 2), but will return a numeric code (from Col 1) when a list item is double clicked (or selected then an "OK" button is pressed).
When I try to execute it (either natural or on stepthrough) I get "Run-time error '70' Permission denied" at the
Code:
.List = rngSource.Cells.Value
code line.
The Workbook is not protected, none of the Worksheets are protected, the range is not protected by VBA, the cells are not protected by VBA, and the VBA is not protected.
In fact, as far as I can tell, nothing is protected!
All the Help screens I've found indicate I'm trying to read/write from a protected file; I have NO file I/O at all!
Can anyone point me towards a solution?
 

Wizard13

New member
Joined
Oct 19, 2012
Messages
3
Reaction score
0
Points
0
Age
71
Location
God's Own County
OK, I was being dim (again!).
The Userform had a value in the Rowsource.
On removing it, the error magically disappeared (sort of like my IQ!)
 
Top