VBA to update records via userform

Le 20/01/2016 à 13:51

J'ai un formulaire qui me permet d'ajouter une nouvelle entrée dans ma database et aussi de voir les infos deja existante.

J'ai essaye de creer un bouton "save" afin de modifier / completer les infos deja existantes mais il m'apparait qu'a cause du code "GetData" je ne peux pas editer les donnees. En effet par exemple pour une donnee REF= 2016ct003 certaines infos sont deja rentrees mais la colonne "COD"="Aj" est vide et doit etre mise a jour a posteriori. Hors le get data qui permet d'afficher les donnees marque "vide" et refuse toute modification.....

Voici le code "GETDATA", quelqu'un pourriat il m'aider a le modifier afin de pouvoir modifier toutes les donnees pour une reference donnee?

Private Sub GetData()

Dim IROW As Long
Dim Irow1 As Long
Dim IROW2 As Long

Dim WSH As Worksheet
Dim WSH1 As Worksheet
Dim WSH2 As Worksheet
Dim REF As String

Set WSH = ThisWorkbook.Worksheets("RECAP")
Set WSH1 = ThisWorkbook.Worksheets("PRICING FINAL")
Set WSH2 = ThisWorkbook.Worksheets("PANDL")

IROW = WSH.Cells.Find(What:=Me.REF.Value, LookIn:=xlValues, SearchOrder:=xlRows, SearchDirection:=xlPrevious).row
Irow1 = WSH1.Cells.Find(What:=Me.REF.Value, LookIn:=xlValues, SearchOrder:=xlRows, SearchDirection:=xlPrevious).row
IROW2 = WSH2.Cells.Find(What:=Me.REF.Value, LookIn:=xlValues, SearchOrder:=xlRows, SearchDirection:=xlPrevious).row

