userform with looping code

Can someone look at the worksheet and see if this is possible?
As one of my other posts shows, I can get the Lines to show up 1-18, as opposed to 51-67,73, and I am fine with that, as I can add a "helper" column and get the matches I need, but I am not sure how to perform the looping?

And also was curious how to get the Zones to show up?

I have the design made, and an example so you can see, what I am trying to accomplish...
can someone point me in the right direction, I have googled everything, I could think of.

I attached another copy to the original thread, maybe that works. It opened for me.

It untested but modified from similar mikerickson code

Sub TextBoxesValues()
    Dim xCell As Range
    Dim xControl As Control
    Dim xRow As Long, xCol As Long
    Set xCell = ThisWorkbook.Sheets("Sheet").Range("C2")
      xRow = 2
    For Each xControl In Me.Controls
        If TypeName(xControl) = "TextBox" Then
            
              With Sheets("sheet1")
                xControl.Value = Cells(xRow, xCol)
              xRow = xRow + 2
              If xRow > 10 Then
              xRow = 2
              xCol = xCol + 2
              End If
            End With
            
        End If
    Next xControl
End Sub

Not to sure what your objective is but,

And also was curious how to get the Zones to show up?

Try this in B3

=IF(OR(MOD(ROW(),2)=0,B$1=""),"",INDEX(LOG!$A$2:$D$1000,MATCH($B$40&MID($A3,FIND(" ",$A3,1)+1,255)&MID(B$1,FIND(" ",B$1,1)+1,255),LOG!$A$2:$A$1000&LOG!$B$2:$B$1000&LOG!$C$2:$C$1000,0),4))

Confirm with Ctrl+Shift+Enter not just Enter

Drag Across and then Down to suit your table.

I have used conditional formatting on your sheet "User Form example" to put borders around the results

Hope this helps

[EDIT]
There are none so blind as them that cannot see!..
Just noticed your actual Userform, still the formula might be useful if you intend keeping the sheet "User Form example" as a holding template, (It could be hidden).

ok, so I am not sure if the code is inserted properly, but I have modified the userform to at least show up, I have the tab order corrected and the buttons working properly, now it appears the code is not working as I desire, any thoughts?

I really do appreciate your insight Marcol, not sure if it will help me in this scenerio, but definately going to keep it for reference.

Hi 00Able,
I'll need more of a detailed explination on exactly what happening?

ok basically I have 18 lines and each line has 5 sections, I will call them zones. I want to input specific information for these zones into the txt boxes on the user form, and have it show up in the log, as demonstrated on the example log. So if on line 51, zone 1 I input "A", on line 51 zone 2, I input "B" and put nothing else on the user form except the date "2/12/2011 it would show up in the log like.

2/12/2011 51 1 A
2/12/2011 51 2 B

and the rest would be left either totatlly blank or just column D would be blank, either way would work.

Try this workbook.

Because VBa cannot handle an array of controls like full VB you have to be more careful with your control names and their layout.

I have corrected some names and positions in this workbook, the code will fail in your originally posted workbook.

I have left my original formula in this workbook, change the date in B40 to see how it works.

There is also code that can be used to populate your userform on activation, this could possibly be used to call a similar previous order that might be faster to amend and give a new date than fill in the lot from scratch.

Is this what you are after?

I think that it is pretty cool that you can do this, however, this is performing the task completely opposite as I desire. I want to enter the information into the userform, and it to put that data into the Log.

Perhaps you should look at the file again, it does exactly as you ask....

Open the workbook with macros enabled and run the macro "Main"

Have you altered the code in any way?

Perhaps you should look at the file again, it does exactly as you ask....

Ok I looked at it again, apparently I wasn't wearing my glasses and didn't drink my coffee prior to analyzing this. It appears to be working very closely to what I am looking for. I do have some issues.

I went through my last updated file, and changed the tab orders and names of those txt boxes, so they would go from left to right, then down left to right, then down, it took me a very long time, as this was tidious work. So I went to copy the code from your workbook over and I am getting errors, I am not sure why, but I believe it has something to do with what you said here, but I am not sure what you did differently.

I have corrected some names and positions in this workbook, the code will fail in your originally posted workbook.

I will attach the workbook so you can see what I mean, but here is the line I get the error on.

If Controls("tb" & strTbGroup & c) <> "" Then

It appears if I enter anything from "Line 56 to Line 73" will not show up on the log?

Also you said,

There is also code that can be used to populate your userform on activation, this could possibly be used to call a similar previous order that might be faster to amend and give a new date than fill in the lot from scratch

,
How do I do this, as this will be very useful?

I can't look at it just now, I'll try to have something for tomorrow.

A couple of questions

!/. Do all of the textboxes need to have a value before the order is added to the Log?

