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]