VBA Run-time Error '70': Permission Denied.

Question

BTW, my employer has an Office 365 licence, which is what I'm using.  That option wasn't available in the dropdown .

What I'm trying to do:  I have a userform combobox that I am trying to add some refined search functions to, but I don't know how to code VBA well enough to make that happen.  

What I did:  I copied some code from a forum and modified it to my userform and controls.

What happened:  When I run the code, I get this error message - 

When I click debug, it shows the highlighted line of code:

Any help would be very much appreciated.

Answer 1

Hi Dana

A runtime error 70 indicates you are accessing the same names object twice in your code . . .

Set a variable at the top of your code to = Worksheets("Data").Range("PROGRAM").Value

Then try setting V and .list to that variable

If that does not resolve this, what is .cboProgram referring to?

Answer 2

I haven't tried the fix yet, but cboProgram is the name of the userform combobox that I'm trying to add search functionality to.  I'll try the fix and reply. 

Thanks.

Answer 3

To Dana,

Thanks for reaching out. This is Gems88 an Independent Advisor. I'd be more than happy to help you.

Could you please comment out your current code in cboprogram_change and replace that with this code

Dim v As Variant, i As Long

Dim rangeval As Range

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Data")

With Me.cboProgram

If .Value <> "" And .ListIndex = -1 Then

v = ws.Range("Program").Value

.Clear

For i = LBound(v, 1) To UBound(v, 1)

    If LCase(v(i, 1)) Like "\*" & LCase(.Value) & "\*" Then

        .AddItem v(i, 1)

    End If

Next i

Else

Me.cboProgram.Clear

'.List = ThisWorkbook.Worksheets("Data").Range("program").Value

For Each rangeval In ws.Range("Program")

With Me.cboProgram

 .AddItem rangeval.Value

End With

Next rangeval

End If

End With

Hope this one helps

Regards,

Gems88

Answer 4

To Dana,

Thanks for reaching out. This is Gems88 an Independent Advisor. I'd be more than happy to help you.

Could you please comment out your current code in cboprogram_change and replace that with this code

Dim v As Variant, i As Long

Dim rangeval As Range

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Data")

With Me.cboProgram

If .Value <> "" And .ListIndex = -1 Then

v = ws.Range("Program").Value

.Clear

For i = LBound(v, 1) To UBound(v, 1)

If LCase(v(i, 1)) Like "*" & LCase(.Value) & "*" Then

.AddItem v(i, 1)

End If

Next i

Else

Me.cboProgram.Clear

'.List = ThisWorkbook.Worksheets("Data").Range("program").Value

For Each rangeval In ws.Range("Program")

With Me.cboProgram

.AddItem rangeval.Value

End With

Next rangeval

End If

End With

Hope this one helps

Regards,

Gems88

Hi Gems88,

Thanks for your quick reply.  Sorry it took so long for me to get back to this.

I had some properties in the current userform that would cause it to error out, so I just created another userform with a combobox and inserted the code there.  Once I did that and ran the code, nothing happened when I stared typing in characters into the combobox.

Allow me to broaden the scope of the question, and maybe I should have lead with this, what I really want to do is type into a box (doesn't matter what kind, text combo, whatever) and have a have a list return values that filter as I type.  I would  like the list to reference a dynamic named range in a worksheet.  The worksheet is  codenamed in the VBA editor. 

 I have searched every single VBA forum that will show answers and I have found nothing that works.  If the forum requires a log in to see answers, I will not be able to use it because I'm doing this from work.

This seems to be a common request.  Too bad there's not a good video for it?

Answer 5

Hi Dana

A runtime error 70 indicates you are accessing the same names object twice in your code . . .

Set a variable at the top of your code to = Worksheets("Data").Range("PROGRAM").Value

Then try setting V and .list to that variable

If that does not resolve this, what is .cboProgram referring to?

Hi DaveM121,

Thanks for getting back to me so quickly.  So, I'm new to VBA .  I know how to declare and set variables, but I'm not sure what that would look like with the instructions that you gave me.

What I'm really trying to do is type into a box (doesn't matter what kind, text, combo, whatever) and have a have a list return values that filter as I type.  I would  like the list to reference a dynamic named range in a worksheet.  The worksheet is codenamed in the VBA editor.   

I have searched every single VBA forum that will show answers and I have found nothing that works.  If the forum requires a log in to see answers, I will not be able to use it because I'm doing this from work.

Thank You,