2/. If not how is the data entered, should it be all the Lines including blanks, or do we skip blanks?

3/. I am assuming each order is appended to the Log and it does not overwrite old orders and it is not on a new sheet each time.


Hang in there, we have been discussing a solution for your situation and it is not far away.

If you answer the above we will get there sooner.

Cheers

[EDIT]
You haven't arranged and named your Labels and Textboxes as in my example, that is why the code fails, the order and naming is vitally important in this case, as I explained earlier.

I have been looking back and forth at the code, to see how you have yours arranged and named, and I am not seeing any noteable differences, the only thing I notice is really the "tab order", but for efficiency and accuracy, I need to have them go from left to right, so I will be entering all the data for each zone on Line 51, then 52, then 53, etc..

So to answer your questions:

1. Do all of the textboxes need to have a value before the order is added to the Log?
   Answer: No, some text boxes will be blank
2. If not how is the data entered, should it be all the Lines including blanks, or do we skip blanks?
   Answer: We can skip blanks, as the code currently does.
3. I am assuming each order is appended to the Log and it does not overwrite old orders and it is not on a new sheet each time.
   Answer: Yes, your assumption is correct.

Okay, try this, it's a bit faster than the old code.

With sheet "User Form Data"
1/. If you want to use earlier data as a template select a date from the dropdown in B40, otherwise leave blank.

2/. Click on D40 to get the userform, if you are using a template you will be prompted to confirm.

3/. Remember to change the Date to a value that is not already used before entering a new order, otherwise the data will be confused and return the first instance of any line for that date.

This weakness can easily be cured but first we need to know.
1/. Can there be more than one order in any one date?

2/. If so, how do we identify each entry?
A unique Order Number auto-generated is the easiest way, but you may have numbers issued from a different source, again, if so what are they like, and where would you enter them in the UserForm and the Log?

As I said in Post #9 you must be careful not to change the names for the controls and their position on the form.

Your Labels for "Line ##" are out of sequence, they must be ordered by their name, not the caption. The label you are using for "Date" is Label6 this label should be renamed, say LblDate, and your Labels for "Line ##" should be renamed in sequence Label1, Label2....etc top to bottom, read Post #9 again.

I haven't looked at the rest of your new file yet, lets get this stage sorted first.

Ok, I have reviewed the file. I think it is absolutely brilliant. I will say, I have no idea of how that code does the recall, but it is genius. And, I finally understand what you were saying about label order (i think)...

Before we proceed any further, I do have one question:

Would it be possible to recreate this scenerio using comboboxes instead of textboxes? I was thinking, every box will have the identical list to choose from, so if I had one dynamic named range, could I easily attach the range to each combobox? This would help any typos or misprints as well and also keep data entry efficient. Or is there another way to perform a similiar task?

I will attach a file to illustrate what I mean, I put some code (see the Green), in the UserForm , but it will not work in this instance, (the named dynamic range "ItemList", is included in the sheet "LookUpLists") I am hoping it helps explain what I want it to do...

Now to answer your question:
Can there be more than one order in any one date? No

Anyway, I realize what I ask may not be possible, I am just not sure.

Okay I'll amend to only allow for one order to be entered in any one day.

As regard comboboxes or list boxes instead of textboxes, yes it is possible, but are they really needed?

In my opinion this project would be much better using worksheets, and a minimum amount of VBa, instead of a User Form, that way would be much easier and faster and you could have as many drop-down list as you want.

Can't do more tonight, look back tomorrow late on (say after 20:00 GMT)

I want to apologize if it seems I am changing the scope. I am going to try to put a "Big Picture" together, so you get a full understanding of the project. As we have been tackling, several different realms.

I think you may be on to something, when you say using worksheets, and I think you may know of a better approach then what I can think of. I just want to make this project efficient and easy to use. I will try to capture all the data to show you what I am working with. I am afraid though, that the file might be too big to post, but we will see.

I will try to have it done by tomorrow evening.

Okay, I'll put this on hold until you get back to the forum, in the meantime here's the amended file restricting date entries to one per day.

I have restricted the enty of data to "/" and 0 to 9 so there should be less problems with date formats, the cell will be highlighted if the entered data is found in the log. The programme will not procede until the date is fixed.

There will doubtless be more to consider as the project develops.

Cheers

Ok, I used your recent file, and updated it try to show you what I am trying to accomplish...the real world setting. I have copied all the following information to the workbook in the "Log" worksheet so you can read it while you are looking at the layout. I have made some alterations but I have tried to explain everything.

I originially wanted to have 2 userforms, one with text boxes & one with combo boxes. To enter the information for column's D and E, "Employee Name" & "Production #'s" respectively. After thinking about it, I guess just one userform using comboboxes (for the employee name) will work the best, as I can enter the "unit" information manually on this page without too much hassle and doing that will also assist in making sure I am entering it correctly.

