Userform to modify data in different workbooks

moylara

New Member
Joined
Oct 13, 2019
Messages
1
Hey guys,
Hope everyone is doing great. At my current job we use several workbooks to store data regarding different steps of a process. Tehe thing is that some data is the same through the different workbooks. I have been working on a vba app that would allow me to store the data needed on all the different workbooks by just capturing it once. Most of the code for the database needed to capture the data is working although I have not been able to make the modify data button work quite well. I am able to modify the record on the current workbook however when I try to save the changes made on a different workbook I am unable to make it work the way I intended. I'm working on 2 different versions of office, at home I have Office 365 while at work I have Office 2010. At work the current code is pretty much working, however at home my code behaves differently. At times it either updates the first record on my database, others it "updates" the record but it actually creates the record on the same row number where I have the actual record on the workbook where I have the data and not on the one with the actual data. Can anyone help me? I would like the code to be able to run on both versions of office doing what I intend it to do.
Also is there a way in which I can upload the files in case anyone wants to give a full review to my code and if you see any improvements you could let me know?

Code:
Private Sub cmdModificar_Click()Set sh = ThisWorkbook.Sheets("Concentrado")


answer = MsgBox("Esta seguro de que quiere realizar cambios a ese registro?", vbYesNo + vbQuestion, "Modificar Registro")
If answer = vbNo Then


End If


If answer = vbYes Then
sh.Cells(currentrow, 1).Value = Me.txtSiniestro.Value
sh.Cells(currentrow, 2).Value = Me.txtReporte.Value
sh.Cells(currentrow, 3).Value = Me.txtAsegurado.Value
sh.Cells(currentrow, 4).Value = Me.txtBeneficiario.Value
sh.Cells(currentrow, 5).Value = Me.txtMarca.Value
sh.Cells(currentrow, 6).Value = Me.txtTipo.Value
sh.Cells(currentrow, 7).Value = Me.txtYear.Value
sh.Cells(currentrow, 8).Value = Me.txtOcurrido.Value
sh.Cells(currentrow, 9).Value = Me.txtRecibido.Value
sh.Cells(currentrow, 10).Value = Me.txtComentarios.Value




'''''''''''Modificar valores en libro Progreso Siniestros'''''''''''''''From here everything goes south at home'''''''''''''
' Registrando variables utilizadas para identificar el libro y hoja
' With these I declare the variables of the book and sheet
Dim wbps As Workbook
Dim wse As Worksheet


    ' Registrando el valor de las variables de este libro
    ' I use these to open the book and set the value of the wse variable
    Set wbps = Workbooks.Open(Filename:="C:\Users\moyla\Documents\PTS Prueba\ProgresoSiniestros.xlsm")
    Set wse = wbps.Worksheets("Estatus")
    
            ' Declarando las variables de busqueda del registro en Progreso Siniestros
        ' Declare the variables that I use to search in at this book     
        Dim trow As Long, b As Long
        trow = wse.Range("A1").CurrentRegion.Rows.count
        For b = 2 To totRows
               
                          
            ' Indicando accion a tomar una vez que se encuentra la variable
            ' Indicates the action once the value is found
            If Trim(wse.Cells(b, 1)) = Trim(txtSiniestro.Text) Then
            currentrow = b ' Indicates the new position of currentrow
                       
            Exit For
            End If
            Next b
              
            ' Modificando la variable en el libro
            ' Modifies the values at this book''''''''This is the part that I have not been able to make it work at home
            ' Like I said before, here the value is stored on the exact row where the info I updated on the current book is
            ' For example if the record is on row 957 of the book I made this user form, the data is stored on row 957 of 
            ' wse instead of row 956 where the current record is, no change is made on this row at all. At work the change
            ' does happen.
            wse.Cells(currentrow, 1).Value = Me.txtSiniestro.Value
            wse.Cells(currentrow, 2).Value = Me.txtReporte.Value
            wse.Cells(currentrow, 3).Value = Me.txtAsegurado.Value
            wse.Cells(currentrow, 4).Value = Me.txtBeneficiario.Value
            wse.Cells(currentrow, 5).Value = Me.txtMarca.Value
            wse.Cells(currentrow, 6).Value = Me.txtTipo.Value
            wse.Cells(currentrow, 7).Value = Me.txtYear.Value
            wse.Cells(currentrow, 8).Value = Me.txtOcurrido.Value
            wse.Cells(currentrow, 11).Value = Me.txtRecibido.Value
            
            ' Guardando la informacion y cerrando Progreso de Siniestros
            'Store the data and close the workbook
            wbps.Save
            wbps.Close
    
    
 ''''''''''Modificar registros en libro Carta de Baja de Placas''''''''Same happens with this other book
' Registrando variables utilizadas para identificar el libro y hoja
Dim wbcbp As Workbook
Dim wsd As Worksheet




    ' Registrando el valor de las variables y abriendo el libro
    Set wbcbp = Workbooks.Open(Filename:="C:\Users\moyla\Documents\PTS Prueba\CBP.xlsm")
    Set wsd = wbcbp.Worksheets("Datos")
    
    
      ' Declarando las variables de busqueda del registro en CBP
        Dim trw As Long, e As Long
        trw = wsd.Range("A1").CurrentRegion.Rows.count
        For e = 2 To totRows
            
         
            ' Indicando accion a tomar una vez que se encuentra la variable
            If Trim(wsd.Cells(e, 1)) = Trim(txtSiniestro.Text) Then
            currentrow = e 'Captura la posicion del registro como la variable currentrow
              ' Modificando la variable en el libro''''''' When I tried the code like this it does not make any changes at all,
              ' or at times it saves the data on the first row modifying the current value that is a completely different record
            wsd.Cells(currentrow, 1).Value = Me.txtSiniestro.Value
            wsd.Cells(currentrow, 2).Value = Me.txtReporte.Value
            wsd.Cells(currentrow, 6).Value = Me.txtMarca.Value
            wsd.Cells(currentrow, 7).Value = Me.txtTipo.Value
            wsd.Cells(currentrow, 8).Value = Me.txtYear.Value
            wsd.Cells(currentrow, 11).Value = Me.txtOcurrido.Value
 
            Exit For
            End If
             Next e
     
      
            ' Guardando la informacion y cerrando Carta de Baja de Placas
            wbcbp.Save
            wbcbp.Close
End If
End Sub
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,099,566
Messages
5,469,446
Members
406,652
Latest member
LJA

This Week's Hot Topics

Top