Ramarian_1967
New Member
- Joined
- Aug 5, 2022
- Messages
- 1
- Office Version
- 365
- 2010
- 2007
- Platform
- Windows
Hi all, I'm hoping someone can help me on this one as it's driving me nuts! I have a form that is working but totally forgot about the update part of it? I have tried various codes from the forum changing some of the code but obviously it’s not working, which leads me to beg for help in solving this one. I have pasted the FORM’s VBA code and also attached a downsized version of the excel file, is it possible to add a code to the form so that the user clicks on the ‘UPDATE EMPLOYEE RECORD’ button opens up either FORM 1 (or I can create FORM 2) the user then enters the ‘EMPLOYEE ID NUMBER’ which fills in information to the relevant cells, the user then can enter more or change data then clicks the update button to which the data is then saved.
Again thanks for taking the time and helping me with this, as always any help is very much appreciated.
Again thanks for taking the time and helping me with this, as always any help is very much appreciated.
VBA Code:
Private Sub CommandButton1_Click()
Dim sht As Worksheet, sht1 As Worksheet, lastrow As Long
If VBA.IsNumeric(txtEMPLOYEEID.Value) = False Then
MsgBox "Sorry only numeric values are accepted in the EMPLOYEE ID box", vbCritical
Exit Sub
End If
Set sht = ThisWorkbook.Sheets("TRAINING DATABASE")
lastrow = sht.Range("a" & Rows.Count).End(xlUp).Row + 1
With sht
If Trim(txtEMPLOYEENAME) = "" Then .Range("A" & lastrow) = "MISSING" Else .Range("A" & lastrow) = txtEMPLOYEENAME
.Range("B" & lastrow).Value = txtEMPLOYEEID.Value
.Range("C" & lastrow).Value = txtAREA.Value
.Range("D" & lastrow).Value = txtjobtitle.Value
.Range("E" & lastrow).Value = txtSHQGLOBALORIENTATION.Value
.Range("F" & lastrow).Value = txtWHIMS.Value
If Trim(txtORIENTATIOS) = "" Then .Range("G" & lastrow) = "MISSING" Else .Range("G" & lastrow) = txtEMPLOYEENAME
If Trim(txtFITTEST) = "" Then .Range("H" & lastrow) = "MISSING" Else .Range("H" & lastrow) = txtFITTEST
End With
sht.Activate
End Sub
Private Sub CommandButton2_Click()
With Me
.txtEMPLOYEENAME.Value = ""
.txtEMPLOYEEID.Value = ""
.txtAREA.Value = ""
.txtjobtitle.Value = ""
.txtSHQGLOBALORIENTATION.Value = ""
.txtWHIMS.Value = ""
.txtORIENTATIONS.Value = ""
.txtFITTEST.Value = ""
End With
End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub
Private Sub Frame7_Click()
End Sub
Private Sub UserForm_Initialize()
Me.txtjobtitle.List = Sheet3.Range("B2:B71").Value
Me.txtAREA.List = Sheet3.Range("C2:C71").Value
End Sub
TRAINING DATABASE V10.xlsm | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
1 | GLOBAL | WHMIS | ORIENTATIONS | RESPIRATOR | |||||||||||||||||
2 | LAST SAVED DATE- | July 29, 2022 | TODAYS DATE - | August 5, 2022 | 3 YEARS | ||||||||||||||||
3 | EMPLOYEE NAME | EMPLOYEE ID | AREA | JOB TITLE | |||||||||||||||||
4 | JOE BLOGGS | 12345 | OFFICE | HELPER | 2022-02-03 | 2020-02-04 | 2020-02-05 | 2022-05-27 | |||||||||||||
5 | JOHN DOE | 34567 | ADMIN | SUPERVISOR | 2019-02-07 | 2019-02-08 | 2019-03-02 | 2022-05-27 | |||||||||||||
6 | PAUL MAUL | 78901 | MAINTENANCE | CLEANER | 2021-05-09 | 2021-05-09 | 2021-05-10 | 2021-05-11 | |||||||||||||
7 | DON BLAST | 23456 | MAINTENANCE | MAINTENANCE | 2022-02-02 | ||||||||||||||||
8 | PETER WING | 78901 | OPERATIONS | OPERATOR | 2022-02-02 | 2022-03-02 | |||||||||||||||
9 | LEN ROE | 23456 | MAINTENANCE | ELECTRICIAN | 2022-02-06 | 2022-07-08 | 2022-09-09 | ||||||||||||||
10 | |||||||||||||||||||||
11 | |||||||||||||||||||||
12 | |||||||||||||||||||||
13 | |||||||||||||||||||||
14 | |||||||||||||||||||||
15 | |||||||||||||||||||||
16 | |||||||||||||||||||||
17 | |||||||||||||||||||||
18 | |||||||||||||||||||||
19 | |||||||||||||||||||||
20 | |||||||||||||||||||||
TRAINING DATABASE |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2 | B2 | =LastSavedDateTime() |
D2 | D2 | =+TODAY() |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
H1 | Expression | ="f$3=""y""" | text | NO |
H1 | Cell | does not contain a blank value | text | NO |
S4:S8389 | Expression | =ISBLANK(S4)=TRUE | text | YES |
S4:S8389 | Cell Value | <NOW() | text | NO |
S4:S8389 | Cell Value | <NOW()+15 | text | NO |
S4:S8389 | Cell Value | between NOW()+15 and NOW()+30 | text | NO |
C4 | Expression | ="f$3=""y""" | text | NO |
C4 | Cell | does not contain a blank value | text | NO |
O3:P3 | Expression | ="f$3=""y""" | text | NO |
O3:P3 | Cell | does not contain a blank value | text | NO |
O1:P1 | Expression | ="f$3=""y""" | text | NO |
O1:P1 | Cell | does not contain a blank value | text | NO |
A3:D3,Q1:S1,Q3:S3,E1:G1,I1:N1,E2:N3,O2:S2 | Expression | ="f$3=""y""" | text | NO |
A3:D3,Q1:S1,Q3:S3,E1:G1,I1:N1,E2:N3,O2:S2 | Cell | does not contain a blank value | text | NO |