[RESOLVED] New project

**Spaggiari** · Jul 24th, 2012, 09:29 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!

**vbfbryce** · Jul 24th, 2012, 12:07 PM

Where in your code are the ranges that you want to make dynamic?

**Spaggiari** · Jul 24th, 2012, 03:07 PM

Originally Posted by vbfbryce

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?

Thx

**vbfbryce** · Jul 24th, 2012, 08:59 PM

You have a lot going on there...

Let's start with your first wish: dynamic ranges.

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

**Spaggiari** · Jul 25th, 2012, 04:20 AM

Originally Posted by vbfbryce

You have a lot going on there...

Let's start with your first wish: dynamic ranges.

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?

**Spaggiari** · Jul 25th, 2012, 04:37 AM

Code:

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

**vbfbryce** · Jul 25th, 2012, 09:00 AM

As an example:

Code:

usfMachines.lstMachines.RowSource = rngMach 'would set your listbox row source to dynamic range

**Spaggiari** · Jul 25th, 2012, 09:07 AM

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

**vbfbryce** · Jul 25th, 2012, 09:12 AM

I only have 2002 at work, so I can't run this.

Where are you getting the error, and what does it say?

**Spaggiari** · Jul 25th, 2012, 09:37 AM

Originally Posted by vbfbryce

I only have 2002 at work, so I can't run this.

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

**Spaggiari** · Jul 25th, 2012, 10:26 AM

Originally Posted by Spaggiari

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

Now I'm also getting this error when using this code to sort the dynamic range

Code:

Range("rngOperators").Sort Key1:=Range("a2"), Order1:=xlDescending

Run-time error '1004':

Method 'Range' of object'_Global' failed

**vbfbryce** · Jul 25th, 2012, 11:08 AM

Any chance you're "out of sequence?" For example, trying to use the range before having set it?

**Spaggiari** · Jul 27th, 2012, 03:44 AM

Originally Posted by vbfbryce

Any chance you're "out of sequence?" For example, trying to use the range before having set it?

I don't think so, I'm using the same code to set the range and at the end I try to sort it but I get that error!

**vbfbryce** · Jul 27th, 2012, 11:23 AM

Just got 2010 installed...will take another look.

Can you tell me what you do so I can try to recreate your steps?

**Spaggiari** · Jul 31st, 2012, 10:51 AM

Originally Posted by vbfbryce

Just got 2010 installed...will take another look.

Can you tell me what you do so I can try to recreate your steps?

Here's what I've got so far, better than explaining take a look at it yourself If you'd please!

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

**vbfbryce** · Jul 31st, 2012, 11:02 AM

Ok, I have the workbook downloaded...what do I do? Click start? Utilities? then what?

**Spaggiari** · Jul 31st, 2012, 02:54 PM

Originally Posted by vbfbryce

Ok, I have the workbook downloaded...what do I do? Click start? Utilities? then what?

Utilities > System > Operators

Give your thoughts about it, have you seen the code?

**vbfbryce** · Aug 1st, 2012, 10:19 AM

I had some issues when running it...

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:

**Spaggiari** · Aug 1st, 2012, 10:37 AM

Originally Posted by vbfbryce

I had some issues when running it...

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:

Thx for the reply those errors were fixed!

File is now updated > https://www.dropbox.com/s/ouda3vdcu0buefh/project.xlsm

**Spaggiari** · Aug 2nd, 2012, 07:56 AM

Hi again,

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

**vbfbryce** · Aug 2nd, 2012, 09:52 AM

What are are you expecting to have entered in the Start and End boxes, and what do you want it to be turned into exactly?

**Spaggiari** · Aug 2nd, 2012, 09:59 AM

Originally Posted by vbfbryce

What are are you expecting to have entered in the Start and End boxes, and what do you want it to be turned into exactly?

A numeric value for example 1200, formated to 12:00 something like this but for example if enter 2500 it turns out to be 1:00.

**vbfbryce** · Aug 2nd, 2012, 10:05 AM

What do you want 2500 to be turned into?

**Spaggiari** · Aug 3rd, 2012, 03:59 AM

Originally Posted by vbfbryce

What do you want 2500 to be turned into?

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!

Ty

**vbfbryce** · Aug 3rd, 2012, 11:19 AM

You could try something like this:

Code:

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

**Spaggiari** · Aug 6th, 2012, 04:11 AM

Originally Posted by vbfbryce

You could try something like this:

Code:

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

**vbfbryce** · Aug 8th, 2012, 08:43 PM

Split into hours and minutes as above, then check for minutes greater than 59 regardless of hour

**Spaggiari** · Aug 10th, 2012, 04:25 AM

Originally Posted by vbfbryce

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

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

**vbfbryce** · Aug 13th, 2012, 11:09 AM

Column C in sheet "Records" contains the shift.

You're wanting to take the hour / minute values and turn that into shift somehow? or something completely different from that?

**Spaggiari** · Aug 14th, 2012, 04:45 AM

Originally Posted by vbfbryce

Column C in sheet "Records" contains the shift.

You're wanting to take the hour / minute values and turn that into shift somehow? or something completely different from that?

Sorry I meant Column B hours! I just want to pick the start and end time and record the difference between them on column B.

**vbfbryce** · Aug 15th, 2012, 07:47 AM

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...

**Spaggiari** · Aug 17th, 2012, 04:03 AM

Originally Posted by vbfbryce

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.

You can take a look here https://www.dropbox.com/s/qb2m9a7a6a...project.xlsm?m

Ty

Thread: [RESOLVED] New project

Thread Tools

Display

[RESOLVED] New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Re: New project

Posting Permissions