1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
| Private Sub CommandButton1_Click()
Dim i As String
Dim j, skilled, Ongoing, Noskills As Double
Dim lastrow As String
Dim MyDate
MyDate = Date ' MyDate contains the current system date.
If ComboBox1.Value = "" Then
MsgBox "Please, fill the form"
UserForm1.Hide
Else
lastrow = Sheet1.Range("B65536").End(xlUp).Row ' last row of column B
i = ComboBox1.Value
j = WorksheetFunction.Match("" & i, Range("B1:B" & lastrow), 0) ' find the line of the value selected in combobox 1
If Sheet1.Range("A" & j).Value = "" Then
Sheet3.Range("B1").Value = Sheet1.Range("A" & j).End(xlUp).Cells ' Fill the form on sheet 3
Else: Sheet3.Range("B1").Value = Sheet1.Range("A" & j).Value
End If
Sheet3.Range("B3").Value = Sheet1.Range("B" & j).Value ' Fill the form on sheet 3
Sheet3.Range("B5").Value = Sheet1.Range("C" & j).Value ' Fill the form on sheet 3
skilled = WorksheetFunction.Match("3", Range("B" & j & ":EC" & j), 0)
Ongoing = WorksheetFunction.Match("2", Range("B" & j & ":EC" & j), 0)
Noskills = WorksheetFunction.Match("1", Range("B" & j & ":EC" & j), 0)
MyDate = Date
Sheet3.Range("B10").Value = MyDate
End If
End Sub |
