Posts by Yinkavelli

    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!

    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

    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.


    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: Userform Listbox column heading


    My situation is a bit different. I have a userform with textboxes and combo boxes that populate a listbox. Then a save button essentially captures that information on to a designated excel sheet.

    Here is my issue:
    If a user opens the userform and doesn't select a row in the listbox before typing in the respective combo boxes and textboxes.....and hits save on my userform........the header row gets replaced with the data entered and messes things up. As the sheet needs to track totals etc.
    I tried locking the header row so that when the user gets a error message, it might prompt them to actually select a row in the listbox. Is there a way to
    1. Prevent information from being typed over the header row if a user starts entering info in the textboxes and then saves?
    2. Prevent a user from hitting the save button unless a row is selected for information to be typed.

    Thanks in advance

    Re: Referencing Userform on Multiple Worksheets

    1. Me.ListBox1.RowSource = .Range("A2").Resize(.UsedRange.Rows.Count - 1, 5).Address([ThisWorkbook],sheet10!$A$2:$E$51, True)

    I made an attempt to do so but somehow I am getting errors, could you please show me where I am going wrong? In the above code, I am working on sheet 10. and have specified the area but still no success. I appreciate all your help on this, truly do


    Re: Referencing Userform on Multiple Worksheets

    Quote from yegarboy;535769

    As stated above I didnt notice a method of unhiding the sheets. Perhaps thats why it goes to the first unhidden sheet instead of the called sheet.

    I dont want it to show the sheets, I just want it to put the information in the sheets and all the end user needs to do is pull up the userform, select the line they would like to add information to or edit and save.

    Re: Referencing Userform on Multiple Worksheets

    Thank you for the quick response and honestly, I am not an expert in vba, I was just suiting a template to my needs and that is probably why I decided to duplicate the forms multiple times.

    Regarding the first solution with the .Address reference, should I insert the "workbook, sheet and cell arguement where you have the parenthesis(in your response) throughout the code where .address is present?

    Second question, how would i be able to simply write a code for your second solution, sounds simpler

    Re: Referencing Userform on Multiple Worksheets

    I have attached the sheet I am working on below,
    In this workbook, I have 11 Worksheets
    Each Worksheet represents a project
    Each project has a unique project number
    So, I made a userform for each project and a button to activate the userform for people to select and edit.
    What I want to do is hide the 10 sheets and keep the sheet titled "front page" to place all the buttons on that page, so that when a user clicks one of the 10 projects, that userform will show with the contents of its particular sheet.

    Hope the makes sense and THANK you for the response

    I need help!
    I have created 10 userforms on a particular workbook. Each userform is linked to particular sheet. The userforms have a listbox and three text boxes that correspond to specific cells in that particular sheet.

    The listbox basically shows the cells between column A and E, the text boxes are what makeup cells C, D and E. I have a macro button set up so that when I click on it, it brings up the userform, so far so good.(users can edit the line items and so forth)

    Here is the problem, when I go hide the sheets and bring all the buttons to a common page, it does not reference the specific sheet they were set for, instead it looks on the current page

    Sample code below