Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()

   Dim rw As Long    'next available row
 
   With Sheets("Sheet1")
 
      'get the next avialable row in Sheet1
      rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
      
      'put the text box values in this row
      
      .Range("A" & rw).Value = TextBox1.Value
      .Range("B" & rw).Value = TextBox2.Value
      .Range("C" & rw).Value = TextBox3.Value
      .Range("D" & rw).Value = ComboBox19.Value
      .Range("E" & rw).Value = ComboBox1.Value
      .Range("F" & rw).Value = ComboBox2.Value
      .Range("G" & rw).Value = ComboBox3.Value
      .Range("H" & rw).Value = ComboBox5.Value
      .Range("I" & rw).Value = ComboBox6.Value
      .Range("J" & rw).Value = ComboBox8.Value
      .Range("K" & rw).Value = ComboBox9.Value
      .Range("L" & rw).Value = ComboBox10.Value
      .Range("M" & rw).Value = ComboBox12.Value
      .Range("N" & rw).Value = ComboBox7.Value
      .Range("O" & rw).Value = ComboBox14.Value
      .Range("P" & rw).Value = ComboBox15.Value
      .Range("Q" & rw).Value = ComboBox16.Value
      .Range("R" & rw).Value = ComboBox18.Value
      .Range("S" & rw).Value = ComboBox13.Value
      
   'copy the formula from the previous row
      .Range("J" & rw - 1 & ":H" & rw - 1).Copy
     .Range("J" & rw & ":H" & rw).PasteSpecial Paste:=xlPasteFormulas
      Application.CutCopyMode = False
   
      
   End With

End Sub

Code: Select all

Private Sub CommandButton1_Click()

   Dim rw As Long    'next available row
 
   With Sheets("Sheet1")
 
      'get the next avialable row in Sheet1
      rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
      
      'put the text box values in this row
      
      .Range("A" & rw).Value = TextBox1.Value
      .Range("B" & rw).Value = TextBox2.Value
      .Range("C" & rw).Value = TextBox3.Value
      .Range("D" & rw).Value = ComboBox19.Value
      .Range("E" & rw).Value = ComboBox1.Value
      .Range("F" & rw).Value = ComboBox2.Value
      .Range("G" & rw).Value = ComboBox3.Value
      .Range("H" & rw).Value = ComboBox5.Value
      .Range("I" & rw).Value = ComboBox6.Value
      .Range("J" & rw).Value = ComboBox8.Value
      .Range("K" & rw).Value = ComboBox9.Value
      .Range("L" & rw).Value = ComboBox10.Value
      .Range("M" & rw).Value = ComboBox12.Value
      .Range("N" & rw).Value = ComboBox7.Value
      .Range("O" & rw).Value = ComboBox14.Value
      .Range("P" & rw).Value = ComboBox15.Value
      .Range("Q" & rw).Value = ComboBox16.Value
      .Range("R" & rw).Value = ComboBox18.Value
      .Range("S" & rw).Value = ComboBox13.Value
      
   'copy the formula from the previous row
      .Range("J" & rw - 1 & ":S" & rw - 1).Copy
     .Range("J" & rw & ":S" & rw).PasteSpecial Paste:=xlPasteFormulas
      Application.CutCopyMode = False
   
      
   End With

End Sub

Code: Select all

Private Sub ComboBox8_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("E2") = "Done" Then
            ws.Range("J2") = "Done"
        Else
            ws.Range("J2") = Me.ComboBox8
        End If
        Set ws = Nothing
End Sub

Code: Select all

Private Sub ComboBox8_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("E2") = "Done" Then
            ws.Range("J2") = "Done"
        Else
            ws.Range("J2") = Me.ComboBox8
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox9_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("F2") = "Done" Then
            ws.Range("K2") = "Done"
        Else
            ws.Range("K2") = Me.ComboBox9
        End If
        Set ws = Nothing
End Sub

