CODE FOR FORM FOR A USER TO BE ABLE TO UPDATE THE USER FORM

Ramarian_1967

New Member
Joined
Aug 5, 2022
Messages
1
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. 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.


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
ABCDEFGHIJKLMNOPQRS
1GLOBALWHMIS ORIENTATIONS RESPIRATOR
2LAST SAVED DATE-July 29, 2022TODAYS DATE -August 5, 20223 YEARS
3EMPLOYEE NAMEEMPLOYEE IDAREAJOB TITLE
4JOE BLOGGS12345OFFICEHELPER2022-02-032020-02-042020-02-052022-05-27
5JOHN DOE34567ADMINSUPERVISOR2019-02-072019-02-082019-03-022022-05-27
6PAUL MAUL78901MAINTENANCECLEANER2021-05-092021-05-092021-05-102021-05-11
7DON BLAST23456MAINTENANCEMAINTENANCE 2022-02-02
8PETER WING78901OPERATIONSOPERATOR2022-02-022022-03-02
9LEN ROE23456MAINTENANCEELECTRICIAN2022-02-062022-07-082022-09-09
10
11
12
13
14
15
16
17
18
19
20
TRAINING DATABASE
Cell Formulas
RangeFormula
B2B2=LastSavedDateTime()
D2D2=+TODAY()
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H1Expression="f$3=""y"""textNO
H1Celldoes not contain a blank value textNO
S4:S8389Expression=ISBLANK(S4)=TRUEtextYES
S4:S8389Cell Value<NOW()textNO
S4:S8389Cell Value<NOW()+15textNO
S4:S8389Cell Valuebetween NOW()+15 and NOW()+30textNO
C4Expression="f$3=""y"""textNO
C4Celldoes not contain a blank value textNO
O3:P3Expression="f$3=""y"""textNO
O3:P3Celldoes not contain a blank value textNO
O1:P1Expression="f$3=""y"""textNO
O1:P1Celldoes not contain a blank value textNO
A3:D3,Q1:S1,Q3:S3,E1:G1,I1:N1,E2:N3,O2:S2Expression="f$3=""y"""textNO
A3:D3,Q1:S1,Q3:S3,E1:G1,I1:N1,E2:N3,O2:S2Celldoes not contain a blank value textNO
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
A couple of questions...
Is CommandButton1 the ‘UPDATE EMPLOYEE RECORD’ button?
If the Employee ID text box is already on the form you have, why do you need another form? Do you need to restrict access on who can put in a new ID or update info, or some other reason?

I'll suggest you remark the code so you can tell what the buttons do, e.g. under Private Sub CommandButton2_Click(), I'd put this:
VBA Code:
'clear userform text boxes

If you want to have a separate box, an InputBox is a cheap and dirty way to do it, there's no formatting of userforms involved. Add this line after your Dim line:
VBA Code:
Dim x As Variant
'gives the output of the Input Box to a variable
x = Application.InputBox("Enter New Employee Number", "New Employee", Type:=1)
'check to see if they hit Cancel
If x = False Then
    Exit Sub
'otherwise
Else
'set the Employee Number in the form field.
<Change this to your UserForm Name>.txtEMPLOYEEID.Value = x '
End If
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top