Results 1 to 3 of 3

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

  1. #1
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12

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

    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
    Attached Files Attached Files

  2. #2
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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
    Last edited by bakerman; 08-27-2013 at 09:02 PM.

  3. #3
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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
    Attached Files Attached Files

Similar Threads

  1. Replies: 4
    Last Post: 07-27-2013, 01:34 PM
  2. VBA code to delete cell ranges
    By rich_cirillo in forum Excel Help
    Replies: 3
    Last Post: 07-08-2013, 09:18 AM
  3. Replies: 2
    Last Post: 05-30-2013, 07:28 PM
  4. Removing unused Cell styles - need an efficient code
    By siddharthsindhwani in forum Excel Help
    Replies: 8
    Last Post: 04-15-2013, 07:12 AM
  5. Replies: 14
    Last Post: 01-26-2013, 04:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •