+ Reply to Thread
Results 1 to 19 of 19

Userform to enter data in multiple worksheets

  1. #1
    Registered User
    Join Date
    03-02-2019
    Location
    Sligo, Ireland
    MS-Off Ver
    2007
    Posts
    7

    Userform to enter data in multiple worksheets

    Hi,

    I am a beginner to excel and am in dire need of help.

    Basically, i have created an excel file to record various daily aspects of animals at a zoo e.g. food, enrichment, behavior, medical data etc. with each individual worksheet being for an individual animal/group of animals all formatted in the exact same way.

    My hope is to create a userform which will contain a drop down menu to select which sheet the data is to be entered in (e.g. which animal the data is being recorded for) and then various text boxes for all the variables (and sub variables in some instances).

    However as I mentioned, I am a complete beginner regarding this so I have no idea how to even begin the coding for this process or of this is even possible.

    I have attached the template file that I am working with. I am definitely reaching far beyond my abilities with this one so any and all help would be much appreciated. Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Userform to enter data in multiple worksheets

    This is certainly possible but there are a few things you should do to get started. First, you have to unmerge any merged cells in your sheets. Look into "Center across selection". This formatting feature almost always has the same effect of merging cells without actually merging them. You should avoid using merged cells at all cost because they can create havoc for Excel macros. Secondly, you would have to design and build a userform and then we can help you with the coding. If you also need help with building the userform, please let us know. Do you think this would be possible? If so, please attach a revised workbook.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Userform to enter data in multiple worksheets

    Hi zookeeper and welcome to the forum,

    After looking at your workbook, I have some suggestions. The yellow area is pretty constant and won't change. The blue part is a feeding schedule and will change every day or even multiple times a day. I'd make separate workbooks or at least worksheets for these two types of data tables. On the feeding sheet I'd have only a few columns of data. Animal Name, Date, Amount, Type (of food). This is the data that will change and you will work with daily. The other sheet can be filled in once and left along. When Bozo feeds you will record the data on the second sheet. If you need more info about Bozo, you will look at the first sheet.

    I hope this helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Contributor
    Join Date
    05-12-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    266

    Re: Userform to enter data in multiple worksheets

    is this something to start with?

  5. #5
    Registered User
    Join Date
    03-02-2019
    Location
    Sligo, Ireland
    MS-Off Ver
    2007
    Posts
    7

    Re: Userform to enter data in multiple worksheets

    Hi Mumps,
    I have unmerged the cells however I could use some help with creating the userform, particularly which of the tools to use for the various different variables I have e.g. which ones to use for the drop down menu i mentioned previously,the data entry boxes for each variable and of course a save button. Thanks again

    Hi Marvin,
    That was what I originally did in order to create a records database, however it has a number of drawbacks. Firstly, it isolates the individual datasets which means itīs somewhat more difficult to tell any impacts a variable may be having on another so for example if bozo is trained and this was followed by a change in behavior or if a change in behavior was followed decreased food intake etc. itīs much harder to see how one impacts another if the data is separated between worksheets/workbooks. Secondly it leads to an enormous amount of files created (at least for us). For example we have 64 birds of prey so thatīs 64 individual files which each has to be opened, the data entered and saved and closed, thereīs just far to much maintenance involved and possibilities for things to go wrong. Basically, we need to keep all information about an animal all in one easily accessible format so if necessary we can make a quick and informed decision about their welfare. The easiest option would be to use a commercial record system however these are massively expensive and weīre on a shoestring budget. Thanks for your input though, itīs much appreciated.

    Thanks again to both of you

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Userform to enter data in multiple worksheets

    OK. Give me a little time to work on it.

  7. #7
    Forum Contributor
    Join Date
    05-12-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    266

    Re: Userform to enter data in multiple worksheets

    Zookeeper, have you seen my contribution?

  8. #8
    Registered User
    Join Date
    03-02-2019
    Location
    Sligo, Ireland
    MS-Off Ver
    2007
    Posts
    7

    Re: Userform to enter data in multiple worksheets

    Hi SonjR,

    I only seen your contribution after I had posted my previous response. That is exactly what Iīm looking for. I have been working away on the record system for on of the sections here (birds of prey). And I have a massive favour to ask. I have attached a file (very similar to the template but slightly modified) which contains individual tabs with the origin data entered. I was wondering if you could possibly set up a similar userform for it? The main difference is that I have had to add a second, slightly differently formatted worksheet which records data for a group of animals along with the worksheets for individual animals, everything is formatted the exact same way except the overview data is slightly different with the group worksheets (youīll see it within the first few worksheets) and with this version I have also slightly modified the flight record section.
    Sorry to ask you to do this once again, I just donīt have the skill or knowledge to edit the excellent userform you supplied
    Many, Many thanks
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-02-2019
    Location
    Sligo, Ireland
    MS-Off Ver
    2007
    Posts
    7

    Re: Userform to enter data in multiple worksheets

    Sorry, that last post was directed at SjonR (apologies for misspelling your username)

  10. #10
    Forum Contributor
    Join Date
    05-12-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    266

    Re: Userform to enter data in multiple worksheets

    Tommorow Morning i Will try to do it. Takes some time .

  11. #11
    Registered User
    Join Date
    03-02-2019
    Location
    Sligo, Ireland
    MS-Off Ver
    2007
    Posts
    7

    Re: Userform to enter data in multiple worksheets

    Perfect, thanks. I'll continue to work on it and if I find a solution I shall let you know. Although this is definitely unlikely
    Last edited by zookeeper94; 03-02-2019 at 12:40 PM.

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Userform to enter data in multiple worksheets

    @SjonR:
    You've done a nice job on the userform. I would suggest that you delete the "Private Sub CmbAnimal_Change()" macro and place the code in the "Private Sub CommandButton1_Click()" macro as below:
    Please Login or Register  to view this content.
    This solves two probems: The formulas in the "Private Sub CmbAnimal_Change()" macro were reversed and the data was not being copied to the sheet when the "Save" button was clicked. Keep up the good work!!!!

  13. #13
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Userform to enter data in multiple worksheets

    Sorry

    I have just been called to go on an Emergency Callout.

    In my opinion the Approach is all wrong.

    The present approach is totally unmanageable.

    I have mocked up a Master Sheet.

    The Plan Would be to have a Single WorkSheet for Each Species.

    Each Animal would be in 5 Columns Of that Worksheet.

    Most Of The Data In the Yellow Area Would Be in Sheet 2 of the Userform. as it is not needed often

    The Colour Coded Data would be In Sheet1 but would Be presented as Listboxes so Historical Data can be displayed.
    New Data Would be entered using textboxes.

    I will work on the Userform When I get back in approx 3 hours.
    In the Meanwhile please feel free to Fine Tune the attached Master Sheet.





    Ok. I have created an Index Sheet.

    Type "Zebra" in A3.

    Type "Tom" in B3

    Type "Dave" in C3
    Attached Files Attached Files
    Last edited by mehmetcik; 03-02-2019 at 09:45 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  14. #14
    Forum Contributor
    Join Date
    05-12-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    266

    Re: Userform to enter data in multiple worksheets

    I removed the overview part, because the lay-out in the sheets are not the same.

    Let me know if it works for you.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    03-02-2019
    Location
    Sligo, Ireland
    MS-Off Ver
    2007
    Posts
    7

    Re: Userform to enter data in multiple worksheets

    Thatīs exactly what I was looking for, thanks so much. Although the file appears to be corrupted on my end so I had to extract the data which didnīt pick up a lot of the visual formatting (colour, column widths, borders etc). Which means I just have to run through it again and changed those.

    Just to clarify, I can assume I can add and remove sheets without it requiring modification to the userform?

    Thanks again

  16. #16
    Forum Contributor
    Join Date
    05-12-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    266

    Re: Userform to enter data in multiple worksheets

    As long as the added sheets have the same postions, its no problem

  17. #17
    Registered User
    Join Date
    03-02-2019
    Location
    Sligo, Ireland
    MS-Off Ver
    2007
    Posts
    7

    Re: Userform to enter data in multiple worksheets

    That`s absolutely perfect. Thanks again

  18. #18
    Forum Contributor
    Join Date
    05-12-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    266

    Re: Userform to enter data in multiple worksheets

    If i ever visit ireland, i'm pleased to visit your Birds.
    A friend lives in cahersiveen. Is that near you?
    Last edited by SjonR; 03-03-2019 at 03:52 PM.

  19. #19
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Userform to enter data in multiple worksheets

    This is my version of the Userform.

    This is a start on managing the Yellow portion.

    Type a Species Name in Column A of the index Sheet to add a worksheet.

    Type a Name to the right of the Species name to add data for a new Specimen.

    Click on the button on the Index Sheet to see the present version of my Userform.

    I will add functionality to this as time goes by.
    Attached Files Attached Files
    Last edited by mehmetcik; 03-03-2019 at 10:42 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Transfer data from Userform to multiple worksheets
    By nancyching1711 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2018, 08:48 AM
  2. [SOLVED] Allowing Multiple user to enter data from a sinlgle userform
    By rv02 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-24-2016, 04:46 AM
  3. Using a Form to enter data into multiple worksheets
    By JahJr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-10-2015, 12:08 PM
  4. Copy data from userform to multiple worksheets
    By kx1bn7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-25-2014, 12:36 AM
  5. Replies: 0
    Last Post: 02-27-2014, 04:43 PM
  6. Ssaving data from userform to multiple worksheets.
    By K Mahendra in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-11-2014, 09:11 AM
  7. Userform data to multiple worksheets
    By JessFace in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-04-2010, 12:44 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1