User form, how to update previously saved data?

JarmoVee

New Member
Joined
May 27, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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:
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
 

Attachments

  • User Form.JPG
    User Form.JPG
    205 KB · Views: 5

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I forgot this...

This code displays the information in the text boxes when I select the machine I retrieved from the listbox. How can I update changed or added information on a previously saved machine?
VBA Code:
Private Sub lstSearchResults_AfterUpdate()

Dim ws As Worksheet
Dim iRow As Long

Set ws = ThisWorkbook.Worksheets("Product Search")
With Me
    iRow = Me.lstSearchResults.ListIndex + 2

    .txtSarjanro.Value = ws.Cells(iRow, 1)
    .txtKonevalmistaja.Value = ws.Cells(iRow, 2)
    .txtMalli.Value = ws.Cells(iRow, 3)
    .txtVuosimalli.Value = ws.Cells(iRow, 4)
    .cmbMastotyyppi.Value = ws.Cells(iRow, 5)
    .txtMastosno.Value = ws.Cells(iRow, 6)
    .txtNostokorkeus.Value = ws.Cells(iRow, 7)
    .txtVapaanosto.Value = ws.Cells(iRow, 8)
    .txtAsetinlaite.Value = ws.Cells(iRow, 9)
    .txtAsetinlaitesno.Value = ws.Cells(iRow, 10)
    .txtHaarukat.Value = ws.Cells(iRow, 11)
    .txtMoottori.Value = ws.Cells(iRow, 12)
    .txtMoottorisno.Value = ws.Cells(iRow, 13)
    .txtAkku.Value = ws.Cells(iRow, 14)
    .txtEturenkaat.Value = ws.Cells(iRow, 15)
    .txtTakarenkaat.Value = ws.Cells(iRow, 16)
    .cmbKäyttövoima.Value = ws.Cells(iRow, 17)
    .cmbOhjaamo.Value = ws.Cells(iRow, 18)
    .txtLisälaite.Value = ws.Cells(iRow, 19)
    .txtLisälaitesno.Value = ws.Cells(iRow, 20)
    .txtMuuvaruste1.Value = ws.Cells(iRow, 21)
    .txtMuuvaruste2.Value = ws.Cells(iRow, 22)
    .cmbKuormapyörät.Value = ws.Cells(iRow, 23)
    .txtKuormapyörä.Value = ws.Cells(iRow, 24)
    .txtVetopyörä.Value = ws.Cells(iRow, 25)
    .txtTukipyörä.Value = ws.Cells(iRow, 26)
    .txtIstuin.Value = ws.Cells(iRow, 27)
    
End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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