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.
Where in your code are the ranges that you want to make dynamic?
I defined the fixes ranges through the Name Manager. As for dynamic ranges the database sheet would be the target, column A as dynamic range for operators, column B as dynamic range for machines and so on.
Have you seen the file I attached? What are you're thoughts about it?
Here is some code to create range objects that are dynamic:
Code:
Sub range_def()
Dim wb As Workbook
Dim shtDB As Worksheet
Dim rngMach As Range
Dim lastRow As Long
Set wb = Workbooks("spag test.xlsm")
Set shtDB = wb.Sheets("database")
lastRow = shtDB.Range("b" & Rows.count).End(xlUp).Row
Set rngMach = shtDB.Range("b2", "b" & lastRow)
usfMachines.lstMachines.RowSource = rngMach 'would set your listbox row source to dynamic range
End Sub
Here is some code to create range objects that are dynamic:
Code:
Sub range_def()
Dim wb As Workbook
Dim shtDB As Worksheet
Dim rngMach As Range
Dim lastRow As Long
Set wb = Workbooks("spag test.xlsm")
Set shtDB = wb.Sheets("database")
lastRow = shtDB.Range("b" & Rows.count).End(xlUp).Row
Set rngMach = shtDB.Range("b2", "b" & lastRow)
usfMachines.lstMachines.RowSource = rngMach 'would set your listbox row source to dynamic range
End Sub
Thx for the reply and for the info I'm still a rookie and this is my first project I've done some stuff but I'm sure there are a lot of errors and stuff I can improve.
Should I put the code into the userform usfMachines in the initialize event? If I could apply this to the whole workbook It would be great is it possible?
Private Sub btnInsert_Click()
Dim wb As Workbook
Dim wsDatabase As Worksheet
Dim rngMachines As Range
Dim lastRow As Long
Set wb = Workbooks("test.xlsm")
Set wsDatabase = wb.Sheets("database")
lastRow = wsDatabase.Range("b" & Rows.count).End(xlUp).Row
Set rngMachines = wsDatabase.Range("b2", "b" & lastRow)
Dim text As String
Dim insert As Integer
insert = MsgBox("Insert?", vbYesNo + vbExclamation, "v1.0")
text = txtMachine.Value
If insert = vbYes And text = vbNullString Or text = " " Then _
MsgBox "Empty field!", vbCritical, "v1.0"
txtMachine.SetFocus
If text <> vbNullString And text <> " " Then
With Sheets("database")
Cells(lastRow + 1, 2) = text
MsgBox "Machine added!", vbInformation, "v1.0"
End With
Selection.Sort Key1:=Range("b2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
txtMachine.Value = ""
txtMachine.SetFocus
End If
End Sub
Just used the code and made some slight changes on what I had it seems to be working as I wanted to! Next step will be assigning the list box rowsource. Ty
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!
Code:
Private Sub btnInsert_Click()
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
End Sub
Last edited by Spaggiari; Jul 25th, 2012 at 09:46 AM.
Where are you getting the error, and what does it say?
Run time error '13:
Type mismatch
This is the error I get, and this is the code in the Initialize event of usfOperators
Code:
Private Sub UserForm_Initialize()
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)
usfOperators.lstOperators.RowSource = rngOperators
txtOperator.SetFocus
End Sub
This is the error I get, and this is the code in the Initialize event of usfOperators
Code:
Private Sub UserForm_Initialize()
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)
usfOperators.lstOperators.RowSource = rngOperators
txtOperator.SetFocus
End Sub
Now I'm also getting this error when using this code to sort the dynamic range
When I tried to "remove an operator," it looped through and removed ALL of them, not just one.
When I added a new one (after having removed them all accidentally), it once again showed me all the originals plus the new one...
First things first, though...let's go back to your original post. You wanted to use dynamic ranges; I think you need to re-visit that wish. When you populate the list box on the Operator form (for example), you're looking at all 104k rows to find the non blank cells. In the attached book you will find some code making this dynamic instead:
When I tried to "remove an operator," it looped through and removed ALL of them, not just one.
When I added a new one (after having removed them all accidentally), it once again showed me all the originals plus the new one...
First things first, though...let's go back to your original post. You wanted to use dynamic ranges; I think you need to re-visit that wish. When you populate the list box on the Operator form (for example), you're looking at all 104k rows to find the non blank cells. In the attached book you will find some code making this dynamic instead:
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
Do you have any other solutions? I don't think this one is gonna work
Code:
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
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
I don't want 2500 to be turned into something, actually 2500 would generate an error message box because the value is over 2359 wich would be the last number allowed and in that case 2359 would be formatted to time like this 23:59.
Did I make myself clear? I have thought about other possible solution wich I will post later and it consists in two textboxes!
Private Sub TextBox1_AfterUpdate()
Dim myVal As Integer
Dim hr As Integer
Dim min As Integer
If Not IsNumeric(TextBox1.Value) Then
MsgBox "Enter a number less than or equal to 2359"
Else
myVal = TextBox1.Value
hr = myVal / 100
If (hr * 100) > myVal Then
hr = hr - 1
End If
min = myVal - (hr * 100)
End If
'put error checking in to determine if minutes > 59
MsgBox "Hour: " & hr & " & Minute: " & min
End Sub
Private Sub TextBox1_AfterUpdate()
Dim myVal As Integer
Dim hr As Integer
Dim min As Integer
If Not IsNumeric(TextBox1.Value) Then
MsgBox "Enter a number less than or equal to 2359"
Else
myVal = TextBox1.Value
hr = myVal / 100
If (hr * 100) > myVal Then
hr = hr - 1
End If
min = myVal - (hr * 100)
End If
'put error checking in to determine if minutes > 59
MsgBox "Hour: " & hr & " & Minute: " & min
End Sub
Hey vbfbryce your code works fine.
The thing is for example, if you type 2260 that's no supposed to be accepted, I mean I'd have to limit each hour to 59 minutes there should be an easier way to check this shouldn't it?
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.
Either way I think there should be an easier way to deal with this time thingy! Let me hear your thoughts.
Code:
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
Split into hours and minutes as above, then check for minutes greater than 59 regardless of hour
Hey all, so I've put 2 textboxes one for 0 to 23 hours and the other for 0 to 59! They're working just fine, now I need to add apply a formula so it fills the column "C" from sheet records Take a look at the file below below
Looks like you're already calculating the difference in column I.
Is that not working? I see you have at least one example where the end time is earlier than the start time. Are the dates always the same, or can the start be late one day and the end be early the next day?
You'd need to validate that they're not entering an earlier end time than start time...
Looks like you're already calculating the difference in column I.
Is that not working? I see you have at least one example where the end time is earlier than the start time. Are the dates always the same, or can the start be late one day and the end be early the next day?
You'd need to validate that they're not entering an earlier end time than start time...
This is what I came up with, created a table and applied a formula to the fields and its working fine.
[RESOLVED] New project
Reply With Quote
Originally Posted by vbfbryce
