i have a multi column ListBox row source is a table range
called "Data". the information in "Data" comes form a
userform with several TextBox's and ComboBox's.one of the
columns in "Data" can be left unfilled, as the others are
required, the "Entry" userform will not run through the
sub to add the record if all the textbox/combobox's are
filled except for one, a text box to enter a completion
date. the lsit box displays the records in "Data" i
want the user to be able to DblClick the record in the
list box and have it bring up another userform
called "EnterCompletion" with one text box that the user
can enter the completion date in, and have that go into
the appropriate cell in "Data" the completion column is
8 columns to the right of A. i have been playing with
this:
If ListBox1.ListIndex < -1 Then
Rows(ListBox1.ListIndex + 2).Select
End If
and the only thing i cant get is how to say, "column I in
active row, value = EnterCompletion.TextBox1.value.
hanks for any help...spence

If ListBox1.ListIndex < -1 Then
Rows(ListBox1.ListIndex + 2).Select
Cells(ActiveCell.Row, "I").Value = _
EnterCompletion.TextBox1.value
Unload.EnterCompletion
End If

or skip the selecting and do

If ListBox1.ListIndex < -1 Then
Cells(ListBox1.ListIndex + 2, "I").Value = _
EnterCompletion.TextBox1.value
Unload.EnterCompletion
End If

This assume EnterCompletion is still loaded (though probably hidden)


--
Regards,
Tom Ogilvy

spence wrote in message
...
i have a multi column ListBox row source is a table range
called "Data". the information in "Data" comes form a
userform with several TextBox's and ComboBox's.one of the
columns in "Data" can be left unfilled, as the others are
required, the "Entry" userform will not run through the
sub to add the record if all the textbox/combobox's are
filled except for one, a text box to enter a completion
date. the lsit box displays the records in "Data" i
want the user to be able to DblClick the record in the
list box and have it bring up another userform
called "EnterCompletion" with one text box that the user
can enter the completion date in, and have that go into
the appropriate cell in "Data" the completion column is
8 columns to the right of A. i have been playing with
this:
If ListBox1.ListIndex < -1 Then
Rows(ListBox1.ListIndex + 2).Select
End If
and the only thing i cant get is how to say, "column I in
active row, value = EnterCompletion.TextBox1.value.
hanks for any help...spence

thanks, follow up question, how would i reference, a
range of cells in a particular row, the row corresponding
to the item selected in the list box, in another sub.
like if i wanted to delete the range("A3:I3"). i want to
hide this sheet and i cannot use the select method on a
hidden sheet.
-----Original Message-----
If ListBox1.ListIndex < -1 Then
Rows(ListBox1.ListIndex + 2).Select
Cells(ActiveCell.Row, "I").Value = _
EnterCompletion.TextBox1.value
Unload.EnterCompletion
End If

or skip the selecting and do

If ListBox1.ListIndex < -1 Then
Cells(ListBox1.ListIndex + 2, "I").Value = _
EnterCompletion.TextBox1.value
Unload.EnterCompletion
End If

This assume EnterCompletion is still loaded (though
probably hidden)


--
Regards,
Tom Ogilvy

spence wrote in
message
...
i have a multi column ListBox row source is a table
range
called "Data". the information in "Data" comes form a
userform with several TextBox's and ComboBox's.one of
the
columns in "Data" can be left unfilled, as the others
are
required, the "Entry" userform will not run through the
sub to add the record if all the textbox/combobox's are
filled except for one, a text box to enter a completion
date. the lsit box displays the records in "Data" i
want the user to be able to DblClick the record in the
list box and have it bring up another userform
called "EnterCompletion" with one text box that the
user
can enter the completion date in, and have that go into
the appropriate cell in "Data" the completion column
is
8 columns to the right of A. i have been playing with
this:
If ListBox1.ListIndex < -1 Then
Rows(ListBox1.ListIndex + 2).Select
End If
and the only thing i cant get is how to say, "column I
in
active row, value = EnterCompletion.TextBox1.value.
hanks for any help...spence


.

worksheets("SheetName").Cells(listbox1.listindex+2 ,1) _
.Resize(1,9).Delete Shift:=xlShiftUp

If this is part of the rowsource of the listbox, then you probably need to
capture the listindex, set the rowsource to "", delete the row, reset the
rowsource.

--
Regards,
Tom Ogilvy



spence wrote in message
...
thanks, follow up question, how would i reference, a
range of cells in a particular row, the row corresponding
to the item selected in the list box, in another sub.
like if i wanted to delete the range("A3:I3"). i want to
hide this sheet and i cannot use the select method on a
hidden sheet.
-----Original Message-----
If ListBox1.ListIndex < -1 Then
Rows(ListBox1.ListIndex + 2).Select
Cells(ActiveCell.Row, "I").Value = _
EnterCompletion.TextBox1.value
Unload.EnterCompletion
End If

or skip the selecting and do

If ListBox1.ListIndex < -1 Then
Cells(ListBox1.ListIndex + 2, "I").Value = _
EnterCompletion.TextBox1.value
Unload.EnterCompletion
End If

This assume EnterCompletion is still loaded (though
probably hidden)


--
Regards,
Tom Ogilvy

spence wrote in
message
...
i have a multi column ListBox row source is a table
range
called "Data". the information in "Data" comes form a
userform with several TextBox's and ComboBox's.one of
the
columns in "Data" can be left unfilled, as the others
are
required, the "Entry" userform will not run through the
sub to add the record if all the textbox/combobox's are
filled except for one, a text box to enter a completion
date. the lsit box displays the records in "Data" i
want the user to be able to DblClick the record in the
list box and have it bring up another userform
called "EnterCompletion" with one text box that the
user
can enter the completion date in, and have that go into
the appropriate cell in "Data" the completion column
is
8 columns to the right of A. i have been playing with
this:
If ListBox1.ListIndex < -1 Then
Rows(ListBox1.ListIndex + 2).Select
End If
and the only thing i cant get is how to say, "column I
in
active row, value = EnterCompletion.TextBox1.value.
hanks for any help...spence


.