PDA

View Full Version : Solved: how can change the same data raw selected in ListBox?



rrosa1
11-23-2010, 10:23 PM
hi
i find this user form in forum online and i change to suite my need but it "Modify & Return"
button not work. As it suppose to change the same raw of sheet as in selected list box 1 raw . But instead of selected raw it change 2nd raw every time if u change the value in any textBox in user form
i hope i am explaining my problem with code.

i need to change the same raw which is selected in listbox.since listbox is populated from the sheet1
hear is my code fore the bot ton and also i attached the sample wb with commented info.
thanks for help

Private Sub CommandButton1_Click()
'Modify list and return to Page1

var1 = 1
Sheet1.Range("A5").Offset(ListBox1.ListIndex, 0).Value = ComboBox1
Sheet1.Range("B5").Offset(ListBox1.ListIndex, 0).Value = TextBox1
Sheet1.Range("C5").Offset(ListBox1.ListIndex, 0).Value = TextBox2
Sheet1.Range("D5").Offset(ListBox1.ListIndex, 0).Value = TextBox3
Sheet1.Range("E5").Offset(ListBox1.ListIndex, 0).Value = TextBox4
Sheet1.Range("F5").Offset(ListBox1.ListIndex, 0).Value = ComboBox2
TextBox5 = Round((TextBox4.Value / TextBox3.Value), 2)
TextBox6 = TextBox5.Value
Sheet1.Range("G5").Offset(ListBox1.ListIndex, 0).Value = TextBox6
Sheet1.Range("H5").Offset(ListBox1.ListIndex, 0).Value = TextBox7
Sheet1.Range("I5").Offset(ListBox1.ListIndex, 0).FormulaR1C1 = "=RC[-4]/RC[-5]"

var1 = Empty
ListBox1.TopIndex = ListBox1.ListIndex 'returns to same place on ListBox1
ListBox1.ListIndex = -1 'turns off selection on ListBox1
MultiPage1.Pages(0).Enabled = True
UF_List.MultiPage1.Value = 0
MultiPage1.Pages(1).Enabled = False


End Sub

Bob Phillips
11-24-2010, 01:10 AM
Private Sub CommandButton1_Click()
'Modify list and return to Page1
Dim Selected As Long

var1 = 1
Selected = ListBox1.ListIndex
With Sheet1

.Range("A5").Offset(Selected, 0).Value = ComboBox1
.Range("B5").Offset(Selected, 0).Value = TextBox1
.Range("C5").Offset(Selected, 0).Value = TextBox2
.Range("D5").Offset(Selected, 0).Value = TextBox3
.Range("E5").Offset(Selected, 0).Value = TextBox4
.Range("F5").Offset(Selected, 0).Value = ComboBox2
TextBox5 = Round((TextBox4.Value / TextBox3.Value), 2)
TextBox6 = TextBox5.Value
.Range("G5").Offset(Selected, 0).Value = TextBox6
.Range("H5").Offset(Selected, 0).Value = TextBox7
.Range("I5").Offset(Selected, 0).FormulaR1C1 = "=RC[-4]/RC[-5]"
End With

var1 = Empty
ListBox1.TopIndex = Selected 'returns to same place on ListBox1
ListBox1.ListIndex = -1 'turns off selection on ListBox1
MultiPage1.Pages(0).Enabled = True
UF_List.MultiPage1.Value = 0
MultiPage1.Pages(1).Enabled = False

End Sub

rrosa1
11-24-2010, 06:50 PM
thanks you very much xld