Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet

  • Hello,


    I have spent almost a week trying to get my userform to work. My eyes are really bad, and a user form will help immensely.


    The buttons enter, search, and exit work for me; however,Edit and Delete do not. I'd like to change my exit button to exit and save. The sheet's headers are ID, First Name, Last Name, Phone 1, Phone 2, Email, Address, City, State, Zip, and Notes.


    Please can anyone assist me? Here is the code which I found here and there from other spreadsheet examples:


    Command Button 1 is Enter, Command Button 2 is Search, Command Button 3 is Edit, Command Button 4 is delete, Command Button 5 is Exit


  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    I can help you but you have to upload the workbook.
    Dummy data is fine if you have sensitive info, but leave the sheets, sheet names, headers etc. intact.


    When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions to upload a workbook.

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    There's no data and there are two sheets.


    So explain to me in detail which buttons you want code for and exactly how you want to use these buttons, meaning what you will type etc., your comments are a little hard to follow.


    Please don't be generic, be specific, not I want to search the sheets.
    My next question would be search the sheets for what and your reply might be what is in the textbox and my next question would be which text box etc.


    Why do I ask these things because you are asking me to help you do certain things and I don't know exactly what you expect, I may make it search a certain way and you may say well that's not what I wanted, so please spell it out.


    Explain why there are two sheets and how that works into what you want.
    Also naming text boxes 1, 2 etc. is a terrible idea, it makes it very difficult, you have to remember what box was which, not a good idea.


    I'm renaming them to what the label says.

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    I removed the actual data and left the headers in those two sheets. I'll send you the revised worksheet tomorrow with some data added. Was there any VBA? I figured I could move the sheet tabs and make HUMS the first sheet when I needed to use the user form and vice versa. :) I need data to populate under the headers in the sheet with the user form and be able to search by ID, and hopefully be able to delete data with the userform, edit data with the user form and exit and save using the data form. Does any of that make sense?


    I have a macro in which you can run too which should pull up my Userform. I'm just now heading home from work. I've put in almost two hours of extra tonight at the office here and I'm going cross eyed. Thank you for absolutely everything.

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    Of course you saw the VBA. Gees my brain is dead. I'm sorry I'm such an air head Bruce. I'll get to it first thing in the morning. It's almost 8:00 pm here adn I have to get ready for tomorrow. I'm so sorry to hold you up. Sincerely, kelly



    Quote from kjpeissner;771074

    I removed the actual data and left the headers in those two sheets. I'll send you the revised worksheet tomorrow with some data added. Was there any VBA? I figured I could move the sheet tabs and make HUMS the first sheet when I needed to use the user form and vice versa. :) I need data to populate under the headers in the sheet with the user form and be able to search by ID, and hopefully be able to delete data with the userform, edit data with the user form and exit and save using the data form. Does any of that make sense?


    I have a macro in which you can run too which should pull up my Userform. I'm just now heading home from work. I've put in almost two hours of extra tonight at the office here and I'm going cross eyed. Thank you for absolutely everything.

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    I got interrupted when I was initially going to upload the revised Excel spreadsheett, so I've tried again. I think it is uploaded now. I've uploaded the revised spreadsheet. I added information the ECE page of what I've been trying to do. Please let me know if I need to be clearer. Also, I intend to use the userform for both sheets. When I need to work on HUMS with the Userform, I'll pull the tab over and make it sheet one, and then do the same with ECE when I need to work on it. :)


    Thank you kindly, kelly

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    Quote

    Of course you saw the VBA. Gees my brain is dead. I'm sorry I'm such an air head Bruce. I'll get to it first thing in the morning. It's almost 8:00 pm here adn I have to get ready for tomorrow. I'm so sorry to hold you up. Sincerely, kelly


    Don't worry, I'm here to help you.

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    Right now the code I'm writing is only written for sheet1("ECE 201603").


    What would you think about a combo box that had a list of all the sheet names or selected sheet names that you could pick from to run the code against.


    Regarding an edit button I'm thinking you just use search instead and the code for the enter button will always check if the ID exists and if it does it will just ask you if you want to replace the existing record.


    If you are replacing you say yes, if by chance you are trying to enter what you think is a new record but it already exists you get warned, giving you a chance to change the ID?


    Delete will also warn you before doing the deed, giving you a chance to change your mind.


    By exit/save do you mean close the form and save the workbook but leave the workbook open or close the form and save and close the workbook?


    If it's the latter then we should also have a close form button in case you just want the form to go away and keep working.

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    What you are proposing is awesome! Thank you! And close the form and save the workbook but leave the workbook open is what I's like to be able to do. Wow!

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    If you happen to see this message.


    Regarding your ID numbers, are they pure numbers 123 etc. and if so do you want then to get entered into the cell as a number because I will need to change some code to get that for you?


    The output of the text boxes are text and I need to change that.


    Probably the same question for zip code.


    The changes are very minor so don't worry about adding to my workload it's no big deal.


    If they are a mixture of numbers and letters or just text then it won't matter.

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    Yes the ID numbers and zip are numbers only.


    I was concerned about your workload. Even so you are taking personal time to do this and that is valuable too. I'll make sure to pay it forward to someone that needs assistance or help in something in the future. Again, thank you.

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    Here's all the code for anyone that doesn't want to open the file.


    Remember if you want to use this form in a workbook other than the one I uploaded, right click on the form name, choose to export the file and then import that exported file into another workbook.


    Let me know if you have any bugs or you want to do something else with the form.


    There isn't a whole lot of data validation code in this form, I'll leave that up to you.


    Control + q is a shortcut to run the macro that initializes the form.

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    This is absolutely perfect! You are the best. I can't thank you enough!! I'm going to use it in my budget ledger too. You have no idea what a improvement you have made in my life. I'm going to send you a PM









  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    The OP has offered to make a donation, that offer is very much appreciated.


    I don't have a charity with links, so if someone on this board wants to chime in with a suggestion please feel free.


    To the OP it was completely my pleasure, if you have issues or want to add some more functionality, I'm here. :)

  • Re: Excel VBA USERForm Enter, Search, Edit, Delete, Exit for Spreadsheet


    Hi, I found when entering notes, the data does not wrap. Instead it goes on and on. If I hit enter to get the information to another line, it tries to exit. Please, is that anything you can fix at your convenience? Thank you.