Listbox within Userform

  • Hello experts,

    I have a nagging problem that perhaps you can help solve.

    in the attached file, when a userform is selected a user is supposed to FIRST select a row in the userform and then enter information that populates a sheet in the background. Often times, a user will start typing without selecting a line first. When this happens, and they save the userform, it clears the headers of the sheet and subsequently the listbox. Is there a way to prevent a user from hitting save unless they have selected a row in the listbox? Or is there a way to prevent an inadvertent save from wiping out the headers?

    Thanks in advance

  • Re: Listbox within Userform

    I would suggest you give more information.

    Give step by step instructions including worksheet names, cell address, etc. so we can recreate the problem.

    Looking at your workbook it's not readily apparent what you are talking about.

    Bruce :cool:

  • Re: Listbox within Userform

    Hi Kenneth, thank you for your response,

    Where in userform code should I insert this. When I inserted in at the end of the current code, I get an error message.


  • Re: Listbox within Userform

    Hi Skywriter,

    For example if you follow the following steps, you will see what I mean,

    - If you select sheet named Lisa
    - Click the link that says "form for this sheet"
    - When the user form opens, start typing in the fields, a couple will do
    - Click save
    - you will see that everything you typed replaced the headers, (project name, project description, etc)
    However, if you had only selected row 1 before typing, you would put the information in the right place and it works perfectly.

    The problem is many of my users start typing before and ignore the first step of selecting a row .............

    I would like to know how to force the user to select a line before they type. A message box suggestion would work as long as it prevents them from wiping the headers out form the spreadsheet. If everytime someone opens their sheet and does not select a row, it will just continue to replace the header and I lose history.

    Hope that helps

  • Re: Listbox within Userform

    I get an immediate error on this line.

    Sub MakeUF7()
        Userform1.Make Sheet7
    End Sub

    The way I would do this is have the code check a column for the last row with data.
    For instance it could check column B it finds data in row 1 or column B and then offsets to row 2 and puts the data in that row.
    The next time it finds data in row 2 of column B offsets 1 row and puts the data in row 3.
    All of this assumes that the each time the user makes an entry there's a mandatory entry put in column B, if not then whichever column always has a mandatory entry then you can use that column to find the law row with data.
    Another suggestion would be to remove the numbers in column A and have the code check that column for data and enter a new number each time, thus eliminating a possibility of find a blank cell somewhere in your data where the user didn't make an entry in the user form.

    Bruce :cool:

  • Re: Listbox within Userform

    Add Kenneth's code to your Save button code:

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Listbox within Userform

    Quote from rory;778990

    Add Kenneth's code to your Save button code:

    Thank you so much, by inserting the code in the command button to save, it works as intended. I have a number of workbooks that uses this similar style and this has been very helpful.

    Thank you!

Participate now!

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