+ Reply to Thread
Results 1 to 25 of 25

Userform entry to output into merged cells including nextrow outputs

  1. #1
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Userform entry to output into merged cells including nextrow outputs

    I have a userform that is being used to input data for scheduling purposes. I'm about to link up the textboxes and comboboxes with their destination cells but a majority of them are parts of merged cells. The ros they will be going into are also merged. The purpose for this is due to user needs so I'm unable to change this. Is there an easy way to do this? Also, I'm not great with syntax so I've included the demo of the form.

    Thanks in advance for the assistance!

    Demo (NEWBuild).xlsm
    -Orestees

  2. #2
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    Monday Bump. Anyone able to take a look?

  3. #3
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    Desperate for assistance bump. Anyone out there?

  4. #4
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    Tuesday bump. . . still desperate for assistance.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform entry to output into merged cells including nextrow outputs

    What exactly do you need help with?

  6. #6
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    Thanks for dropping in Norie, glad you asked.

    I haven't been able to find any resources to assist me with saving data from a userform into cells that are merged. The other part of this issue is that I'd need the next entry to be placed in the next row but since the first row is a merge of A1:A4, the next row would be A5:A8. I have no idea how something like this would be coded. I've fumbled around with different things but I'm still very much a novice. Any help is greatly appreciated.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform entry to output into merged cells including nextrow outputs

    Orestees

    I might be able to help but you'll need to explain the userform a bit and perhaps post some sample data or instructions of how to use the form.

    To refer to merged cells you usually using the top left cell.

    Offsetting can be a bit strange as VBA treats the merged cells as one cell.

  8. #8
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    Norie,

    The TrainingSchedule userform would come up first, allowing the user to either add a new week to the workbook or edit an entry from a week that's already been created. I'm currently working on the editing part (pulling data back in using the the top left as the cell value) but I'm hung up on the data entry part.

    Essentially this is a schedule builder but since the user requires a more visual method of reviewing the data, I was forced to use merged cells. I've made several forms for ouputing data entered into a userform, but this is the first time I'll have to deal with merged cells. How would this work while also having the ability to offset for each new entry exporting into the next empty row?

    Thanks again,

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform entry to output into merged cells including nextrow outputs

    So what should I do to test the form out?

  10. #10
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    The form itself is completely usable, the only thing that hasn't been done is any coding for sending the data to the corresponding area in the sheet. You can skip the TrainingSchedule module and go straight to the AddNew module. The instructor is selected from the first combobox, the M-S command buttons are used to show a day off, the Trainees are in comboboxes (up to 3 people), the 'No Pref' command button allows for free scheduling and the start times and lunch times are adjustable. Once the week has been created, the Add Schedule command button would be the click event to place all of the data into the active sheet (this would be selected with the TrainingSched userform).

    The Enter & Add for New Instructor can be forgotten, I've actually removed it from my new version.

    Thanks again, sorry I didn't really explain myself in the beginning. I appreciate your patience with me Norie.

    Please let me know if you have any other questions.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform entry to output into merged cells including nextrow outputs

    Orestees

    This is your form, I've no idea what I'm supposed to do with it.

  12. #12
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    Norie,

    Here's an edited version. Hope this makes more sense.

    Demo (NEWBuild).xlsm

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform entry to output into merged cells including nextrow outputs

    The label and data help a lot.

    I'll have a closer look tomorrow - getting late here.

  14. #14
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    Thanks again for your time Norie.

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform entry to output into merged cells including nextrow outputs

    Accidental Duplicate

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform entry to output into merged cells including nextrow outputs

    Orestees

    I've been looking at this but there seems to be pieces missing and things that don't quite add up.

    For example, below each trainee you seem to be entering No Pref, Start Time or Lunch Time but there's only room on the form for one selection each day.

    Also there's code like this, repeated for each day.
    Please Login or Register  to view this content.
    LunchHrsS is a textbox on the form, textboxes don't have an Activate property.

    PS Sorry for not getting back earlier - internet connection problems.

  17. #17
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    Norie,

    Since the (up to 3) trainees will be with one instructor, they would all follow the same schedule. For example on Monday, trainee 1, 2, and 3 are with instructor 1 and the user would either have no preference for their schedule or would want them all to come it at 7am and have lunch at 10am. The special notes would also apply to that entire group for that particular day.

    As far as the GotFocus codes, I got those from another workbook someone else made and I didn't realize they could be left out. I've removed them from my version of the workbook. Thanks for the heads up on that, I'm still learning about syntax and properties and have a very long way to go.

    I did have a question about the start and lunch times though. I only have a single cell for both of those inputs in the worksheet; would there be a way to combine them or would I have to add a separate cell for the minutes than from the hours? If there's a simpler way to handle this, I am all ears and really appreciate the help and feedback.

  18. #18
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform entry to output into merged cells including nextrow outputs

    You can combine the values from the minutes and hours textboxes to give a real time value with the VBA function TimeValue

    That will return a single value that can go in the cell.

  19. #19
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    I'll start researching that now so when this first part is good, I can finish the time values as well.

    I tried a few more forums but still can't figure out how to get the first part accomplished

  20. #20
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform entry to output into merged cells including nextrow outputs

    What have you tried so far?

    I've tried a few things, see the attached.

    Still don't quite understand the No Pref, Lunch, Start thing though.
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    Wow Norie, the work you did should give me everything I need for the data entry part. I'll work with the remainder of it. Thanks for even doing the full week, that's very helpful!

    As far as the No Pref, sometimes the user will want an instructor and their assigned trainees to come in at 10am and go to lunch at 2pm, which would be used with the spin controls and the minute comboboxes. When the time of the shift doesn't matter, they could select no pref which would block out the times, allowing me to decide when to schedule them.

    This was a huge help. I REALLY appreciate all of your time and patience with me.

    After I finish this, it's on to recapturing the data into the form to edit prior entries.

    Thanks again!

  22. #22
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform entry to output into merged cells including nextrow outputs

    Glad it's working.

    Still don't understand the No Pref thing though.

    On the form you can enter the start/lunch times or hit No Pref for each day, but where does the data go?

    I think I can see how to deal with the No Pref, but what if a start time and a lunch time have been entered?

  23. #23
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    The user would decide if there's no pref (no schedule preference) and the times would be blank. If they have a preference, then the NoPref cell would be blank or the value would be 'Pref.' Either way wouldn't be much of an issue.

    I love the way the array coding works. I really have to learn about this, it'll make my prior work MUCH more efficient. Thanks again!

  24. #24
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform entry to output into merged cells including nextrow outputs

    Orestees

    You might want to change the names of the No Pref controls.

    Instead of suffixing them with a number, suffix them with the day.

    I made that change to the Special Event controls but forgot to mention it.

  25. #25
    Forum Contributor
    Join Date
    07-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    142

    Re: Userform entry to output into merged cells including nextrow outputs

    Good idea, thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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