Data Entry Form

[lfellows]

Hi,

I'm trying to create a data entry form which will populate cells within a table on another sheet. I am aware you can use the form button but it does not have the level of customisability that I'm after.

The workbook is used to collate scrap quantities found at different stages of a process. It contains two sheets: One sheet that will contain the data entry form and the other sheet will have the table of information.

There are 20 columns that I would like to fill:

DATE

START TIME

END TIME

OP

BATCH

TYPE

START QTY

PROCESS 1

PROCESS 2

PROCESS 3

PROCESS 4

PROCESS 5

PROCESS 6

PROCESS 7

PROCESS 8

PROCESS 9

PROCESS 10

PROCESS 11

PROCESS 12

END QTY

The columns which are non-bold would be a simple manual input.

The columns which are bold, if possible I would like to be able to have a drop down list to select which process the scrap goes into i.e. if there is 10 scrapped at process 7, you would select process 7 from dropdown list and input 10

I will upload an image of the sheet if it's any help.

Thank you for any assistance. I'm not even sure if what I'm asking for is achievable.

 

[Fluff]

Rather than doing all this in a sheet, I would suggest creating a UserForm. If you're not sure how to do that have a look here.

How to Create Excel UserForm for Data Entry

See how to create an Excel UserForm for data entry, with text boxes and buttons. Follow step-by-step videos, detailed notes with screen shots, free Excel workbook

www.contextures.com

 

[lfellows]

Hi Fluff,

thanks for the link!

I've got it working however I want to know if its possible to use data validation lists for one of the columns? if so, how?
I want to use data validation on my 7th column, the "Process" column.
here is my code:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("TableOfData")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter the date"
Exit Sub
End If
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.txtDate.Value
.Cells(iRow, 2).Value = Me.txtStartTime.Value
.Cells(iRow, 3).Value = Me.txtEndTime.Value
.Cells(iRow, 4).Value = Me.txtOperator.Value
.Cells(iRow, 5).Value = Me.txtBatchNumber.Value
.Cells(iRow, 6).Value = Me.txtType.Value
.Cells(iRow, 7).Value = Me.txtProcess.Value
.Cells(iRow, 8).Value = Me.txtStartQuantity.Value
.Cells(iRow, 9).Value = Me.txtScrapQuantity.Value
' .Protect Password:="password"
End With
'clear the data
Me.txtDate.Value = ""
Me.txtStartTime.Value = ""
Me.txtEndTime.Value = ""
Me.txtOperator.Value = ""
Me.txtBatchNumber.Value = ""
Me.txtType.Value = ""
Me.txtProcess.Value = ""
Me.txtStartQuantity.Value = ""
Me.txtScrapQuantity.Value = ""
Me.txtDate.SetFocus
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close Form button!"
End If

End Sub

 

[Fluff]

Do you mean have data validation in the sheet, or in the userform?

 

[lfellows]

In the Userform. A drop down list to select the process.

Apologies for the confusion I'm rather new to this.

 

[lfellows]

This may help

 

[Fluff]

Ok, delete the "Process" textbox and replace it with a ComboBox.
Add this code to the userform module

Private Sub UserForm_Initialize()
    Me.cboProcess.List = Evaluate("row(1:12)")
End Sub

and change you command button code to

Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("TableOfData")
    'find first empty row in database
    iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    'check for a date
    If Trim(Me.txtDate.Value) = "" Then
        Me.txtDate.SetFocus
        MsgBox "Please enter the date"
        Exit Sub
    End If
    If Me.cboProcess.ListIndex = -1 Then
        MsgBox "please select a process"
        Exit Sub
    End If
    
    'copy the data to the database
    'use protect and unprotect lines,
    ' with your password
    ' if worksheet is protected
    With ws
        ' .Unprotect Password:="password"
        .Cells(iRow, 1).Value = Me.txtDate.Value
        .Cells(iRow, 2).Value = Me.txtStartTime.Value
        .Cells(iRow, 3).Value = Me.txtEndTime.Value
        .Cells(iRow, 4).Value = Me.txtOperator.Value
        .Cells(iRow, 5).Value = Me.txtBatchNumber.Value
        .Cells(iRow, 6).Value = Me.txtType.Value
        .Cells(iRow, 7).Value = Me.txtStartQuantity.Value
        .Cells(iRow, 7 + Val(Me.cboProcess.Value)).Value = Me.txtScrapQuantity.Value
        ' .Protect Password:="password"
    End With
    
    'clear the data
    Me.txtDate.Value = ""
    Me.txtStartTime.Value = ""
    Me.txtEndTime.Value = ""
    Me.txtOperator.Value = ""
    Me.txtBatchNumber.Value = ""
    Me.txtType.Value = ""
    Me.txtProcess.Value = ""
    Me.txtStartQuantity.Value = ""
    Me.txtScrapQuantity.Value = ""
    Me.txtDate.SetFocus
End Sub

Change the name of the combo in red to suit.

 

[lfellows]

This has worked perfectly thank you Fluff!

 

[lfellows]

This has worked perfectly thank you Fluff!

Correction, the start quantity and scrap quantity text boxes do not work now. The information does not get added to the table

 

[lfellows]

Not that I know much about code but the new code compared to the old code looks slightly different, is this right?

With ws
' .Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.txtDate.Value
.Cells(iRow, 2).Value = Me.txtStartTime.Value
.Cells(iRow, 3).Value = Me.txtEndTime.Value
.Cells(iRow, 4).Value = Me.txtOperator.Value
.Cells(iRow, 5).Value = Me.txtBatchNumber.Value
.Cells(iRow, 6).Value = Me.txtType.Value
.Cells(iRow, 7).Value = Me.txtStartQuantity.Value .Cells(iRow, 7 + Val(Me.cboProcess.Value)).Value = Me.txtScrapQuantity.Value
' .Protect Password:="password"
End With


With ws
' .Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.txtDate.Value
.Cells(iRow, 2).Value = Me.txtStartTime.Value
.Cells(iRow, 3).Value = Me.txtEndTime.Value
.Cells(iRow, 4).Value = Me.txtOperator.Value
.Cells(iRow, 5).Value = Me.txtBatchNumber.Value
.Cells(iRow, 6).Value = Me.txtType.Value
.Cells(iRow, 7).Value = Me.txtProcess.Value
.Cells(iRow, 8).Value = Me.txtStartQuantity.Value
.Cells(iRow, 9).Value = Me.txtScrapQuantity.Value
' .Protect Password:="password"
End With