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

[DrEcosse]

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.

[mehmetcik]

If your Listboxes use the same code then use subroutines to do the heavy lifting.

[Rick Rothstein]

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.

[mehmetcik]

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.

[DrEcosse]

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.

[DrEcosse]

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!

[torachan]

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.

[DrEcosse]

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

[torachan]

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.

https://www.excelforum.com/excel-gen...ml#post5122447

[DrEcosse]

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

[DrEcosse]

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?

[DrEcosse]

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

[torachan]

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.

[DrEcosse]

Thanks again torachan!