New project

[Spaggiari]

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]

Welcome to the board!

, as in using dynamic ranges

Here's a good article on dynamic named ranges.

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.

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

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.

[vba]Private Sub Combobox1_Change
if Combobox1 = "This Item" then
me.label1.caption = "This Item"
end if
[/vba]

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]

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.

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

[vba]Private Sub Combobox1_Change
if Combobox1 = "This Item" then
me.label1.caption = "This Item"
end if
[/vba]
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!

[vba]
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
[/vba]

[Tinbendr]

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]

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

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

[Tinbendr]

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]

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]

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
[VBA]With Worksheets("database")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

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

[/VBA]'Change Column Letter as required.

[Spaggiari]

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

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

[/vba]'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]

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]

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]

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]

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]

Re-uploaded the file seems the last is corrupt or something.

Ty

[Tinbendr]

You just have to add it to the list box.
[vba]
'Add to listbox
Me.lstOperators.AddItem usfOperators.txtOperator.Value
[/vba]
But it seems to want to resort the list for viewing, so I would just clear the list and refill it.

[vba]
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
[/vba]
To remove a item from the list.
[vba]Range("Operators").Rows(i + 1).Clear
.RemoveItem .ListIndex
[/vba]
And you're Yes/No is failing anyway. You might try a simpler test.

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

[Spaggiari]

You just have to add it to the list box.
[vba]
'Add to listbox
Me.lstOperators.AddItem usfOperators.txtOperator.Value
[/vba]
But it seems to want to resort the list for viewing, so I would just clear the list and refill it.

[vba]
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
[/vba]
To remove a item from the list.
[vba]Range("Operators").Rows(i + 1).Clear
.RemoveItem .ListIndex
[/vba]
And you're Yes/No is failing anyway. You might try a simpler test.

[vba]If Me.txtOperator.Value <> "" then[/vba]
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.

[vba]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[/vba]

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

[vba]
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
[/vba]

Going for the remove button now.

Ty

[Spaggiari]

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.

[vba]
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
[/vba]

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]

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
[VBA] If verify = 0 Then
Me.lstOperators.Clear
With Sheets("database")
.Cells(lastRow + 1, 1) = text
[/VBA]
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]

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
[VBA] If verify = 0 Then
Me.lstOperators.Clear
With Sheets("database")
.Cells(lastRow + 1, 1) = text
[/VBA]
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]

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.

[vba]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[/vba]

Also
[vba] If verify = 0 Then
Me.lstOperators.Clear
With Sheets("database")
.Cells(lastRow + 1, 1) = text
[/vba]
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