none
excel VBA RRS feed

  • General discussion

  • Hi

    I am using excel 2007 . I have created a userform that when i click the save button the data on the user form saves to an excel sheet 1 and then to a sheet called "patrol". My problem is I do not want the second data recorded to "patrol" unless combobox named CompatrolA has a value , in otherwords if CompatrolA is blank dont save info to sheet "patrol"

    I am new to VBA and I am learning quickly but this one has me stumped. Any Help would be great. Hope I have given you enough info to explain problem.

    Thanks

    Private Sub CmdsaveA_Click()

    ' Write data to Worksheet
        RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
        With Worksheets("Sheet1").Range("A1")
        .Offset(RowCount, 0).Value = Me.Comdate.Value
        .Offset(RowCount, 1).Value = Me.ComsurnameA.Value
        .Offset(RowCount, 2).Value = Me.TxtforeA.Value
        .Offset(RowCount, 3).Value = Me.TxtregA.Value
        .Offset(RowCount, 4).Value = Me.TxtunitA.Value
        .Offset(RowCount, 5).Value = Me.TxtrankA.Value
        .Offset(RowCount, 6).Value = Me.ComshiftA.Value
        .Offset(RowCount, 7).Value = Me.CkleaveA.Value
        .Offset(RowCount, 8).Value = Me.CksickA.Value
        .Offset(RowCount, 9).Value = Me.TextrangeA.Value
        .Offset(RowCount, 10).Value = Me.ComstartA.Value
        .Offset(RowCount, 11).Value = Me.CompatrolA.Value
        .Offset(RowCount, 12).Value = Me.ComRouteA.Value
        .Offset(RowCount, 13).Value = Me.ComvipA.Value
        .Offset(RowCount, 14).Value = Me.ComenqA.Value
        .Offset(RowCount, 15).Value = Me.CompostA.Value
        .Offset(RowCount, 16).Value = Me.ComvisitA.Value
        .Offset(RowCount, 17).Value = Me.ComopsA.Value
    ' Write data to Worksheet
        RowCount = Worksheets("Patrol").Range("A1").CurrentRegion.Rows.Count
        With Worksheets("Patrol").Range("A1")
        .Offset(RowCount, 0).Value = Me.Comdate.Value
        .Offset(RowCount, 1).Value = Me.ComsurnameA.Value
        .Offset(RowCount, 2).Value = Me.TxtforeA.Value
        .Offset(RowCount, 3).Value = Me.CompatrolA.Value
        .Offset(RowCount, 4).Value = Me.ComshiftA.Value
        .Offset(RowCount, 5).Value = Me.ComstartA.Value
    End With
    ' Clear the Form
        For Each Ctl In Me.Controls
        If TypeName(Ctl) = "TextBox" Or TypeName(Ctl) = "ComboBox" Or TypeName(Ctl) = CheckBox Then
            Ctl.Value = ""
        End If
        Next Ctl
    End With

    End Sub

    • Changed type bigger312 Tuesday, November 8, 2011 5:50 PM om advise
    • Changed type bigger312 Tuesday, November 8, 2011 5:53 PM should be VBA office
    • Moved by Mike Feng Thursday, November 10, 2011 3:40 AM VBA (From:Visual Basic General)
    Tuesday, November 8, 2011 4:59 PM

All replies

  • This forum is for VB in Visual Studio Net

    Try the VBA forum which is for VB in office

    http://social.msdn.microsoft.com/Forums/en-US/isvvba


    Success
    Cor
    Tuesday, November 8, 2011 5:38 PM
  • Hello Bigger312,

    If you want to add to the sheet "Patrol" on ly if the ComboBox has a value, then simply add an If statement before the code that fills "patrol"

    If string.isnullorEmpty(Me.compatrolA.SelectedItem.ToString) then

      RowCount = Worksheets("Patrol").Range("A1").CurrentRegion.Rows.Count
        With Worksheets("Patrol").Range("A1")
        .Offset(RowCount, 0).Value = Me.Comdate.Value
        .Offset(RowCount, 1).Value = Me.ComsurnameA.Value
        .Offset(RowCount, 2).Value = Me.TxtforeA.Value
        .Offset(RowCount, 3).Value = Me.CompatrolA.Value
        .Offset(RowCount, 4).Value = Me.ComshiftA.Value
        .Offset(RowCount, 5).Value = Me.ComstartA.Value
    End With

    Endif

     

    Also, I don't know the keyword "TypeName(ctl)". I suppose it is a keyword from the Microsoft Visual Basic library. But you test its result against two strings "TextBox" and "ComboBox" and also against a type Checkbox (without quotes). Possibly a typo ?

     

    Tuesday, November 8, 2011 6:58 PM