'COPY THE DATA TO USERFORM
With ThisWorkbook.Worksheets("RECAP")
Me.REF.Value = .Cells(IROW, "C").Value
Me.MONTH.Value = .Cells(IROW, "A").Value
Me.PANDL_MONTH.Value = WSH.Cells(IROW, "A").Value
Me.PANDL_REF.Value = Me.REF.Value
Me.OIC.Value = WSH.Cells(IROW, "D").Value
Me.PANDL_OIC.Value = WSH.Cells(IROW, "D").Value
Me.VSL.Value = WSH.Cells(IROW, "E").Value
Me.PANDL_VSL.Value = WSH.Cells(IROW, "E").Value
Me.GRADE.Value = WSH.Cells(IROW, "F").Value
Me.PANDL_GRADE.Value = WSH.Cells(IROW, "F").Value
Me.LP.Value = WSH.Cells(IROW, "G").Value
Me.PANDL_LP.Value = WSH.Cells(IROW, "G").Value
Me.CT_QTY.Value = WSH.Cells(IROW, "I").Value
Me.TOLERANCE.Value = WSH.Cells(IROW, "J").Value
Me.SUPPLIER.Value = WSH.Cells(IROW, "L").Value
Me.PANDL_SUPPLIER.Value = WSH.Cells(IROW, "L").Value
Me.TERM_P.Value = WSH.Cells(IROW, "M").Value
Me.PANDL_TERM_P.Value = WSH.Cells(IROW, "M").Value
Me.CT_DATES_TERM.Value = WSH.Cells(IROW, "N").Value
Me.CT_DATES.Value = WSH.Cells(IROW, "O").Value
Me.LP_INSP.Value = WSH.Cells(IROW, "R").Value
Me.LP_INSP_SHARING.Value = WSH.Cells(IROW, "S").Value
Me.LP_AGT.Value = WSH.Cells(IROW, "U").Value
Me.LP_AGT_CATEGORY.Value = WSH.Cells(IROW, "V").Value
Me.RCVRS.Value = WSH.Cells(IROW, "W").Value
Me.PANDL_RCVRS.Value = WSH.Cells(IROW, "W").Value
Me.TERMS_S.Value = WSH.Cells(IROW, "X").Value
Me.PANDL_TERMS_S.Value = WSH.Cells(IROW, "X").Value
Me.DP.Value = WSH.Cells(IROW, "Y").Value
Me.PANDL_DP.Value = WSH.Cells(IROW, "Y").Value
Me.REQ_MIN_QTY.Value = WSH.Cells(IROW, "Z").Value
Me.REQ_MAX_QTY.Value = WSH.Cells(IROW, "AA").Value
Me.DP_INSP.Value = WSH.Cells(IROW, "AD").Value
Me.DP_INSP_SHARING.Value = WSH.Cells(IROW, "AE").Value
Me.DP_AGT.Value = WSH.Cells(IROW, "AG").Value
Me.DP_AGT_CATEGORY.Value = WSH.Cells(IROW, "AH").Value
Me.BL_DATE.Value = WSH.Cells(IROW, "AI").Value
Me.PANDL_BL_DATE.Value = WSH.Cells(IROW, "AI").Value
Me.COD_DATE.Value = WSH.Cells(IROW, "AJ").Value
Me.BL_GROSS_QTY_BBLS.Value = WSH.Cells(IROW, "AL").Value
Me.BL_GROSS_QTY_MTS.Value = WSH.Cells(IROW, "AK").Value
Me.PANDL_BL_GROSS_QTY.Value = WSH.Cells(IROW, "AK").Value
Me.BL_NET_QTY_BBLS.Value = WSH.Cells(IROW, "AM").Value
Me.PANDL_BL_NET_QTY.Value = WSH.Cells(IROW, "AM").Value
Me.BL_NET_QTY_MTS.Value = WSH.Cells(IROW, "AN").Value
Me.OT_NET_QTY_BBLS.Value = WSH.Cells(IROW, "AO").Value
Me.PANDL_OT_QTY.Value = WSH.Cells(IROW, "AO").Value
Me.OT_NET_QTY_MTS.Value = WSH.Cells(IROW, "AP").Value
End With
With ThisWorkbook.Worksheets("PRICING FINAL")
Me.INV_QTY_P.Value = WSH1.Cells(Irow1, "M").Value
Me.PANDL_INV_QTY_P_TERMS.Value = WSH1.Cells(Irow1, "M").Value
Me.PANDL_INV_QTY_P.Value = WSH1.Cells(Irow1, "N").Value
Me.PRICING_P.Value = WSH1.Cells(Irow1, "O").Value
Me.PANDL_PRICING_P.Value = WSH1.Cells(Irow1, "O").Value
Me.PRICING_S.Value = WSH1.Cells(Irow1, "AG").Value
Me.PANDL_PRICING_S.Value = WSH1.Cells(Irow1, "AG").Value
Me.PMT_P.Value = WSH1.Cells(Irow1, "P").Value
Me.OSP_P.Value = WSH1.Cells(Irow1, "R").Value
Me.PREM_P.Value = WSH1.Cells(Irow1, "S").Value
Me.PANDL_PREM_P.Value = WSH1.Cells(Irow1, "S").Value
Me.INV_QTY_S.Value = WSH1.Cells(Irow1, "AE").Value
Me.PANDL_INV_QTY_S_TERMS.Value = WSH1.Cells(Irow1, "AE").Value
Me.PANDL_INV_QTY_S.Value = WSH1.Cells(Irow1, "AF").Value
Me.INV_QTY_S.Value = WSH1.Cells(Irow1, "AE").Value
Me.PMT_S.Value = WSH1.Cells(Irow1, "AH").Value
Me.OSP_S.Value = WSH1.Cells(Irow1, "AJ").Value
Me.PREM_S.Value = WSH1.Cells(Irow1, "AK").Value
Me.PANDL_PREM_S.Value = WSH1.Cells(Irow1, "AK").Value
Me.MIN_FRT_QTY.Value = WSH1.Cells(Irow1, "AW").Value
Me.WS.Value = WSH1.Cells(Irow1, "AX").Value
Me.PANDL_PROV_P.Value = WSH1.Cells(Irow1, "X").Value
Me.PANDL_FINAL_P.Value = WSH1.Cells(Irow1, "W").Value
Me.PANDL_BAL_P.Value = WSH1.Cells(Irow1, "Y").Value
Me.PANDL_PROV_S.Value = WSH1.Cells(Irow1, "AP").Value
Me.PANDL_FINAL_S.Value = WSH1.Cells(Irow1, "AO").Value
Me.PANDL_BAL_S.Value = WSH1.Cells(Irow1, "AQ").Value
Me.PANDL_HEDGE.Value = WSH1.Cells(Irow1, "AT").Value
Me.PANDL_GROSS_FRT.Value = WSH1.Cells(Irow1, "BE").Value
Me.PANDL_SECA.Value = WSH1.Cells(Irow1, "BK").Value
Me.PANDL_TOT_SHIP.Value = WSH1.Cells(Irow1, "BQ").Value
End With
With ThisWorkbook.Worksheets("PANDL")
Me.PANDL_DEM_IN.Value = WSH2.Cells(IROW2, "AC").Value
Me.PANDL_DEM_OUT.Value = WSH2.Cells(IROW2, "T").Value
Me.PANDL.Value = WSH2.Cells(IROW2, "AI").Value
End With

