I have an user form where I can search and save machine data.
Now I need the code how to do update when needed.
Below is also photo what I mean. "Tallenna uusi kone" is in english "Save machine data."
"Päivitä konetiedot" means "Update machine data."
Below is searching code:
And below is code where Machine's Data are saved:
Now I need the code how to do update when needed.
Below is also photo what I mean. "Tallenna uusi kone" is in english "Save machine data."
"Päivitä konetiedot" means "Update machine data."
Below is searching code:
VBA Code:
Private Sub cmdSearch_Click()
Dim RowNum As Long
Dim SearchRow As Long
RowNum = 2
SearchRow = 2
Worksheets("Machine Data").Activate
Do Until Cells(RowNum, 1).Value = ""
If InStr(1, Cells(RowNum, 1).Value, txtKeywords.Value, vbTextCompare) > 0 _
Or InStr(1, Cells(RowNum, 2).Value, txtKeywords.Value, vbTextCompare) > 0 _
Or InStr(1, Cells(RowNum, 3).Value, txtKeywords.Value, vbTextCompare) > 0 Then
Worksheets("Product Search").Cells(SearchRow, 1).Resize(, 27).Value = Cells(RowNum, 1).Resize(, 27).Value
SearchRow = SearchRow + 1
End If
RowNum = RowNum + 1
Loop
If SearchRow = 2 Then
MsgBox "Konetta ei löydy. Lisää uusi ja tallenna konekortti!"
Exit Sub
End If
lstSearchResults.RowSource = "SearchResults"
End Sub
And below is code where Machine's Data are saved:
VBA Code:
Private Sub cmdSave_Click()
'Tallentaa syötetyt konetiedot Machine Data taulukkoon
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Machine Data")
Dim lr As Long
lr = Sheets("Machine Data").Range("A" & Rows.Count).End(xlUp).Row
Dim response
response = MsgBox("Tallennetaan uudet konetiedot?", vbYesNo, "KONEKORTTI")
If response = vbNo Then
Windows("Data.xlsm").Activate
Selection.AutoFilter
Windows("Data.xlsm").Activate
Exit Sub
End If
MsgBox ("Konekortti tallennetaan."), vbInformation
''''''''Ilmoitukset puuttuvista kriittisistä tiedoista''''''''
If Me.txtSarjanro.Value = "" Then
MsgBox "Sarjanumero puuttuu!", vbCritical
Exit Sub
End If
If Me.txtKonevalmistaja.Value = "" Then
MsgBox "Konevalmistaja puuttuu!", vbCritical
Exit Sub
End If
If Me.txtMalli.Value = "" Then
MsgBox "Koneen mallitieto puuttuu!", vbCritical
Exit Sub
End If
''''''' Lisää data Machine data välilehdelle '''''''
With sh
.Cells(lr + 1, "A").Value = Me.txtSarjanro.Value
.Cells(lr + 1, "B").Value = Me.txtKonevalmistaja.Value
.Cells(lr + 1, "C").Value = Me.txtMalli.Value
.Cells(lr + 1, "D").Value = Me.txtVuosimalli.Value
.Cells(lr + 1, "E").Value = Me.cmbMastotyyppi.Value
.Cells(lr + 1, "F").Value = Me.txtMastosno.Value
.Cells(lr + 1, "G").Value = Me.txtNostokorkeus.Value
.Cells(lr + 1, "H").Value = Me.txtVapaanosto.Value
.Cells(lr + 1, "I").Value = Me.txtAsetinlaite.Value
.Cells(lr + 1, "J").Value = Me.txtAsetinlaitesno.Value
.Cells(lr + 1, "K").Value = Me.txtHaarukat.Value
.Cells(lr + 1, "L").Value = Me.txtMoottori.Value
.Cells(lr + 1, "M").Value = Me.txtMoottorisno.Value
.Cells(lr + 1, "N").Value = Me.txtAkku.Value
.Cells(lr + 1, "O").Value = Me.txtEturenkaat.Value
.Cells(lr + 1, "P").Value = Me.txtTakarenkaat.Value
.Cells(lr + 1, "Q").Value = Me.cmbKäyttövoima.Value
.Cells(lr + 1, "R").Value = Me.cmbOhjaamo.Value
.Cells(lr + 1, "S").Value = Me.txtLisälaite.Value
.Cells(lr + 1, "T").Value = Me.txtLisälaitesno.Value
.Cells(lr + 1, "U").Value = Me.txtMuuvaruste1.Value
.Cells(lr + 1, "V").Value = Me.txtMuuvaruste2.Value
.Cells(lr + 1, "W").Value = Me.cmbKuormapyörät.Value
.Cells(lr + 1, "X").Value = Me.txtKuormapyörä.Value
.Cells(lr + 1, "Y").Value = Me.txtVetopyörä.Value
.Cells(lr + 1, "Z").Value = Me.txtTukipyörä.Value
.Cells(lr + 1, "AA").Value = Me.txtIstuin.Value
''''' Tyhjennä lomake '''''''''''
Me.txtSarjanro.Value = ""
Me.txtKonevalmistaja.Value = ""
Me.txtMalli.Value = ""
Me.txtVuosimalli.Value = ""
Me.cmbMastotyyppi.Value = ""
Me.txtMastosno.Value = ""
Me.txtNostokorkeus.Value = ""
Me.txtVapaanosto.Value = ""
Me.txtAsetinlaite.Value = ""
Me.txtAsetinlaitesno.Value = ""
Me.txtHaarukat.Value = ""
Me.txtMoottori.Value = ""
Me.txtMoottorisno.Value = ""
Me.txtAkku.Value = ""
Me.txtEturenkaat.Value = ""
Me.txtTakarenkaat.Value = ""
Me.cmbKäyttövoima.Value = ""
Me.cmbOhjaamo.Value = ""
Me.txtLisälaite.Value = ""
Me.txtLisälaitesno.Value = ""
Me.txtMuuvaruste1.Value = ""
Me.txtMuuvaruste2.Value = ""
Me.cmbKuormapyörät.Value = ""
Me.txtKuormapyörä.Value = ""
Me.txtVetopyörä.Value = ""
Me.txtTukipyörä.Value = ""
Me.txtIstuin.Value = ""
txtSarjanro.SetFocus
End With
End Sub