Posts by voutsy

    Re: Combobox repeating options

    Hi Max

    I am glad that you read my code so thoroughly!!
    However i wasn't asking for your interpretation of the english department of healths ethnic categories.

    If you would like to read into this then read here

    I was actually asking for help solving my vba problem.

    Have you anthing to add other than spam?

    Hi again,

    I have got (with your help) my userform working as intended, except for one minor problem:

    Once the form is filled in the user should click..

    "OK" and the data is written to the spreadsheet.

    After this the form remains completed so in order to input the next set of data the second button needs to be pressed - "clear form"

    This clears the form and allows the user to input the next data set, however the combobox options are multiplied. For example one of the comboboxes has 3 options, however on inputting the second set of data 6 options will appear, each option appearing twice. If the user then clears the form for the 3rd dataset then the combobox will have 9 options, again each option being repeated 3 times,.... and so on.

    What do I need to do in order to get the combobox options to reset to the default of just 3 choices.

    I attach my code below, and feel that the problem could lie in this section of code:

    Private Sub CmdClear_Click()
    Call UserForm_Initialize
    End Sub

    for info the rest of my code:

    Any help with this is greatly appreciated...


    Hi again,

    Ive got my form working ok however once the form is filled in and the user presses the button to write that data to the spreadsheet. Id like to make it so that they are able to edit the data without having to type in the spreadsheet. (ive found that the users random spellings wreak havoc with my formulas!).

    Ideally id have it so that the user could select a line from the spreadsheet and hit an "edit record" button that would then open that line of data in the form that created it.


    If the edit button could open a form similar to the form found in the Data/Form menu but with drop down menus for them to select corrections from rather than have them type them themselves.

    Are either of these options even possible?


    Re: Userform Vertical Scroll Bar to open at top of form

    Andy thanks for your help once again!

    For some reason neither that bit of code or the "Scroll Top" property made any difference to the starting position of the scroll bar on the form.

    Maybe it was something to do with the fact that the form gets opened through a command button on an information box?

    Anyway once I removed the command button from the bottom of the form, the scroll top property worked once again and the form opens up at the top.

    Hi again,

    Ive got my userform working once again!

    However the one minor irritation for me is that when the form with a vertical scrollbar opens up it always opens fully scrolled to the bottom of the form (where the command button to close is located). How can I make it so that the form always opens fully scrolled to the top?

    Ive tried putting various values in the "Scroll Top" property and "Scroll Height" property but I can only make it so that the form opens at the bottom or in the middle.

    Ive posted a screenshot of the form and its properties (although the image is quite low quality due to compression).

    Thanks for any help with this.

    Re: Object Variable or with variable not set

    Thanks guys,

    Derk you were right about me leaving some "orphaned" code in there after my edit.

    And Andy thanks I hadnt thought of putting Option Explicit in there, that saved me ages trawling through the code.

    Thanks to you both!

    I have made a few cosmetic changes to an excel user form that was working perfectly. The changes were only very minor and related to removal of some of the options in the drop down menus.

    Now when I click the "open form" button i get a runtime error 424 object required.

    Ill paste all my code below in the hope that someone can spot what I've done wrong.

    When I click on Debug the "VolOrgs.Show" bit of code is highlighted in yellow.

    Sheet 2

    MODULE 1

    'Opens form and unprotects the worksheet
    Sub OpenForm()
    End Sub

    MODULE 2


    Private Sub CommandButton1_Click()
    Unload Me
    End Sub
    Private Sub UserForm_Click()
    End Sub

    FORM VolOrgs

    Any help with this will be much appreciated


    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

    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 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


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

    Ok, I have replaced where I had written:



    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 ="


    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 .

    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

    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)

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

    Any ideas about what else i have done wrong?

    Thanks again

    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:

    .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

    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:

    Sub OpenForm()
    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:


    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.


    Re: Worksheet Forms, Option Buttons & VBA

    Quote from royUK

    damian - why not do as ShosMeister suggests.

    I have edited my original post and have included a zipped up version of the worksheet (although I did have to delete some of the content to get it under the maximum allowed attachment size). (its the one called zipped)

    I will try to publish the full worksheet on my own personal webspace when i get home from work tonight, but in the meantime i am happy to email the sheet to anybody who is interested in helping me.

    Im having dificulties with the form that i've created

    In the Age Band (grouped area) I have 4 Option buttons
    Under 18, 18-64, 65-74, 75+
    When one of these is selected, I wanted the selected value to appear in cell A100. When I select one of the option button cell A100 only shows TRUE or FALSE, how can i get cell A100 to display the age band selected.

    I am also having the same issue with the Ethnic Origin group of option buttons

    And also with the FACS eligibility Criteria grouped set of buttons.

    Could somebody please let me know what im doing wrong, and how to correct this.

    The other thing that I need help getting to work correctly is with the "Click Here to Save this record" command button. I wanted this button to do the following;
    When the inputter has completed inputting the details onto the form, the responses that have been input should appear across the various columns of ROW 100. When the command button is pressed i wanted it to;
    Move everything in ROW100 (A:L) and below, down one ROW, and then clear the form and finally save the worksheet.

    I hope I have explained this clearly,
    I tried to upload my .xls file but its too large at 100k, so i have cut out much of the file so that you can at least see some of what ive done, i am more than happy to e-mail a copy of the un-edited version to anyone who could help me with this.
    my email address is: [email protected]

    Thanks in advance for your time/effort.
    Kind Regards

    I have edited the post to included a zipped attachment (i still had to remove some things from it to get it under the maximum file size.

    Re: Search range, find value, copy then offset paste across 2 worksheets

    Ok i have attached some small screenshots that could possibly help explain what i mean.

    Basically id like the sub to:

    Search 'client tab' column A for any client ID that also exists in 'carer tab' column A. When it finds a matching value i want it to copy the value in 'carer' column "D" on the row where the matching value exists, to 'column C' in the client tab on the row of the searched for value.
    I want the sub to do this for all rows of Client tab A that contain data.