The reason I want combobox's is that I am working with 90 employees in this instance, and some names being foreign, need to have a "-" in a certain spot, and everything has to be typed correctly or I will not be able to sort the data properly, which is currently my number one issue. So I moved the "UniqueDateList" to column S, and created a "NameList" in column T, where I can input all the current employees and call this information to the comboboxes.

*Note: I tried to use the Data Validation List function, but it doesn't help as the list is extremely long, and it will not finish what you start typing, so it is not efficient. For illustrative purposes, See cell D2.

*This is why having that "recall" feature is really valuable, because often employees are in the same spot, but not 100% of the time, as people call off, are moved to other lines, quit, vactaion, etc.

The rest of the sheet is going to be calling information from other workbooks (columns F-R), so that information will not have to be put on the userform. So my biggest hurdle right now will be comboboxes.

I have added another userform "UserForm2" with comboboxes to this workbook, but the code will not work as it is from your initial worksheet, not even the updated code. But I believe I have the labels named properly as you have previously mentioned.

*also I added a Reset button, but the code I used will not work, so I erased the code, any ideas how I can have this clear the form?

Updated previous thread to state: "have added another userform "UserForm2" with comboboxes to this workbook" as it will not show that userform, as it is not active yet

I might be a step behind you, had to do some work ....

Have a play about with this workbook, there are no userforms involved.

With Sheet1
1/. Select a date from the drop-down in D21
The table should change to the rota for the date selected.

2/. Either clear the date or enter a new date and follow the prompts

3/. When you select a cell in the table (B2:F19) the available names will appear in the validation drop-down, cells are yellow until a name is selected, when a name is selected, it is removed from the next selected cells' drop-down.

You probably will not need a separate sheet for each month, the same result can be achieved by filtering Sheet "Log", we can slick that up a bit if desired.

This is not bullet proof at the moment, so make a list of bugs and we can fix them if you want to go this way.

I haven't used a calendar at present but it should be easy to add if really needed.

Have fun.....

I will put it to work and let you know. Thanks!

Here are some of the issues that I have encountered:
Also note at work, I will be using excel 2003, so I may need to combine the "Lookups" & "Sheet1" worksheets together, in order for the data valadation to work properly, but I am experiencing the following issues using excel 2010.

1. Changing worksheet "Sheet 1" name. I attempted to try to change the name to "Input Attendance" and then went into the code and changed "Sheet 1" to "Input Attendance" in the spot that I seen it, but it didnt seem to work.
   
2. Changing the employees names in the "Lookups" sheet. I tried to paste over the names in the list using the real life employees and everytime I do, it will allow me to pick two names and then the drop downs no longer work. The name range is still the same so I am not sure why this would be an issue? All I am doing is pasting data over the existing names. But as the company has a large turnover rate, we will need to update this list frequently.
   
3. I need to delete all entries in the Log, so that I can start from scratch, and I am experiencing some difficulties doing this, by using the "amend rota" button. I have also tried to just delete the records straight from the log.

Until I get this fixed I wont really be able to tell what other bugs there are.

1/. If you change a sheet name you wiil have to change the code throughout, Find and Replace in VBa is the easiest way. (Any forrmulae will take care of themselves)

2/. I think I have found the bug when changing the Names List and it should be okay now
To change the names in the lookup list change or paste in whatever you need, make sure the list is continuous, don't leave any blank cells in the list. Sort the list alphabetically if you wish.

If the list has less than 90 names you will not be able to fill the entire table, if there are more than 90 then you will have some left in the drop-down list when the table is filled.

I have added conditional formatting to make the text turn red if a name is deleted from the list but is still in the Log ( This will only work for 2007 on as it stands)

3/. I assumed you would not want to delete the entire log on a regular basis, but if you do just clear all the data except the headers. Avoid deleting rows I haven't addressed that scenario and it could well break the Named Ranges


This will still need debugging, so please check things step at a time so that errors can be easier to trace.

I will check it out in 2003 when I get the chance, but I don't have access to a machine at the moment. I don't think you need combine the sheets, the validation lists are VBa generated, but I will test in 2003 later.

Have fun....

I appreciate your insight, I will test it as soon as I can, but I like you do not have access to 2003, until I go to work...they really need to upgrade. Unitl then I will test with this machine.
I also wanted to point out that your mailbox is full, as I tried to reply back, but was not able. Thanks again.

Still having issues with the names, when I go to choose a name, it shows the full name as 2 seperate names. I am assuming, it is reading the ", " as a space?

So Manning, Peyton, is showing up in the data list as "Manning" and then on a seperate line as another option is "Peyton", not sure why.

