Update Cells from Form Listbox

[Simon Campbell]

I am so close, yet so far..
I have a simple UserForm (frm_EditFracGrad), with a list box (ltb_FracGrad) displaying 2 columns of data from a Sheet "EXTRAS", from Range "B80:C92"
I have two textboxes (txt_EditPressure & txt_EditDepth) in the User Form that display a selected row from the Listbox. in order to edit the displayed values I have a command button (cmd_SaveEditFracGrad) that transfers the new values back to the Spreadsheet. When I perform the operation I only seem to be able to edit and Save a single column from the Listbox, It will not work for both.

I'm a novice in VBA and Desparatly need some help on this... can anyone show me what I'm doing wrong

The Code is
'Initialize Form
Private Sub UserForm_Initialize()
me.ltb_FracGrad.List = ThisWorkbook.Worksheets("EXTRAS").Range("B80:C92").Value
End Sub

'Select Row in Listbox
Private Sub ltb_FracGrad_Click()
frm_EditFracGrad.txt_EditPressure.Value = Me.ltb_FracGrad.Column(0)
frm_EditFracGrad.txt_EditDepth.Value = Me.ltb_FracGrad.Column(1)
End Sub

'Save Data back to Sheet
Private Sub cmd_SaveEditFracGrad_Click()
frm_EditFracGrad.ltb_FracGrad.Column(0) = txt_EditPressure.Value
frm_EditFracGrad.ltb_FracGrad.Column(1) = txt_EditDepth.Value

Dim x
x = Me.ltb_FracGrad.List
ThisWorkbook.Worksheets("EXTRAS").Range("B80:C92").Value = x

End Sub

 

[Fluff]

How about

With Me.ltb_FracGrad
   .List(.ListIndex, 1) = Me.txt_EditPressure
   .List(.ListIndex, 0) = Me.txt_EditDepth
   
   ThisWorkbook.Worksheets("EXTRAS").Range("B80").Offset(.ListIndex) = Me.txt_EditDepth
   ThisWorkbook.Worksheets("EXTRAS").Range("B80").Offset(.ListIndex, 1) = Me.txt_EditPressure
End With

 

[DanteAmor]

Note: It is not necessary to put the name of the userform or Me in the code, it is only necessary when working with several userforms.

Try this:

Dim updating As Boolean   'At the beginning of all the code

'Initialize Form
Private Sub UserForm_Initialize()
  ltb_FracGrad.List = ThisWorkbook.Worksheets("EXTRAS").Range("B80:C92").Value
End Sub

'Select Row in Listbox
Private Sub ltb_FracGrad_Click()
  If updating = True Then Exit Sub
  txt_EditPressure.Value = ltb_FracGrad.Column(0)
  txt_EditDepth.Value = ltb_FracGrad.Column(1)
End Sub

'Save Data back to Sheet
Private Sub cmd_SaveEditFracGrad_Click()
  updating = True
  With ltb_FracGrad
    .Column(0) = txt_EditPressure.Value
    .Column(1) = txt_EditDepth.Value
    ThisWorkbook.Worksheets("EXTRAS").Range("B80:C92").Value = .List
  End With
  updating = False
End Sub

 

[Simon Campbell]

Note: It is not necessary to put the name of the userform or Me in the code, it is only necessary when working with several userforms.

Try this:

Dim updating As Boolean   'At the beginning of all the code

'Initialize Form
Private Sub UserForm_Initialize()
  ltb_FracGrad.List = ThisWorkbook.Worksheets("EXTRAS").Range("B80:C92").Value
End Sub

'Select Row in Listbox
Private Sub ltb_FracGrad_Click()
  If updating = True Then Exit Sub
  txt_EditPressure.Value = ltb_FracGrad.Column(0)
  txt_EditDepth.Value = ltb_FracGrad.Column(1)
End Sub

'Save Data back to Sheet
Private Sub cmd_SaveEditFracGrad_Click()
  updating = True
  With ltb_FracGrad
    .Column(0) = txt_EditPressure.Value
    .Column(1) = txt_EditDepth.Value
    ThisWorkbook.Worksheets("EXTRAS").Range("B80:C92").Value = .List
  End With
  updating = False
End Sub

DanteAmor
Many thanks.. this did seem to solve the problem.. Much appreciate your time and help. Quick question... what does the "updating = TRUE/FALSE" actually do and why do I need it.

Simon

 

[Simon Campbell]

How about

With Me.ltb_FracGrad
   .List(.ListIndex, 1) = Me.txt_EditPressure
   .List(.ListIndex, 0) = Me.txt_EditDepth
  
   ThisWorkbook.Worksheets("EXTRAS").Range("B80").Offset(.ListIndex) = Me.txt_EditDepth
   ThisWorkbook.Worksheets("EXTRAS").Range("B80").Offset(.ListIndex, 1) = Me.txt_EditPressure
End With

Fluff, Did try your code, but seemed to still get the same problem.. DanteAmor gave me a code that seemed to work. Much appreciate your time and effort in helping.
Simon

 

[Fluff]

Fluff, Did try your code, but seemed to still get the same problem.

Odd, as it worked for me, but glad you have a solution.

 

[DanteAmor]

Quick question... what does the "updating = TRUE/FALSE" actually do and why do I need it.

    .Column(0) = txt_EditPressure.Value

That instruction execute the ltb_FracGrad_Click event again, that updates the textbox again, but in this case, the second textbox is updated with the "old" value that the listbox has, so when you update the sheet it puts the "old" value again instead of the "new".

What we do with the updating variable is put it in TRUE when it is updating the listbox, then when it tries to run the again ltb_FracGrad_Click event , as it is in TRUE then it leaves the event, finishes updating the listbox, the sheet and returns the state of the updating variable to FALSE.

@Fluff 's idea is correct, only that he turned the controls upside down, so it would be correct:

  With Me.ltb_FracGrad
    .List(.ListIndex, 1) = Me.txt_EditDepth
    .List(.ListIndex, 0) = Me.txt_EditPressure
  
    ThisWorkbook.Worksheets("EXTRAS").Range("B80").Offset(.ListIndex, 1) = Me.txt_EditDepth
    ThisWorkbook.Worksheets("EXTRAS").Range("B80").Offset(.ListIndex) = Me.txt_EditPressure
  End With

----
Another way, instead of the List property, you could use the RowSorce property, with this you only need to update the sheet:

'Initialize Form
Private Sub UserForm_Initialize()
  ltb_FracGrad.RowSource = "EXTRAS!B80:C92"
End Sub

'Select Row in Listbox
Private Sub ltb_FracGrad_Click()
  txt_EditPressure.Value = ltb_FracGrad.Column(0)
  txt_EditDepth.Value = ltb_FracGrad.Column(1)
End Sub

'Save Data back to Sheet
Private Sub cmd_SaveEditFracGrad_Click()
  ThisWorkbook.Worksheets("EXTRAS").Range("B" & ltb_FracGrad.ListIndex + 80).Resize(1, 2).Value = Array(txt_EditPressure, txt_EditDepth)
End Sub