R
rupertsland
I am getting a Run-time error 70 (permission denied) in Excel 2003 when I try
to display a form with a list box that displays 2 columns. The first column
is bound. The purpose of the form is to let the user pick an item from the
list box and have that selected value (from the bound column) return to a
cell in Excel.
What baffles me is why I get the run-time error when I have another form
that's nearly identical (also with 2 columns), uses nearly the same VB code,
and does not generate any error (what's different are the use of range
names). I've compared the properties of each form, and I am unable to trace
the problem except to one VB statement (see below).
Why do I get this run-time error? How do I fix it?
Here's sample VB code for the form. I'm not an advanced programmer, but here
goes:
----- start : code for user form 'UserForm_RateCodes' -----
___________________
Option Explicit
___________________
Private Sub Userform_Initialize()
'Declare variables
Dim Cell As Range
Dim Item
Dim NoDupes As New Collection
'trap errors
On Error Resume Next
'place cellpointer in correct cell
Range("Timesheet_RateCode").Select
'look up rate code range and read rates into memory and check for
duplicates
UserForm_RateCodes.ListBox1.RowSource = "Rates!RateTable"
For Each Cell In Range("Rates!RateTable")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0
'Add the nonduplicated items to a ListBox
For Each Item In NoDupes '<--------- this is where run-time error 70
occurs
UserForm_RateCodes.ListBox1.AddItem Item
Next Item
End Sub
___________________
Private Sub OK_Button_Click()
On Error GoTo Err_OK_Button_Click 'error handler
'Declare variables
Dim varSelected As String
Dim varNewCellVal As String
'Store listbox item selected by user in memory
varSelected = ListBox1.Value
'null handler
If ListBox1.Value = Null Then
varNewCellVal = ""
varNewCellVal = varSelected
End If
'put listbox value selected by user into correct cell
varNewCellVal = varSelected
Range("Timesheet!Timesheet_RateCode").Select
ActiveCell.Value = varNewCellVal
End
Exit_OK_Button_Click:
Exit Sub
Err_OK_Button_Click:
MsgBox Err.Description
Resume Exit_OK_Button_Click
End Sub
___________________
Private Sub Cancel_Button_Click()
Unload Me
End Sub
----- end code -----
to display a form with a list box that displays 2 columns. The first column
is bound. The purpose of the form is to let the user pick an item from the
list box and have that selected value (from the bound column) return to a
cell in Excel.
What baffles me is why I get the run-time error when I have another form
that's nearly identical (also with 2 columns), uses nearly the same VB code,
and does not generate any error (what's different are the use of range
names). I've compared the properties of each form, and I am unable to trace
the problem except to one VB statement (see below).
Why do I get this run-time error? How do I fix it?
Here's sample VB code for the form. I'm not an advanced programmer, but here
goes:
----- start : code for user form 'UserForm_RateCodes' -----
___________________
Option Explicit
___________________
Private Sub Userform_Initialize()
'Declare variables
Dim Cell As Range
Dim Item
Dim NoDupes As New Collection
'trap errors
On Error Resume Next
'place cellpointer in correct cell
Range("Timesheet_RateCode").Select
'look up rate code range and read rates into memory and check for
duplicates
UserForm_RateCodes.ListBox1.RowSource = "Rates!RateTable"
For Each Cell In Range("Rates!RateTable")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0
'Add the nonduplicated items to a ListBox
For Each Item In NoDupes '<--------- this is where run-time error 70
occurs
UserForm_RateCodes.ListBox1.AddItem Item
Next Item
End Sub
___________________
Private Sub OK_Button_Click()
On Error GoTo Err_OK_Button_Click 'error handler
'Declare variables
Dim varSelected As String
Dim varNewCellVal As String
'Store listbox item selected by user in memory
varSelected = ListBox1.Value
'null handler
If ListBox1.Value = Null Then
varNewCellVal = ""
varNewCellVal = varSelected
End If
'put listbox value selected by user into correct cell
varNewCellVal = varSelected
Range("Timesheet!Timesheet_RateCode").Select
ActiveCell.Value = varNewCellVal
End
Exit_OK_Button_Click:
Exit Sub
Err_OK_Button_Click:
MsgBox Err.Description
Resume Exit_OK_Button_Click
End Sub
___________________
Private Sub Cancel_Button_Click()
Unload Me
End Sub
----- end code -----