Consulting

Results 1 to 2 of 2

Thread: Add features to userform

  1. #1

    Add features to userform

    Greetings everyone,

    I have a userform that works great, but I like to add some features if its possible. First, I like to tell you a little about the userform. The form allows users to enter data in the form to populate the worksheet. Also, if the data in the worksheet needs to be edited, when the user clicks on a row needing to be edited, the data from the worksheet populates the form.

    The form has 28 text boxes. One of the text box is called "Total". When the user fills this box in with a number, I would like the ability to auto-fill certain other boxs in the form identically to the number in the "Total" box.

    Ex are the titles for each column
    AIRCRAFT - ASEL - ASES - AMEL - OPT1 - OPT2 - OPT3 - OPT4 - OPT5 - DLNDG - NLNDG - NITE - IMC - S-IMC - XCTRY - SOLO - PIC - SIC - DUAL - CFI

    Cell AD9 has the Type of Aircraft the user will enter(Ex. C172)
    If, for example, the user places an "x" in the ASEL, XCTRY, PIC, CFI colums in the row next to the aircraft, it will auto-fill certain text boxes in the userform the number thats in the "Total" box in the userform.

    Ex. if Total = 1.5 Than
    ASEL, XCTRY, PIC, CFI will have 1 auto-filled with 1.5. All other text boxes will be empty, since the other colums did not have a "x" place in their respective column.


    The columns in the worksheet that will have an "x" to identify which text box to be auto-fill ranges AE - AW. The number of rows that the user can add an aircraft are rows 9 - 30.

    Below, is the code to the userform...

    [VBA] Private Sub UserForm_Activate()
    Dim x
    x = Cells(ActiveCell.Row, 1)
    If IsDate(x) Then
    frmLogbook.txtDATE = Format(x, "Medium Date")
    Else
    frmLogbook.txtDATE = Format(Date, "Medium Date")
    End If
    End Sub
    Private Sub UserForm_Initialize()
    Me.APPCHTYPE.List = Array("ILS", "LOC", "VOR", "NDB", "LDA", "BC-LOC", "RNAV", "GPS", "MLS")
    With ActiveCell
    If .Value = vbNullString Then
    With .Parent
    Set myCells = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
    Else
    Set myCells = ActiveCell.EntireRow.Range("A1")
    End If
    Set myCells = myCells.Resize(1, 28)
    End With
    Call FromSheetToUserform
    Me.ACLIST.SetFocus
    End Sub
    Sub FromSheetToUserform()
    Dim myList, i As Integer
    myList = Array("txtDATE", "ACLIST", "txtIDENT", "txtROUTE", "txtTOTAL", "txtSEL", _
    "txtSES", "txtMEL", "txtOPT1", "txtOPT2", "txtOPT3", "txtOPT4", "txtOPT5", _
    "txtDAY", "txtNIGHTLDG", "txtNIGHT", "txtIMC", "txtSIM", "txtAPPCH", _
    "APPCHTYPE", "txtFLTSIM", "txtXCTRY", "txtSOLO", "txtPIC", "txtSIC", _
    "txtDUAL", "txtCFI", "txtREMARKS")

    For i = 0 To UBound(myList)
    Me.Controls(myList(i)).Value = myCells(i + 1).Value
    Next
    End Sub
    Private Sub cmdAdd_Click()
    With Me
    If .txtDATE.Text = vbNullString Then
    MsgBox "Please enter a DATE"
    .txtDATE.SetFocus
    Else
    myCells.Range("A1").Select
    Call FromUFormToSheet
    Unload Me
    End If
    End With
    End Sub
    Sub FromUFormToSheet()
    Dim myList, i As Integer, a
    myList = Array("txtDATE", "ACLIST", "txtIDENT", "txtROUTE", "txtTOTAL", "txtSEL", _
    "txtSES", "txtMEL", "txtOPT1", "txtOPT2", "txtOPT3", "txtOPT4", "txtOPT5", _
    "txtDAY", "txtNIGHTLDG", "txtNIGHT", "txtIMC", "txtSIM", "txtAPPCH", _
    "APPCHTYPE", "txtFLTSIM", "txtXCTRY", "txtSOLO", "txtPIC", "txtSIC", _
    "txtDUAL", "txtCFI", "txtREMARKS")
    With myCells
    a = .Value
    For i = 0 To UBound(myList)
    Select Case i
    Case 0 To 3, 19, 27: a(1, i + 1) = Me.Controls(myList(i)).Value
    Case Else: a(1, i + 1) = IIf(Me.Controls(myList(i)).Value = "", "", Val(Me.Controls(myList(i)).Value))
    End Select
    Next
    .Value = a
    End With
    End Sub[/VBA]

  2. #2
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    Quote Originally Posted by av8tordude
    First, I like to tell you a little about the userform.
    Welcome to the board
    Maybe I missed it, but after you told us about the form... Did you ever ask a question?

    Search on "?" = 0
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

Posting Permissions

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