Private Sub ComboBox10_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("G2") = "Done" Then
            ws.Range("L2") = "Done"
        Else
            ws.Range("L2") = Me.ComboBox10
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox11_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("H2") = "Done" Then
            ws.Range("M2") = "Done"
        Else
            ws.Range("M2") = Me.ComboBox11
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox7_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("I2") = "Done" Then
            ws.Range("N2") = "Done"
        Else
            ws.Range("N2") = Me.ComboBox7
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox14_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("J2") = "Done" Then
            ws.Range("O2") = "Done"
        Else
            ws.Range("O2") = Me.ComboBox14
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox15_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("K2") = "Done" Then
            ws.Range("P2") = "Done"
        Else
            ws.Range("P2") = Me.ComboBox15
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox16_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("L2") = "Done" Then
            ws.Range("Q2") = "Done"
        Else
            ws.Range("Q2") = Me.ComboBox16
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox17_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("M2") = "Done" Then
            ws.Range("R2") = "Done"
        Else
            ws.Range("R2") = Me.ComboBox17
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox13_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("N2") = "Done" Then
            ws.Range("S2") = "Done"
        Else
            ws.Range("S2") = Me.ComboBox13
        End If
        Set ws = Nothing
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim range2014 As Range
    Dim range2015 As Range
    Dim r As Range
    With Sheets("temp")
        Set range2014 = .Range("e2:i2")
        Set range2015 = .Range("j2:n2")
        For Each r In range2014
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        For Each r In range2015
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        .Range("A2:S2").Resize(1, 19).Copy
        Sheets("Sheet1").Range("A" & Rows.Count) _
            .End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        MsgBox ("Already Saved")
        .Range("A2:S2").ClearContents
    End With
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim range2014 As Range
    Dim range2015 As Range
    Dim range2016 As Range
    Dim r As Range
    With Sheets("temp")
        Set range2014 = .Range("E2:I2")
        Set range2015 = .Range("J2:N2")
        Set range2016 = .Range("O2:S2")
        For Each r In range2014
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        For Each r In range2015
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        For Each r In range2016
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        .Range("A2:S2").Resize(1, 19).Copy
        Sheets("Sheet1").Range("A" & Rows.Count) _
            .End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        MsgBox ("Already Saved")
        .Range("A2:S2").ClearContents
    End With
End Sub

Code: Select all

Private Sub TextBox1_Change()
        Worksheets("temp").Range("A2") = Me.TextBox1
End Sub

Private Sub TextBox2_Change()
        Worksheets("temp").Range("B2") = Me.TextBox2
End Sub

Private Sub TextBox3_Change()
        Worksheets("temp").Range("C2") = Me.TextBox3
End Sub
Private Sub ComboBox19_Change()
        Worksheets("temp").Range("D2") = Me.ComboBox19
End Sub

Private Sub ComboBox1_Change()
        Worksheets("temp").Range("E2") = Me.ComboBox1
End Sub

Private Sub ComboBox2_Change()
        Worksheets("temp").Range("F2") = Me.ComboBox2
End Sub

Private Sub ComboBox3_Change()
        Worksheets("temp").Range("G2") = Me.ComboBox3
End Sub

Private Sub ComboBox5_Change()
        Worksheets("temp").Range("H2") = Me.ComboBox5
End Sub
Private Sub ComboBox6_Change()
        Worksheets("temp").Range("I2") = Me.ComboBox6
End Sub
Private Sub ComboBox8_Change()
        Worksheets("temp").Range("J2") = Me.ComboBox8
End Sub
Private Sub ComboBox9_Change()
        Worksheets("temp").Range("K2") = Me.ComboBox9
End Sub
Private Sub ComboBox10_Change()
        Worksheets("temp").Range("L2") = Me.ComboBox10
End Sub
Private Sub ComboBox11_Change()
        Worksheets("temp").Range("M2") = Me.ComboBox11
End Sub
Private Sub ComboBox7_Change()
        Worksheets("temp").Range("N2") = Me.ComboBox7
End Sub
Private Sub ComboBox14_Change()
        Worksheets("temp").Range("O2") = Me.ComboBox14
End Sub
Private Sub ComboBox15_Change()
        Worksheets("temp").Range("P2") = Me.ComboBox15
End Sub
Private Sub ComboBox16_Change()
        Worksheets("temp").Range("Q2") = Me.ComboBox16
End Sub
Private Sub ComboBox17_Change()
        Worksheets("temp").Range("R2") = Me.ComboBox17
End Sub
Private Sub ComboBox13_Change()
        Worksheets("temp").Range("S2") = Me.ComboBox13
End Sub

Private Sub CommandButton1_Click()
    Dim range2014 As Range
    Dim range2015 As Range
    Dim range2016 As Range
    Dim r As Range
    With Sheets("temp")
        Set range2014 = .Range("E2:I2")
        Set range2015 = .Range("J2:N2")
        Set range2016 = .Range("O2:S2")
        For Each r In range2014
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        For Each r In range2015
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        For Each r In range2016
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        .Range("A2:S2").Resize(1, 19).Copy
        Sheets("Sheet1").Range("A" & Rows.Count) _
            .End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        MsgBox ("Already Saved")
        .Range("A2:S2").ClearContents
    End With
End Sub