PDA

View Full Version : New project



Spaggiari
07-24-2012, 07:22 AM
Hey all this is my first post on this forum!

I'm doing a new project with the objective of saving data from a userform on a sheet and afterwards making graphs with the saved records.

This project is supposed to work on a factory environment with the operators filling and selecting the info in the end of their shift from a form such as their name, the shift they're on, the machine they're working with, the product they're making, what events occurred that made them stop the production during their shift and how long those events lasted, and so on.

There are also several forms to add or remove operators, products, machines and events into a sheet used as a database.

This is what I've done so far but still I have some issues I'd like to solve.

First things first, I'd like to make the database dynamic, as in using dynamic ranges because they're all fixed named ranges at the moment, I've tried but had no luck so I opted for the fixed range but now I need it to be dynamic.

In second place after the dynamic named ranges are set I'd like to assign the combo boxes and list boxes row sources only with the items within the range, no blanks.

Third place, in the Main form when selecting an item from the combo box products It would autocomplete some labels with data associated with that product.

I don't think I can do anything without making the ranges dynamic first because its all related I guess.

Thx in advance!

Tinbendr
07-24-2012, 01:49 PM
Welcome to the board!


, as in using dynamic ranges Here's a good article on dynamic named ranges. (http://www.ozgrid.com/Excel/DynamicRanges.htm)

I'd like to assign the combo boxes and list boxes row sources only with the items within the range, no blanks. In that case, you'll have to remove the rowsource and iterate the named range and add it to the combobox.

For Each aCell in Range("MyRange")
if aCell.Value <> "" then
me.combobox.additem aCell
end if

Third place, in the Main form when selecting an item from the combo box products It would autocomplete some labels with data associated with that product. Use the combobox change event to process other controls.

Private Sub Combobox1_Change
if Combobox1 = "This Item" then
me.label1.caption = "This Item"
end if


I don't think I can do anything without making the ranges dynamic first because its all related I guess. All this can be done WITHOUT named ranges.

Spaggiari
07-25-2012, 07:31 AM
Welcome to the board!









Here's a good article on dynamic named ranges.
In that case, you'll have to remove the rowsource and iterate the named range and add it to the combobox.

For Each aCell in Range("MyRange")
if aCell.Value <> "" then
me.combobox.additem aCell
end if
Use the combobox change event to process other controls.

Private Sub Combobox1_Change
if Combobox1 = "This Item" then
me.label1.caption = "This Item"
end if

All this can be done WITHOUT named ranges.








Thx for the reply and for the welcome!

This is the code I came up with after some time playing around.

So I managed to insert new operators into the database sheet and sort them like I wanted, I had a problem sorting because the next column was beeing sorted as well even tough I'd only selected the correct range! The problem solver here was leaving the next column, "B", alone and voilá

Furthermore i added some more code to avoid numeric and nullstring entries but i think it needs some more work.

Afterwards I tried to assign the rowsource to the listbox but I got an error, dunno what I'm doing wrong there some help would be nice

You can check all of this in the attached file, in the Index sheet press Utilities > System > Operators

Cheers!


Dim wb As Workbook
Dim wsDatabase As Worksheet
Dim rngOperators As Range
Dim lastRow As Long
Set wb = Workbooks("test.xlsm")
Set wsDatabase = wb.Sheets("database")
lastRow = wsDatabase.Range("a" & Rows.count).End(xlUp).Row
Set rngOperators = wsDatabase.Range("a2", "a" & lastRow)
Dim text As String
Dim insert As Integer
Dim verify As Integer
verify = 0
insert = MsgBox("Insert?", vbYesNo + vbExclamation, "v1.0")
text = usfOperators.txtOperator.Value

If insert = vbYes And IsNumeric(usfOperators.txtOperator.Value) Then
MsgBox "Insert a name not a number!", vbCritical, "v1.0"
usfOperators.txtOperator.Value = ""
usfOperators.txtOperator.SetFocus
verify = verify + 1
Exit Sub
ElseIf insert = vbYes And text = vbNullString Or text = " " Then _
MsgBox "Field is empty!", vbCritical, "v1.0"
usfOperators.txtOperator.Value = ""
usfOperators.txtOperator.SetFocus
verify = verify + 1
Exit Sub
End If