There were no names on your list separated by a comma so I built the diminishing list based on this, ie a comma separated string.
If you start a new table the first list will be okay, as it reads from the named range "NameList", however subsequent drop-downs will be split at every comma. I'll look at this later tonight to find the best way around this situation.

Don't be tempted to think it would be easier to use combo-boxes, the same problem would arrise with them but 90 times over. I really miss the ability to use control arrays as in full VB, I feel it is a feature that VBa would really benefit from.

Hang in there, I have found a laptop with 2003 installed, don't knock 2003 it's pretty good and its limitations can be a real advantage, later versions allow the user more "gimmicks" and some great new functions, but some are a real headache when using VBa, keep it simple should be your watch words.

P.S. Mailbox cleared, thanks for letting me know.

There were no names on your list separated by a comma so I built the diminishing list based on this, ie a comma separated string.

I was actually thinking the same thing, funny how I cant sleep trying to figure out why it wouldnt work.

I was thinking about taking the names out of the comma type, as it might be easier for users that don't know someone's last name. So is there a way we can attempt it using both methods, in the same vb?

I learned excel on 2007, so everything is much harder for me in 2003, just finding the features is a pain...also, when I take work home...everyday, and take it back to work the next day...it does not work, because of the newer functions, that I am used to. So it is learning curve to try to adapt in both environment that constantly gives me a headache...but its better then pen and paper and calculator...lol

Still haven't fired up the 2003 m/c however give this a try.

I have dropped the string lists from the data validation and substituted dynamic named ranges for available employees.
This is backed up with code to keep the lists clean.

You should now manage to use commas in their names.
We might have problems with employees that have the same name. For now at least try to give them unique names, e.g. R. Smith, Rob Smith, Robert Smith, Bob Smith, R. J. Smith... etc.
Long term it would be better to use their works number and don't reuse that number again when someone leaves.

We'll get there soon....

Ok, the names are working fine, I would like to be able to put the names in multiple fields...

Here is an example:
Line 52 is not running. So no one is working on that line. Line 51 breaks down at 10:00am, so we move those employees to Line 52. An outside program is going to generate production values for all the lines automatically, so it will show 4 hours of productions on line 51, and 4 hours of production on line 52, it will be easier for me to adapt by entering the same names on those line, then to try to change this outside program.

I didn't think of this scenerio until I just entered numbers for Friday. And this was something that came up.

Yes, we would not need the diminishing validation lists, in this case...

If we can allow more than one entry per day then we could flag the log to indicate shifts or break-downs/down time, then produce tables to indicate each scenario

I am thinking that although I agree there could be some benefits to your ideas, it would be unnecessary, as duplicating this file would be just fine. As another user would ultimately be in control of filling out the data for their respective shift, and I would rather restrict their access to just one shift's information.

1/. How many shifts do you have in a working day? 22/. Are all lines running at any one time? Sometimes but usually not

Other Ideas

• Could we move the "Input Attendance" form down to row 500
• Hide the first 500 rows, so it appears that the Input Log is at the top of the sheet, and then in each Column have the cells automatically reference the names inputted into the "Lookups" NameList? Remember this list will change weekly so it will have to be versatile, and there will never be more than 500 employee names on that list.
• We could then hide the Gridlines, and Headings on this worksheet.

That way when I started typing them they would be there? I mention this option for convience of the data entry, but I also like the drop down as well, as maybe the name is unknown or unrecognized. I do not want to get rid of the Lookup sheet, as it will be convenient to change where it is. Just a thought, what do you think?

Also this another small issue, I dont know what "Rota" is can we change this to "Entry" or "List"

"Rota" is perhaps "Roster" in your part of the world.

Why complicate things?
Use old fashioned paper and pencil and draught out your ideas, draw bubble diagrams..etc and then see if you can see where I am coming from.

With respect, now I see your problem, I have dealt with this type of problem for some 40years, long before Excel was invented, unless of course you have an early prototype called "Excelsious" used by the Roman Empire.

Thats hillarious, wonder how my boss would take that...maybe I will draw one up and hope to catch him on a good day...lol

Yeah I have been trying to accomodate all the data for both shifts, but I want to delegate that responsibility to someone on their own shift, as it will be more accurate, as I have no idea when someone goes home sicks, or if there is a outage.

hi 00Able
I take it you thought marcol solution was a viable to the original problem, or at least a manageable one.
It may be time to start a new thread with additional questions that will relate to the title?

I have taken your advise Pike, and started a new thread. I was challenged with a title, as vb outside userform, is alien to me, and to be honest, this project is outside my comfort zone, and way past my "novice" knowledge.

Layout Change of Roster Input VB is the new thread...