listbox -shows table of information - option to select and edit ro

[Roger on Excel]

I have a listbox which displays a table of chemicals (1st column) and their
properties - density (2nd column) and other propeties (3rd, 4th, 5th column
etc)

When I open the userform the table of chemicals is displayed and i can
select a row of chemicals (highlighted in blue).

I already have another userform which allows me to enter new chemicals to
add to the list and I have txt boxes for entering the chemical data

Here is the code i use for that

Private Sub cmdAdd_Click()
Application.EnableEvents = False
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Chemicals")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


'check for a part number
If Trim(Me.txtChemical.Value) = "" Then
Me.txtChemical.SetFocus
MsgBox "Please enter a chemical name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtChemical.Value
ws.Cells(iRow, 2).Value = Me.txtCAS.Value
ws.Cells(iRow, 3).Value = Me.txtMW.Value
ws.Cells(iRow, 4).Value = Me.txtDensity.Value
ws.Cells(iRow, 5).Value = Me.txtMP.Value
ws.Cells(iRow, 6).Value = Me.txtBP.Value
ws.Cells(iRow, 7).Value = Me.txtFP.Value
ws.Cells(iRow, 8).Value = Me.txtMSDS.Value

'clear the data
Me.txtChemical.Value = ""
Me.txtCAS.Value = ""
Me.txtMW.Value = ""
Me.txtDensity.Value = ""
Me.txtMP.Value = ""
Me.txtBP.Value = ""
Me.txtFP.Value = ""
Me.txtMSDS.Value = ""


Me.txtChemical.SetFocus
'Application.EnableEvents = True
End Sub

What I am looking to do on the first userform (which displays all the
chemicals is to select a row and have that data pulled to the different text
boxes so that the individual properties can be edited and the table updated.

Can anyone help me with this or dierct me to a link whereby a table of data
can be appended to and/or edited.

regards,

Roger

 

[Billy]

I have a listbox which displays a table of chemicals (1st column) and their
properties - density (2nd column) and other propeties (3rd, 4th, 5th column
etc)

When I open the userform the table of chemicals is displayed and i can
select a row of chemicals (highlighted in blue).

I already have another userform which allows me to enter new chemicals to
add to the list and I have txt boxes for entering the chemical data

Here is the code i use for that

Private Sub cmdAdd_Click()
Application.EnableEvents = False
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Chemicals")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.txtChemical.Value) = "" Then
  Me.txtChemical.SetFocus
  MsgBox "Please enter a chemical name"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtChemical.Value
ws.Cells(iRow, 2).Value = Me.txtCAS.Value
ws.Cells(iRow, 3).Value = Me.txtMW.Value
ws.Cells(iRow, 4).Value = Me.txtDensity.Value
ws.Cells(iRow, 5).Value = Me.txtMP.Value
ws.Cells(iRow, 6).Value = Me.txtBP.Value
ws.Cells(iRow, 7).Value = Me.txtFP.Value
ws.Cells(iRow, 8).Value = Me.txtMSDS.Value

'clear the data
Me.txtChemical.Value = ""
Me.txtCAS.Value = ""
Me.txtMW.Value = ""
Me.txtDensity.Value = ""
Me.txtMP.Value = ""
Me.txtBP.Value = ""
Me.txtFP.Value = ""
Me.txtMSDS.Value = ""

Me.txtChemical.SetFocus
'Application.EnableEvents = True
End Sub

What I am looking to do on the first userform (which displays all the
chemicals is to select a row and have that data pulled to the different text
boxes so that the individual properties can be edited and the table updated.

Can anyone help me with this or dierct me to a link whereby a table of data
can be appended to and/or edited.

regards,

Roger

Hi Roger,

If I understand your problem correctly you want to edit a chemical so
that the data behind is updated?

If so what I tend to do is use the listbox double click event, from
this get the data from the list box and populate the text boxes of the
form:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' where userform2 is the name of the form containing the textboxes
userform2.txtChemical = Me.ListBox1.Column(0,
Me.ListBox1.ListIndex)
userform2.txtCAS = Me.ListBox1.Column(1, Me.ListBox1.ListIndex)
' And so on
End Sub

that you use to add a new chemical as its easier to reuse the form,
there only two changes you will have to do, firstly change iRow but to
do this you will need a flag on your userform2 form to tell it whether
the form is adding a chemical or editing a chemical, the easiest way
to do this is to add a text box on the form and change the textbox
property to visible=false, in the listbox double click event also
populate the hidden textbox with say "EDIT" therefore you can then use
this in an if statement for iRow, something like:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' where userform2 is the name of the form containing the textboxes
userform2.txtChemical = Me.ListBox1.Column(0,
Me.ListBox1.ListIndex)
userform2.txtCAS = Me.ListBox1.Column(1, Me.ListBox1.ListIndex)
' And so on
userform2.txtHiddenTextBox = "EDIT"
End Sub

Private Sub cmdAdd_Click()
Application.EnableEvents = False
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Chemicals")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

If me.hiddentextbox = "EDIT" then
iRow = application.match(me.txtChemical, ws.range("A:A"),0)
else
iRow = iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
end if

The rest of your code should just then work, normally I would test the
match to see if it returns an error but as you are checking against
the source data of the form, it can never not exist.

Any problems let me know,

James

 

[Billy]

Hi Roger,

If I understand your problem correctly you want to edit a chemical so
that the data behind is updated?

If so what I tend to do is use the listbox double click event, from
this get the data from the list box and populate the text boxes of the
form:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'   where userform2 is the name of the form containing the textboxes
    userform2.txtChemical = Me.ListBox1.Column(0,
Me.ListBox1.ListIndex)
    userform2.txtCAS = Me.ListBox1.Column(1, Me.ListBox1.ListIndex)
'   And so on
End Sub

that you use to add a new chemical as its easier to reuse the form,
there only two changes you will have to do, firstly change iRow but to
do this you will need a flag on your userform2 form to tell it whether
the form is adding a chemical or editing a chemical, the easiest way
to do this is to add a text box on the form and change the textbox
property to visible=false, in the listbox double click event also
populate the hidden textbox with say "EDIT" therefore you can then use
this in an if statement for iRow, something like:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'   where userform2 is the name of the form containing the textboxes
    userform2.txtChemical = Me.ListBox1.Column(0,
Me.ListBox1.ListIndex)
    userform2.txtCAS = Me.ListBox1.Column(1, Me.ListBox1.ListIndex)
'   And so on
    userform2.txtHiddenTextBox = "EDIT"
End Sub



If me.hiddentextbox = "EDIT" then
    iRow = application.match(me.txtChemical, ws.range("A:A"),0)
else
    iRow = iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
end if

The rest of your code should just then work, normally I would test the
match to see if it returns an error but as you are checking against
the source data of the form, it can never not exist.

Any problems let me know,

James

Hi Roger,

Just reread my post and the bottom code should be the if statement:

If me.hiddentextbox = "EDIT" then
iRow = application.match(me.txtChemical, ws.range("A:A"),0)
else
iRow = iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
end if

replaces your original iRow statement:

It will work either way but if its left in it will be less efficient.

James