Me.ROW_NR.Value = Right(Me.REF.Value, 3)
Me.BL_DATE.Value = Format(BL_DATE.Value, "dd-mmm-yy")
Me.PANDL_BL_DATE.Value = Format(BL_DATE.Value, "dd-mmm-yy")
Me.COD_DATE.Value = Format(COD_DATE.Value, "dd-mmm-yy")
Me.PREM_P.Value = Format(PREM_P.Value, "#,##0.00")
Me.PANDL_PREM_P.Value = Format(PREM_P.Value, "#,##0.00")
Me.OSP_P = Format(OSP_P.Value, "#,##0.00")
Me.PREM_S = Format(PREM_S.Value, "#,##0.00")
Me.OSP_S = Format(OSP_S.Value, "#,##0.00")
Me.CT_QTY = Format(CT_QTY.Value, "#,##0.00")
Me.MIN_FRT_QTY = Format(MIN_FRT_QTY.Value, "#,##0.00")
Me.BL_GROSS_QTY_MTS = Format(BL_GROSS_QTY_MTS.Value, "#,##0.00")
Me.PANDL_BL_GROSS_QTY = Format(PANDL_BL_GROSS_QTY.Value, "#,##0.00")
Me.BL_GROSS_QTY_BBLS = Format(BL_GROSS_QTY_BBLS.Value, "#,##0.00")
Me.BL_NET_QTY_MTS = Format(BL_NET_QTY_MTS.Value, "#,##0.00")
Me.BL_NET_QTY_BBLS = Format(BL_NET_QTY_BBLS.Value, "#,##0.00")
Me.PANDL_BL_NET_QTY = Format(BL_NET_QTY_BBLS.Value, "#,##0.00")
Me.OT_NET_QTY_BBLS = Format(OT_NET_QTY_BBLS.Value, "#,##0.00")
Me.OT_NET_QTY_MTS = Format(OT_NET_QTY_MTS.Value, "#,##0.00")
Me.PANDL_INV_QTY_P = Format(PANDL_INV_QTY_P.Value, "#,##0.00")
Me.PANDL_INV_QTY_S = Format(PANDL_INV_QTY_S.Value, "#,##0.00")
Me.PANDL_OT_QTY = Format(OT_NET_QTY_BBLS.Value, "#,##0.00")
Me.PANDL_PROV_P = Format(PANDL_PROV_P.Value, "#,##0.00")
Me.PANDL_PROV_S = Format(PANDL_PROV_S.Value, "#,##0.00")
Me.PANDL_FINAL_P = Format(PANDL_FINAL_P.Value, "#,##0.00")
Me.PANDL_FINAL_S = Format(PANDL_FINAL_S.Value, "#,##0.00")
Me.PANDL_BAL_P = Format(PANDL_BAL_P.Value, "#,##0.00")
Me.PANDL_BAL_S = Format(PANDL_BAL_S.Value, "#,##0.00")
Me.PANDL_GROSS_FRT = Format(PANDL_GROSS_FRT.Value, "#,##0.00")
Me.PANDL_SECA = Format(PANDL_SECA.Value, "#,##0.00")
Me.PANDL_TOT_SHIP = Format(PANDL_TOT_SHIP.Value, "#,##0.00")
Me.PANDL_DEM_IN = Format(PANDL_DEM_IN.Value, "#,##0.00")
Me.PANDL_DEM_OUT = Format(PANDL_DEM_OUT.Value, "#,##0.00")
Me.PANDL = Format(PANDL.Value, "#,##0.00")
Me.PANDL_HEDGE = Format(PANDL_HEDGE, "#,##0.00")
Me.TOLERANCE = Format(TOLERANCE.Value, "0%")
Me.LP_INSP_SHARING = Format(LP_INSP_SHARING.Value, "0%")
Me.DP_INSP_SHARING = Format(DP_INSP_SHARING.Value, "0%")

End Sub

je sais deja qu'afin de changer la worksheet par le formulaire le code doit etre inverse

ex: .Cells(IROW, "C").Value =Me.REF.Value

mais pour l'instant je ne peux rien modifier......

merci d'avance

Rechercher des sujets similaires à "vba update records via userform"