Dim tcno
Private Sub ComboBox1_Change()
Me.ListBox1.Clear
For Each a In Me.Controls
If TypeName(a) = "OptionButton" Then Me.Controls(a.Name).Value = False
If TypeName(a) = "TextBox" Then Me.Controls(a.Name).Value = ""
Next
End Sub
Private Sub CommandButton1_Click()
For b = 1 To 8
If Me.Controls("TextBox" & b).Text = "" Then Exit Sub
Next
'^^^^^^^^^^^^^^^^^^^^^^^^
'tcno = Me.ListBox1.Column(2)
kad = Array("O", "P", "Q", "S") ', "V", "W", "X", "Z")
sk5 = Array("AC", "AD", "AE", "AG") ', "AJ", "AK", "AL", "AN")
sk6 = Array("AQ", "AR", "AS", "AU") ', "AX", "AY", "AZ", "BB")
txt = Array(1, 2, 3, 4) ', 5, 6, 7, 8)
For a = 3 To Range("D" & Rows.Count).End(xlUp).Row
If Cells(a, 4) = tcno Then
For b = LBound(txt) To UBound(txt)
If Me.Controls("OptionButton" & txt(b)).Value = True Then
If 1 = b + 1 Or 2 = b + 1 Then
For c = 1 To 4
Cells(a, kad(c - 1)) = Me.Controls("TextBox" & c).Text
Next c
Exit For
End If
If b + 1 = 3 Then
For c = 1 To 4
Cells(a, sk5(c - 1)) = Me.Controls("TextBox" & c).Text
Next c
Exit For
End If
If b + 1 = 4 Then
For c = 1 To 4
Cells(a, sk6(c - 1)) = Me.Controls("TextBox" & c).Text
Next c
Exit For
End If
End If
Next b
Exit For
End If
Next a
'^^^^^^^^^^^^^^
If Me.TextBox7.Text = 1 Then sayfa = "Derece Terfi Formu"
If Me.TextBox7.Text = 2 Or Me.TextBox7.Text = 3 Then sayfa = "Kademe Terfi Formu"
With Sheets(sayfa)
sut = 7
sat = .Cells(Rows.Count, 4).End(xlUp).Row + 1
If sat = 5 Then sat = sat + 1
dizi = Array(sat - 5, Cells(a, 3), Cells(a, 4), Cells(a, 5), Cells(a, "M"), Cells(a, 2))
.Cells(sat, 2).Resize(1, UBound(dizi) + 1) = dizi
' .Cells(sat, 2) = sat + 5
' .Cells(sat, 3) = Cells(a, 3)
' .Cells(sat, 4) = Cells(a, 4)
' .Cells(sat, 5) = Cells(a, 5)
' .Cells(sat, 6) = Cells(a, "M")
' .Cells(sat, 7) = Cells(a, 2)
For b = 1 To 8
sut = sut + 1
.Cells(sat, sut) = Me.Controls("TextBox" & b).Text
Next
End With
MsgBox "İşlem kayıt Tamam", vbInformation + vbMsgBoxRtlReading, "Tamam"
Unload Me
End Sub
Private Sub Frame1_Click()
End Sub
Private Sub ListBox1_Click()
tcno = Me.ListBox1.Column(2)
kad = Array("O", "P", "Q", "S", "V", "W", "X", "Z")
sk5 = Array("AC", "AD", "AE", "AG", "AJ", "AK", "AL", "AN")
sk6 = Array("AQ", "AR", "AS", "AU", "AX", "AY", "AZ", "BB")
txt = Array(1, 2, 3, 4, 5, 6, 7, 8)
For a = 3 To Range("D" & Rows.Count).End(xlUp).Row
If Cells(a, 4) = tcno Then
For b = LBound(txt) To UBound(txt)
If Me.Controls("OptionButton" & txt(b)).Value = True Then
If 1 = b + 1 Or 2 = b + 1 Then
For c = 1 To 8
Me.Controls("TextBox" & c).Text = Cells(a, kad(c - 1))
Next c
Exit For
End If
If b + 1 = 3 Then
For c = 1 To 8
Me.Controls("TextBox" & c).Text = Cells(a, sk5(c - 1))
Next c
Exit For
End If
If b + 1 = 4 Then
For c = 1 To 8
Me.Controls("TextBox" & c).Text = Cells(a, sk6(c - 1))
Next c
Exit For
End If
End If
Next b
Exit For
End If
Next a
End Sub
Private Sub OptionButton1_Click()
If Me.ComboBox1.Text = "" Then Exit Sub
Dim b()
For a = 3 To Range("AA" & Rows.Count).End(xlUp).Row
If Me.ComboBox1.Text = Cells(a, "AA") Then
If Cells(a, "X") = 2 Or Cells(a, "X") = 3 Then
say = say + 1
ReDim Preserve b(1 To 3, 1 To say)
b(1, say) = Cells(a, "B")
b(2, say) = Cells(a, "C")
b(3, say) = Cells(a, "D")
End If
End If
Next
With Me.ListBox1
.RowSource = vbNullString
.Clear
If IsEmpty(say) Then Exit Sub
.ColumnCount = UBound(b())
.Column = b
.ColumnWidths = "185,85,75"
End With
End Sub
Private Sub OptionButton2_Click()
If Me.ComboBox1.Text = "" Then Exit Sub
Dim b()
For a = 3 To Range("AA" & Rows.Count).End(xlUp).Row
If Me.ComboBox1.Text = Cells(a, "AA") Then
If Cells(a, "X") = 1 Then
say = say + 1
ReDim Preserve b(1 To 3, 1 To say)
b(1, say) = Cells(a, "B")
b(2, say) = Cells(a, "C")
b(3, say) = Cells(a, "D")
End If
End If
Next
With Me.ListBox1
.RowSource = vbNullString
.Clear
If IsEmpty(say) Then Exit Sub
.ColumnCount = UBound(b())
.Column = b
.ColumnWidths = "185,85,75"
End With
End Sub
Private Sub OptionButton3_Click()
If Me.ComboBox1.Text = "" Then Exit Sub
Dim b()
For a = 3 To Range("AO" & Rows.Count).End(xlUp).Row
If Me.ComboBox1.Text = Cells(a, "AO") Then
' If Cells(a, "X") = 1 Then
say = say + 1
ReDim Preserve b(1 To 3, 1 To say)
b(1, say) = Cells(a, "B")
b(2, say) = Cells(a, "C")
b(3, say) = Cells(a, "D")
' End If
End If
Next
With Me.ListBox1
.RowSource = vbNullString
.Clear
If IsEmpty(say) Then Exit Sub
.ColumnCount = UBound(b())
.Column = b
.ColumnWidths = "185,85,75"
End With
End Sub
Private Sub OptionButton4_Click()
If Me.ComboBox1.Text = "" Then Exit Sub
Dim b()
For a = 3 To Range("BC" & Rows.Count).End(xlUp).Row
If Me.ComboBox1.Text = Cells(a, "BC") Then
' If Cells(a, "X") = 1 Then
say = say + 1
ReDim Preserve b(1 To 3, 1 To say)
b(1, say) = Cells(a, "B")
b(2, say) = Cells(a, "C")
b(3, say) = Cells(a, "D")
' End If
End If
Next
With Me.ListBox1
.RowSource = vbNullString
.Clear
If IsEmpty(say) Then Exit Sub
.ColumnCount = UBound(b())
.Column = b
.ColumnWidths = "185,85,75"
End With
End Sub
Private Sub UserForm_Initialize()
ComboBox1.List = Array("Ocak", "Şubat", "Mart", "Nisan", "Mayıs", "Haziran", "Temmuz", "Ağustos", "Eylül", "Ekim", "Kasım", "Aralık")
Sheets("Terfi Bilgi Girişi").Select
End Sub
