data entry to correct cell range...code needs help

**paul_pearson** · 08-26-2013, 04:52 PM

Hi

I have userform "Production" transferring data to the correct cell range....need help getting the code to transfer "Planned Stop" and Machine Stoppage

All dates for Userforms called "Production","Planned Stop" and Machine Stoppage go into Column B in the sheets..
Planned Stop data transfers to Column G,H and I
Machine Stoppage data transfers to Columns J,K,L,M,N,O,P,Q

When data is entered by any of the 3 userforms into the sheets that there is only 1 entry per row....sample:if data is entered in row 15 for XFLOW A for production then the next entry into XFLOW A sheet goes into row 16....please see example sheet attached for sheet XFLOW A

Example:When an entry is entered in say Production for row 15 can the empty cells for Planned Stop and Machine Stoppage in row 15 automatically color Red

Thanks

Paul

**bakerman** · 08-27-2013, 08:45 PM

For UF Production

Code:

Private Sub CommandButton1_Click()
    'check user input
    For Each ctl In Me.Controls
        If ctl.Tag <> vbNullString Then
            If ctl.Value = vbNullString Then MsgBox ctl.Tag: ctl.SetFocus: Exit Sub
        End If
    Next
    'write data to worksheet
    With Sheets(ComboBox2.Value)
       .Unprotect Password:="abc"
       .Cells(.Cells(.Rows.Count, "B").End(xlUp).Row + 1, 2).Resize(, 5) = Array(ComboBox1.Value, _
            ComboBox3.Value, TextBox1.Text, TextBox3.Text, TextBox4.Text)
       .Cells(.Cells(.Rows.Count, "B").End(xlUp).Row, 2).Offset(, 5).Resize(, 11).Interior.ColorIndex = 3
        .Protect Password:="abc"
    End With
    'Clear all fields
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
    Next
End Sub

For UF Planned_Stop

Code:

Private Sub CommandButton1_Click()
    'check user input
    For Each ctl In Me.Controls
        If ctl.Tag <> vbNullString Then
            If ctl.Value = vbNullString Then MsgBox ctl.Tag: ctl.SetFocus: Exit Sub
        End If
    Next
    'write data to worksheet
    With Sheets(ComboBox2.Value)
       .Unprotect Password:="abc"
       .Cells(.Cells(.Rows.Count, "B").End(xlUp).Row + 1, 2).Resize(, 8) = Array(ComboBox1.Value, , , , , _
            ComboBox3.Value, TextBox1.Text, TextBox2.Text)
        .Protect Password:="abc"
    End With
    'Clear all fields
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
    Next
End Sub

For UF MachineStoppage

Code:

Private Sub CommandButton1_Click()
    'check user input
    For Each ctl In Me.Controls
        If ctl.Tag <> vbNullString Then
            If ctl.Value = vbNullString Then MsgBox ctl.Tag: ctl.SetFocus: Exit Sub
        End If
    Next
    'write data to worksheet
    With Sheets(ComboBox2.Value)
       .Unprotect Password:="abc"
       .Cells(.Cells(.Rows.Count, "B").End(xlUp).Row + 1, 2).Resize(, 16) = Array(ComboBox1.Value, , , , , , , , _
            ComboBox4.Value, ComboBox5.Value, ComboBox6.Value, IIf(ComboBox3.ListIndex = 0, TextBox2.Text, ""), _
            ComboBox7.Value, ComboBox8.Value, IIf(ComboBox3.ListIndex = 1, TextBox2.Text, ""), TextBox1.Text)
        .Protect Password:="abc"
    End With
    'Clear all fields
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
    Next
End Sub

**paul_pearson** · 08-28-2013, 05:26 PM

Very nice bakerman...it worked on the Userform which was attached in Post#1

I have changed the Combobox2 on the 3 userforms which selects which machine and replaced with optionbuttons...also replaced combobox3 in the machinestoppage userform

I am stuck on how to use the optionbuttons in the codes instead of the combobox`s so it transfers data to the correct sheets and cells.....currently all 3 userforms give errors.......

Thanks

Paul