If verify = 0 Then
With Sheets("database")
Cells(lastRow + 1, 1) = text
MsgBox "New operator was added!", vbInformation, "v1.0"
End With
Selection.Sort Key1:=Range("a2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
usfOperators.txtOperator.Value = ""
usfOperators.txtOperator.SetFocus
End If

Tinbendr
07-25-2012, 10:34 AM
Afterwards I tried to assign the rowsource to the listbox but I got an error...Just blot out references to rowsource. Unless the data will stay static (even just sorting in place), then rowsource is a headache to deal with.

See attached and see if we're heading in the right direction.

Added dynamic named range for Operators and Machines.
Removed all rowsource references in comboboxes.
Fill operator combobox on startup.
Fill machine combobox on startup.
Fill ProductCode combobox on startup.

When selecting Product code, it changes captions to desc1, feature 1-3.

Also, when referring to a specific problem, range, worksheet, etc, please fill in the data range with sample data. I don't care how obvious it is to you.

Spaggiari
07-27-2012, 03:29 AM
Just blot out references to rowsource. Unless the data will stay static (even just sorting in place), then rowsource is a headache to deal with.

See attached and see if we're heading in the right direction.

Added dynamic named range for Operators and Machines.
Removed all rowsource references in comboboxes.
Fill operator combobox on startup.
Fill machine combobox on startup.
Fill ProductCode combobox on startup.

When selecting Product code, it changes captions to desc1, feature 1-3.

Also, when referring to a specific problem, range, worksheet, etc, please fill in the data range with sample data. I don't care how obvious it is to you.

Hi David first of all thank you very much for your help, this is what I wanted, we're definitely on the right path here.

I'll set the other dynamic named ranges using your formula (shifts,events).

The caption change on combo box selection is working like a charm.

Combo boxes are working good too, the items listed only return non blank values.

I used the same formula to set the dynamic named range for shifts and then I added the code to iterate the named range shifts in userform Main Initialize Event, all went fine I was getting an error at first but then I remembered I had to delete the RowSource value on the combo box :thumb

Next I will work on userform Operators, I guess the listbox can be filled in the same way, by iterating with the values from sheet database. I bet I'm gonna have a headache before lunch on the Add/Remove buttons though!
I'll keep updating on this! Ty once again :beerchug:

Tinbendr
07-27-2012, 04:02 AM
Next I will work on userform Operators, I guess the listbox can be filled in the same way, by iterating with the values from sheet database.
Correct.



I bet I'm gonna have a headache before lunch on the Add/Remove buttons though!

Just chip away at it. You'll get there.

Spaggiari
07-27-2012, 04:22 AM
Correct.



Just chip away at it. You'll get there.

I'm on it!

Btw 1 column dynamic range is working, how about 2 or 5 column? Can it be made?

Lunch time now!

Tinbendr
07-27-2012, 04:46 AM
Btw 1 column dynamic range is working, how about 2 or 5 column? Can it be made?

You don't HAVE to create a dynamic range to fill a combobox. You can
With Worksheets("database")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For Each aCell In .Range("A2:A" & LastRow)

'Change Column Letter as required.

Spaggiari
07-27-2012, 05:59 AM
You don't HAVE to create a dynamic range to fill a combobox. You can
With Worksheets("database")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For Each aCell In .Range("A2:A" & LastRow)

'Change Column Letter as required.

I realize that, I just didn't explain myself very well.

For example, if i want to sort a range like products, that range has 5 columns right? So can i make a dynamic range with those 5 columns?

Spaggiari
07-27-2012, 06:54 AM
I made a sample file to test if some of the code was working but I get an error 1004, Unable to get Match propoerty of the WorksheetFunciont class.

Cheers

Spaggiari
07-27-2012, 07:14 AM
I made a sample file to test if some of the code was working but I get an error 1004, Unable to get Match propoerty of the WorksheetFunciont class.

Cheers

Wrong file this is the correct one!

Tinbendr
07-27-2012, 11:40 AM
iRow = Application.WorksheetFunction.Match(CInt(Me.ComboBox1.Value), Sheet1.Range("numbers"), 0)

Had to convert combobox value to integer, plus I forgot match type at the end. (0 = Exact match)

Spaggiari
07-30-2012, 03:24 AM
iRow = Application.WorksheetFunction.Match(CInt(Me.ComboBox1.Value), Sheet1.Range("numbers"), 0)

Had to convert combobox value to integer, plus I forgot match type at the end. (0 = Exact match)

Hey again David, hope your weekend was better than mine! The code is working just fine.

Now for me more errors...

When adding a new operator:

1- The textbox is empty I press Add, then I get a prompt, if I press No it tells me it Added something.

2- When I add or remove an operator it doesn't update the listbox.

3- The code i'm using to sort/order is not working either, it returns me an error 1004 "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."

Selection.Sort Key1:=Range("Operators"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

So far so good, the code I have is working for adding and removing just need some more tweaks!

Could you have a look at it and tell me what you think about it? Ty

Spaggiari
07-30-2012, 04:31 AM
Re-uploaded the file seems the last is corrupt or something.

Ty

Tinbendr
07-30-2012, 05:21 AM
You just have to add it to the list box.

'Add to listbox
Me.lstOperators.AddItem usfOperators.txtOperator.Value

But it seems to want to resort the list for viewing, so I would just clear the list and refill it.


me.lstoperators.clear
With Worksheets("database")

'range ("Operators") is selected and sorted from A to Z
.Range("Operators").Sort Key1:=Range("Operators"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

For Each aCell In .Range("Operators")
If aCell.Value <> "" Then
Me.lstOperators.AddItem aCell.Value
End If
Next
End With

To remove a item from the list.
Range("Operators").Rows(i + 1).Clear
.RemoveItem .ListIndex

And you're Yes/No is failing anyway. You might try a simpler test.

If Me.txtOperator.Value <> "" then
Sorry, out of time. Off to work. Will check back later.

Spaggiari
07-30-2012, 07:35 AM
You just have to add it to the list box.

'Add to listbox
Me.lstOperators.AddItem usfOperators.txtOperator.Value

But it seems to want to resort the list for viewing, so I would just clear the list and refill it.


me.lstoperators.clear
With Worksheets("database")

'range ("Operators") is selected and sorted from A to Z
.Range("Operators").Sort Key1:=Range("Operators"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

For Each aCell In .Range("Operators")
If aCell.Value <> "" Then
Me.lstOperators.AddItem aCell.Value
End If
Next
End With

To remove a item from the list.
Range("Operators").Rows(i + 1).Clear
.RemoveItem .ListIndex

And you're Yes/No is failing anyway. You might try a simpler test.

If Me.txtOperator.Value <> "" then
Sorry, out of time. Off to work. Will check back later.

The Add button is working the code you gave me worked just fine, it adds the value into the list box and sorts the values like i wanted.

If verify = 0 Then
Me.lstOperators.Clear
With Sheets("database")
Cells(lastRow + 1, 1) = text
For Each aCell In .Range("Operators")
If aCell.Value <> "" Then
Me.lstOperators.AddItem aCell.Value
.Range("Operators").Sort Key1:=Range("Operators"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End If
Next
usfOperators.txtOperator.Value = ""
usfOperators.txtOperator.SetFocus
MsgBox "Added!", vbInformation, "v1.0"
End With
End If

I added one more ElseIf to my Yes/No seems to be working now:


If add = vbYes And IsNumeric(usfOperators.txtOperator.Value) Then
MsgBox "Insert a name not a number!", vbCritical, "v1.0"
usfOperators.txtOperator.Value = ""
usfOperators.txtOperator.SetFocus
verify = verify + 1
Exit Sub
ElseIf add = vbYes And text = vbNullString Or text = " " Then _
MsgBox "Empty field!", vbCritical, "v1.0"
usfOperators.txtOperator.Value = ""
usfOperators.txtOperator.SetFocus
verify = verify + 1
Exit Sub
ElseIf add = vbNo Then
usfOperators.txtOperator.Value = ""
usfOperators.txtOperator.SetFocus
verify = verify + 1
Exit Sub
End If


Going for the remove button now.

Ty

Spaggiari
07-31-2012, 07:14 AM
Here comes some updating.

Changed the offset formula to =OFFSET(database!$A$2; 0; 0; COUNTA(database!$A:$A<>"")-1;1)

If i had blanks between cells it wouldn't list all the items in the range, this takes some time to load but it works, is there a faster alternative?

I had the range sorted when the form initializes just in case some data is entered through the sheet instead. Once again this takes time to load because of the range offset formula but it works just fine.


Private Sub UserForm_Initialize()

txtOperator.SetFocus
Dim aCell As Range
Worksheets("database").Range("Operators").Sort Key1:=Range("Operators"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Application.ScreenUpdating = False

With Worksheets("database")
For Each aCell In .Range("Operators")
If aCell.Value <> "" Then
Me.lstOperators.AddItem aCell.Value
End If
Next

End With

Application.ScreenUpdating = True

End Sub


I still have a problem adding, for example:

I have Operator 01 in the top of the listbox, i add Operator 00 but it shows me a duplicate Operate 01 in the list box, in the sheet its fine.

As for the remove button I'm getting an error when removing the last value, it removes the value from the listbox, keeps deleting the other values from the listbox until there is none, and at last it deletes the last value that was selected in the listbox from the sheet.

Would take a look at it?

Ty

Tinbendr
07-31-2012, 12:08 PM
is there a faster alternative? There's a lot of sorting going on trying to fill the listboxes. You might comment those out and run it to see if that's where the slowdown is.


but it shows me a duplicate Operate 01 Move the sort to the END of the For/Next loop.

Also
If verify = 0 Then
Me.lstOperators.Clear
With Sheets("database")
.Cells(lastRow + 1, 1) = text

Be sure to add the qualifying dot in front of cells.



I'm getting an error when removing the last value, it removes That's because you're looping through the list twice.

For i = 0 To lstOperators.ListCount - 1
and
For count = .ListCount - 1 To 0 Step -1

Just take the second one out. You should be fine.

Spaggiari
07-31-2012, 12:57 PM
There's a lot of sorting going on trying to fill the listboxes. You might comment those out and run it to see if that's where the slowdown is.

Move the sort to the END of the For/Next loop.

Also
If verify = 0 Then
Me.lstOperators.Clear
With Sheets("database")
.Cells(lastRow + 1, 1) = text

Be sure to add the qualifying dot in front of cells.


That's because you're looping through the list twice.

For i = 0 To lstOperators.ListCount - 1
and
For count = .ListCount - 1 To 0 Step -1

Just take the second one out. You should be fine.

Thx David I'll work on it and post it later! Really helpful from you!

Spaggiari
08-01-2012, 02:50 AM
There's a lot of sorting going on trying to fill the listboxes. You might comment those out and run it to see if that's where the slowdown is..

Did what you told me to and the slowdown is definitely when it fills the listbox.



Move the sort to the END of the For/Next loop.


It doesn't sort the last value entered.

If verify = 0 Then
Me.lstOperators.Clear
With Sheets("database")
.Cells(lastRow + 1, 1) = text
For Each aCell In .Range("Operators")
If aCell.Value <> "" Then
Me.lstOperators.AddItem aCell.Value
End If
Next
.Range("Operators").Sort Key1:=Range("Operators"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
usfOperators.txtOperator.Value = ""
usfOperators.txtOperator.SetFocus
MsgBox "Added!", vbInformation, "v1.0"
End With
End If


Also
If verify = 0 Then
Me.lstOperators.Clear
With Sheets("database")
.Cells(lastRow + 1, 1) = text

Be sure to add the qualifying dot in front of cells.


Done!



That's because you're looping through the list twice.

For i = 0 To lstOperators.ListCount - 1
and
For count = .ListCount - 1 To 0 Step -1

Just take the second one out. You should be fine.

I remove the second one but now I can only delete the first entry.

Cheers

Spaggiari
08-01-2012, 04:10 AM
It does sort the last value entered with code!

It writes the value into the sheet, sorts the range, clears the list and then fills it.

Right?

If verify = 0 Then
With Sheets("database")
.Cells(lastRow + 1, 1) = text
Worksheets("database").Range("Operators").Sort Key1:=Range("Operators"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
MsgBox "Added!", vbInformation, "v1.0"
usfOperators.txtOperator.Value = ""
usfOperators.txtOperator.SetFocus
Me.lstOperators.Clear
For Each aCell In .Range("Operators")
If aCell.Value <> "" Then
Me.lstOperators.AddItem aCell.Value
End If
Next
End With
End If

Spaggiari
08-01-2012, 06:13 AM
Now for the remove button, problem solved with this:

For i = 0 To lstOperators.ListCount - 1
If lstOperators.Selected(i) = True Then
remove = MsgBox("Remove?", vbYesNo + vbExclamation, "v1.0")
If remove = vbYes Then
With lstOperators
Sheets("database").Range("Operators").Rows(i + 1).Clear
.RemoveItem .ListIndex
Worksheets("database").Range("Operators").Sort Key1:=Range("Operators"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
usfOperators.txtOperator.Value = ""
usfOperators.txtOperator.SetFocus
MsgBox "Removed!", vbInformation, "v1.0"
End With
End If
End If
Next i

It's working fine so far just let me know your thoughts about it!

Cheers

Tinbendr
08-01-2012, 02:17 PM
Agh, you beat me to it! :)

Looks pretty good to me. Now the hard part; testing!

Spaggiari
08-01-2012, 02:57 PM
Agh, you beat me to it! :)

Looks pretty good to me. Now the hard part; testing!

I've tested it and it seems to be working just fine.

I love it when a plan comes together :rofl:

Spaggiari
08-02-2012, 02:54 AM
Gooooooooooooood morning Vietnam!

In usfMain I have two textboxes I want them to take numeric values, convert them to time and in the end save it to the records sheet as time value so i can calculate the time difference in hh:mm.

I tried to format the entered value as time but it just didn't work so I came up with this code, the problem here is if enter for example 0025 it converts to :25.

Also if I put 2500 it formats the value to 1:00 I can see the logics here :whip

Do you have any other solutions? I don't think this one is gonna work :think:


Private Sub txtStart_AfterUpdate()
Dim lastRow As Long
Dim verify As Integer
verify = 0
If Not IsNumeric(usfMain.txtStart.Value) Then
MsgBox "Value must be numeric!", vbInformation, "v1.0"
usfMain.txtStart.Value = "Select"
verify = verify + 1
Exit Sub
End If

If verify = 0 Then
Sheets("records").Select
With Sheets("records")
lastRow = .Cells(.Rows.count, "G").End(xlUp).Row
txtStart.text = Format(txtStart.text, "##:##")
Cells(lastRow + 1, 7).Value = txtStart.Value
End With
End If

End Sub

Tinbendr
08-02-2012, 06:50 AM
I'm really bad at Excel Time math. Here's link that might help you. (http://www.cpearson.com/excel/DateTimeEntry.htm)

Spaggiari
08-03-2012, 02:23 AM
I'm really bad at Excel Time math. Here's link that might help you. (http://www.cpearson.com/excel/DateTimeEntry.htm)

I'm gonna take a look at it, any updates will be posted sooner or later.

Ty

Spaggiari
08-06-2012, 02:21 AM
Hey everyone.
The link you gave me was like, "nyaaahhh" :rotlaugh:
What I did what two textboxes was this, the first one takes the hours and the second one the minutes, after I'll concatenate both add some ":" to format it and it should work. Tada!
Either way I think there should be an easier way to deal with this time thingy! Let me hear your thoughts. :thumb



Private Sub txtStart1_AfterUpdate()

Dim lastRow As Long
Dim verify As Integer
Dim start1 As Integer
start1 = usfMain.txtStart1.Value
verify = 0

If start1 < 0 Or start1 > 23 Then
MsgBox "Error!", vbInformation, "v1.0"
usfMain.txtStart1.Value = "Selected"
verify = verify + 1
Exit Sub
End If

If verify = 0 Then
With Sheets("records")
lastRow = .Cells(.Rows.count, "B").End(xlUp).Row
Cells(lastRow + 1, 7).Value = start1
End With
End If

End Sub

Spaggiari
08-06-2012, 08:07 AM
Hey everyone, I'd like to know, if its possible, how to increment a formula with an If statement that transforms 2 values in time format and then as long as these two values keep beeing added in the cells the formula keeps active in the column.

I tried to apply the code for all the but this makes my file huge like 20MB!

You can check this on the file, go the "records" sheet, add the time in the "shift start" and "shift end" fields, then the difference value goes to "hours" in the sheet.

https://www.dropbox.com/s/ouda3vdcu0buefh/project.xlsm (https://www.dropbox.com/s/ouda3vdcu0buefh/project.xlsm)

Thx in advance!
:beerchug:

Spaggiari
08-10-2012, 02:14 AM
Hey everyone, I'd like to know, if its possible, how to increment a formula with an If statement that transforms 2 values in time format and then as long as these two values keep beeing added in the cells the formula keeps active in the column.

I tried to apply the code for all the but this makes my file huge like 20MB!

You can check this on the file, go the "records" sheet, add the time in the "shift start" and "shift end" fields, then the difference value goes to "hours" in the sheet.

https://www.dropbox.com/s/ouda3vdcu0buefh/project.xlsm (https://www.dropbox.com/s/ouda3vdcu0buefh/project.xlsm)

Thx in advance!
:beerchug:

Updated the link It wasn't working!

https://www.dropbox.com/s/qb2m9a7a6at7e68/project.xlsm