VBA Run-time error 70 permission error on User Form

    Hi,
    I have designed a user-form from within the MS VB module of Excel. I have set up the form as required adjusted all the properties as required and have used the following vba code to make it work;



    I then created a Word Art button on the worksheet that is supposed to bring up the form so that a user can begin inputing, the code i used to do this is on sheet2:


    Code
    Sub OpenForm()
    VolOrgs.Show
End Sub


    When i click on the button assigned to the open form macro i get a run-time error 70 "Permission Error". And the debugger highlights the following line of text in yellow:

    Code
    VolOrgs.Show


    I have checked the File, Permissions menu and it is set to unrestricted access.


    Does anyone have any suggestions or a possible solution for this?


    Id be extremely grateful if somebody could help me resolve this issue.


    Regards
    Damian

    Re: VBA Run-time error 70 permission error on User Form


    Hi,


    The error is more than likely in the Initialize event.
    Can you set through the code line by line, use F8, until the offending line is revealed. Post back with your findings.

    [h4]Cheers
    Andy    [/h4]

    Re: VBA Run-time error 70 permission error on User Form


    Hi Andy,


    I used F8 to step through the code line by line, and it didnt get very far before i got the run-time 70 error. It appeared at the following line:


    Code
    .AddItem "Carers [Aged Under 18]"



    Please let me know if you you need any more information, (or email me at [email protected] if you'd like me to send the file to you)


    :thanx: for your help

    Re: VBA Run-time error 70 permission error on User Form


    Check the RowSource property of the combobox. I think you may have it linked to cells in which case you can not add your own items.

    [h4]Cheers
    Andy    [/h4]

    Re: VBA Run-time error 70 permission error on User Form


    Mr Pope, your a star :congrats:


    That is exactly what i had done, ive just deleted the rowsources for the comboboxes, and for the first time since i started doing this my form has initialized.


    It still doesnt quite work as intended as when i enter the some values on the form and press on the "Accept and Save" command button i get this run-time error: 438 'object doesnt support this property or method'
    I stepped through the code again using F8 and this is the offending line of code (i think)


    Code
    ActiveCell.Offset(0, 2).cboClientCategory.Value


    Any ideas about what else i have done wrong?



    Thanks again
    Damian

    Re: VBA Run-time error 70 permission error on User Form


    Quote from Andy Pope

    missing an equals?
    [vba]ActiveCell.Offset(0, 2) = .cboClientCategory.Value [/vba]



    Correct again Andy!!! I was missing and = but needed to lose a .


    Code
    ActiveCell.Offset(0, 2) = cboClientCategory.Value


    Now the form is working as intended apart from one small thing, when data is entered and the ok button is pressed the data is recorded where i wanted it on sheet2. However, when the form is cleared and i enter another persons details and click accept, this new data overwrites the previous responses on the worksheet, rather than appearing in the next available free row, beneath the previous set of responses.


    Have you any idea what the solution to this issue is?


    Many many thanks for your help so far, as without your assistance id have never got this far.


    Kind regards
    Damian

    Re: VBA Run-time error 70 permission error on User Form


    At the end of your cmdOk_click() routine[vba]' replace
    ' Range("B2").Select


    ' with
    set Activecell = activecell.offset(1,0)[/vba]

    [h4]Cheers
    Andy    [/h4]

    Re: VBA Run-time error 70 permission error on User Form


    Ok, I have replaced where I had written:


    Code
    Range("B2").Select


    with


    Code
    Set ActiveCell = ActiveCell.Offset(1, 0)


    When i try to click the "ok" button it now says "compile error - invalid use of property"


    The highlighted bit of code in debugger is "ActiveCell ="


    Regards
    Damian

    Re: VBA Run-time error 70 permission error on User Form


    oops!
    [vba]Activecell.offset(1,0).select[/vba]


    Although I'm not sure why the code at the begining of the routine is not working for you.
    [vba] Range("b2").Select

    'to get down to the 1st empty row

    Do

    If IsEmpty(ActiveCell) = False Then
    ActiveCell.Offset(1, 0).Select
    End If

    Loop Until IsEmpty(ActiveCell) = True [/vba]
    Anyway you will need to comment this out otherwise the setting of the active cell at the end of the routine will be made redundent.

    [h4]Cheers
    Andy    [/h4]

    Re: VBA Run-time error 70 permission error on User Form


    I played around with some of the cell references until it eventually put the data where i wanted it to go. :ole:


    Andy i really cant thank you enough for the help you have provided with this problem, when i was first tasked with setting up this data input form, i didnt envisage it being quite so awkward to set-up. After several attempts at setting it up on my own I was getting no-where fast and had begun to think that the writing of the VBA for it would need to be outsourced and hence we'd have to pay someone to do it.


    Im really pleased that the form is working, and am proud of my efforts, although the kudos should go to you for your efforts in helping me.


    I have edited the Excel properties of the workbook, and have entered "Credit goes to Andy Pope of http://www.ozgrid.com as without his input this would still be in progress" in the comments section.


    Just in case anyone is interested here is the complete and working VBA code:



    Thanks, thanks and thanks again
    kind regards


    Damian

    Re: VBA Run-time error 70 permission error on User Form


    It seems like i've not entirely finished with this form yet.


    Ive been trying to add protection to the sheet to prevent users from altering the cells where the form writes the data to.


    I want to lock the whole sheet except the range A1:E8 (this is where the buttons to open the sheet, get instructions, and email me will be located).
    So all the user is able to do is click one of these buttons. When the open form button is clicked i need the sheet to unprotect, then after the form is filled in and the user hits the "OK" button i want it to lock the sheet after it has written the values from the form to the worksheet.


    I didnt think this would be too difficult to achieve, but each of my attempts at this have generated "Exception Occurred" messages.

    Re: VBA Run-time error 70 permission error on User Form


    This will apply and remove protection.
    [vba]
    wSheet.Unprotect Password:="Password"


    wSheet.protect Password:="Password"
    [/vba]You might want to use the macro recorder and apply sheet protection in order to get all the arguments.
    You will also need to uncheck the Locked property of those cells

    [h4]Cheers
    Andy    [/h4]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login