+ Reply to Thread
Results 1 to 14 of 14

Using identical ListBox(es) in a form and manipulating the data in the same way too!

  1. #1
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Lightbulb Using identical ListBox(es) in a form and manipulating the data in the same way too!

    Hi All

    I've designed a form (attached) to complete a rota spreadsheet, which uses identical ListBox(es) for rota tasks/times. I have a single list box for names and I can populate each ShiftList using an array. It would've have been neater if I could have used ShiftListX, with X being a numeric variable to cycle through the 7 days of the week, ShiftLIst1, ShiftList2, etc. However, that didn't work, so I've had to replicate any code for each of the days, i.e. ListBoxes. Surely there must be a neater method to work with these ListBoxes, when they are treated exactly the same way.

    I did think about reducing it to a single day and using the form 7 times instead of once.

    My code works but it is so longwinded and I'm struggling to find an easier/less verbose way to do it. Any ideas?

    Many thanks in advance.
    Attached Images Attached Images

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    If your Listboxes use the same code then use subroutines to do the heavy lifting.
    My Rules if you want my help:

    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.

  3. #3
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    1,907

    Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    So that the people you are hoping will help you do not have to try to duplicate your layout and data, post a copy of your actual workbook instead of that picture. You can replace any sensitive data with junk text. See the yellow banner near the top of this forum's window for instructions on how to post a workbook.

  4. #4
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    I try to use Subroutines for all my userforms

    So Say I have 20 TextBoxes

    I write the Subroutines for the 20 TextBoxes using Excel. Then I Copy the code to VBA.


    A1 = ="Private Sub TextBox" & INT(ROW()/3)+1 & "_Change()"
    A2 = ="TBChange("&INT(ROW()/3)+1&")"
    A3 = End Sub


    Copy A1:A3 and Paste into A1:A60 and you have the change code for 20 TextBoxes. Copy Paste into VBA. The Main Code goes into the Subroutine TBChange


    Please Login or Register  to view this content.
    Last edited by mehmetcik; 09-18-2020 at 06:36 PM.

  5. #5
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    Hi Memetchik

    Thanks for the reply. I'm doing the manipulation within the Userform, i.e. the OKButton_Click() sub. I can't find a way of doing that without replication because you have to refer to each ListBox literally. The code basically takes the data from the form into the spreadsheet, formats the text (dependant on the entry) and adds calculations. Nothing too complicated and apart from a Wednesday, every day is identical.

  6. #6
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Lightbulb Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    Hi Rick

    Thanks for that. Yep, I've taken out some of the calculations and left the basic form as is.
    I actually use a calendar form to select the start date and it must be a Monday - that's not included.

    I'm not a coder and only attempt macros when I'm asked for help. Hopefully this will let everyone see what my issue is!
    Looking forward to your suggestions!
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    I hate merged cells, however your query was how to shorten the code to remove repetition.
    Run through the attached I hope it gives you some ideas to work on.
    I have added an extra sheet with your staff & shifts in tables to populate your listboxes.
    Use tables or ranges to populate lists rather than the cumbersome unstable rowsource or additem methods.
    Rather than hard coding your lists this method enables your lists to be dynamic without resorting to constantly altering your code.
    torachan.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    Hi torachan

    That's BRILLIANT and exactly what I was looking for, plus easy to understand.
    What would you suggest instead of using merged cells?
    BTW: the original doc was from my wife's manager, sent as a Word template, so not my design.

    Cheeres, Stuart

  9. #9
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    Stick with it while it works.
    If starting from scratch use a block of two or three individual vertical cells and target their individual references.
    As you are only merging vertically you will have noted your reference cell is the upper of the merged set.
    Instability normally occurs when you have both vertically and horizontally merged cells sometimes VBA gets confused when trying to use the cell.offset location.
    The general consensus is not to mate VBA and merged cells when developing an app for reliability.
    I have attached a link to one of the many I have replied to on the subject of merged cells.
    Open this chaps file and try and escape from some of the merged boxes of cells-the tab and arrows lose their sense of direction and fall over.


  10. #10
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    Thanks Tora

    As I need the "SHOPPING 2HR" to be RGB(12, 72, 24), I unmerged the cells, entered the value and used Offset(1,0) to enter the ShiftList value.
    Although it's not a problem necessarily, when it's a normal shift, I've added:
    With ActiveCell
    If ActiveCell.MergeCells = False Then Range(ActiveCell, ActiveCell.Offset(1, 0)).Merge
    ActiveCell.Value = ShiftList3.Value
    End With
    which works but I get the system warning message about merging cells only keeping the upper-left value and discarding the rest.
    Can I trap this and if so, where do I insert the error trap? I've tried using On Error Resume Next in various (logical, to me) places but without success.

    TIA, DrEcosse

  11. #11
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27
    Quote Originally Posted by torachan View Post
    Run through the attached I hope it gives you some ideas to work on.
    Would using this method work for populating arrays or am I barking up the wrong tree?

  12. #12
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    Hi torachan

    I've added some code to calculate the shift hrs from the text string, added them daily, with their totals inserted in the end column. Again, its cumbersome. Improvements?

    TIA, DrEcosse
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    get rid of sheet 3 - put the hours into column 2 of table 2 - i have added a hidden column to the seven shift comboboxes - this carries the necessary number of hour per shift.
    there is no need for searches or lookups the data is already available by reading the value in the hidden column of the combobox selection.
    please check the summations as i have not checked every combination.
    torachan.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    05-25-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2019
    Posts
    27

    Re: Using identical ListBox(es) in a form and manipulating the data in the same way too!

    Thanks again torachan!
    Last edited by DrEcosse; 09-22-2020 at 06:06 AM. Reason: I found the answer myself.

+ 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] Identical Formulas from Identical Data Sets Return Different Results
    By EverClever in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-28-2018, 06:56 PM
  2. Replies: 3
    Last Post: 01-18-2016, 10:25 PM
  3. [SOLVED] Manipulating range from Userform's listbox?
    By GIS2013 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-23-2014, 01:56 PM
  4. [SOLVED] Manipulating range from listbox
    By GIS2013 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2014, 12:01 PM
  5. Manipulating Dropdown List in IE using VBA & Form Submit oddities
    By JScottArnold in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-30-2013, 05:43 PM
  6. Populate data in listbox in a form
    By fatalcore in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2012, 09:42 AM
  7. creating/manipulating form controls placed on a spreadsheet in excel using vba
    By jeff.naifeh@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2005, 10:05 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