display and change listbox data

[Oreg]

Hello,

In short, I have a listbox as part of a form called "SNEM", which displays data located on Sheets("SP"). I would
like to be able to double click a row of data within that listbox, and have a separate form appear
displaying the data I selected allowing me the ability to make and save changes.

Currently I have a seperate form, called WMS, that finds the next blank cell in column B of Sheets("SP") and
enters data in cells B thru M.

I can create code to call a form when the listbox is selected, but I don't know what code to used that would recognize
the line selected and display that row (B - M) inside my new form. Is this possible ?

Thank you in advance.

[Nigel]

the listindex property of the form returns the item number in the list, a
value from 0 to n (where n = number of items in your list less 1), a value
of -1 indicates that no item is selected.

If you use the list index then a select case statement could be used to open
a separate form, something like with a value being set to indicate the row
to open in the relevant form which uses the value in xRow to load data.....


select case SNEM.ListIndex
case is = 0
xRow = "B"
userform0.show
case is = 1
xRow = "C"
userform1.show
end select

--
Cheers
Nigel



"Oreg" <Oreg.1wm6yb_1128827107.9298@excelforum-nospam.com> wrote in message
news:Oreg.1wm6yb_1128827107.9298@excelforum-nospam.com...
>
> Hello,
>
> In short, I have a listbox as part of a form called "SNEM", which
> displays data located on Sheets("SP"). I would
> like to be able to double click a row of data within that listbox, and
> have a separate form appear
> displaying the data I selected allowing me the ability to make and save
> changes.
>
> Currently I have a seperate form, called WMS, that finds the next blank
> cell in column B of Sheets("SP") and
> enters data in cells B thru M.
>
> I can create code to call a form when the listbox is selected, but I
> don't know what code to used that would recognize
> the line selected and display that row (B - M) inside my new form. Is
> this possible ?
>
> Thank you in advance.
>
>
> --
> Oreg
> ------------------------------------------------------------------------
> Oreg's Profile:
http://www.excelforum.com/member.php...fo&userid=9195
> View this thread: http://www.excelforum.com/showthread...hreadid=474539
>

[Oreg]

Hi Nigel,

Thanks for the help. I've been trying to work with your suggestion with no luck unfortunately. I'm not sure how to accomplish this task. Just looking at the code you've suggested, it seems as though a new form will be created for each index. Am I reading that correctly?
What I would like to be able to do is have one form where textbox1.value would list the data in B2, Textbox2.value would list the data in C2 ....and so on....of sheets("SP"), in relation to the row selected on the listbox of my second form.

Thanks !!!

[Rowan Drummond]

Maybe this could work for you. I have named the new form to be opened
frmEntry. This form has 13 textboxes, one for each column, named txtB
(for the data from column B), txtC (for data from column C), txtD and so
on to txtM.

On the original form I have assumed the data populating the listbox
(listbox1) in my example starts in row1. I have then used the lisindex
on the selected value to get the row of data to populate into the
textboxes. Note I have added 1 to this listindex as it is 0 based.
Change this if the data populating the listindex starts in a row other
than row 1.

The listbox_click event then loops through the controls on my new form
and populates each txtbox with the relevant data. Seems to work for me:

Private Sub ListBox1_Click()
Dim i As Long
Dim col As String
Dim txt As Control
i = Me.ListBox1.ListIndex + 1

For Each txt In frmEntry.Controls
If Left(txt.Name, 3) = "txt" Then
col = Right(txt.Name, 1)
txt.Value = Sheets("SP").Cells(i, col).Value
End If
Next txt
frmEntry.Show

End Sub

Hope this helps
Rowan

Oreg wrote:
> Hello,
>
> In short, I have a listbox as part of a form called "SNEM", which
> displays data located on Sheets("SP"). I would
> like to be able to double click a row of data within that listbox, and
> have a separate form appear
> displaying the data I selected allowing me the ability to make and save
> changes.
>
> Currently I have a seperate form, called WMS, that finds the next blank
> cell in column B of Sheets("SP") and
> enters data in cells B thru M.
>
> I can create code to call a form when the listbox is selected, but I
> don't know what code to used that would recognize
> the line selected and display that row (B - M) inside my new form. Is
> this possible ?
>
> Thank you in advance.
>
>

[Oreg]

Rowan,

That is fantastic !!!!!! Thanks so much for your help. One more questions if I could. Would I be able to make changes in the frmEntry form to write back to those particular cells in sheets("SP")?

Thanks again. your directions were easy to follow !!!!

Oreg

[Rowan Drummond]

Hi Oreg

You're welcome.

I added a label to to the frmEntry userform called lblRow and set it's
visible property to false so that it is hidden. I then added a button
called cmdSave. I then added one line of code to the event posted
earlier to set the caption of lblRow so that it now reads like this:

Private Sub ListBox1_Click()
Dim i As Long
Dim col As String
Dim txt As Control
i = Me.ListBox1.ListIndex + 1

For Each txt In frmEntry.Controls
If Left(txt.Name, 3) = "txt" Then
col = Right(txt.Name, 1)
txt.Value = Sheets("SP").Cells(i, col).Value
End If
Next txt
frmEntry.lblRow.Caption = i '<< Added line
frmEntry.Show
End Sub

The click event for the save button on frmEntry can reference this label
in order to update the correct row. There are other ways to do this
passing variables etc but this is simple and works:

Private Sub cmdSave_Click()
Dim i As Long
Dim txt As Control
Dim col As String
i = CInt(Me.lblRow.Caption)
For Each txt In Me.Controls
If Left(txt.Name, 3) = "txt" Then
col = Right(txt.Name, 1)
Sheets("SP").Cells(i, col).Value = txt.Value
End If
Next txt
Unload Me
End Sub

Regards
Rowan

Oreg wrote:
> Rowan,
>
> That is fantastic !!!!!! Thanks so much for your help. One more
> questions if I could. Would I be able to make changes in the
> frmEntry form to write back to those particular cells in sheets("SP")?
>
>
> Thanks again. your directions were easy to follow !!!!
>
> Oreg
>
>

[Nigel]

I have to admit I read your request as opening a separate new form for each
set of data. If you just need one form then Rowan suggestion is a good
choice.

--
Cheers
Nigel



"Oreg" <Oreg.1wntac_1128902704.9659@excelforum-nospam.com> wrote in message
news:Oreg.1wntac_1128902704.9659@excelforum-nospam.com...
>
> Hi Nigel,
>
> Thanks for the help. I've been trying to work with your suggestion
> with no luck unfortunately. I'm not sure how to accomplish this task.
> Just looking at the code you've suggested, it seems as though a new
> form will be created for each index. Am I reading that correctly?
> What I would like to be able to do is have one form where
> textbox1.value would list the data in B2, Textbox2.value would list the
> data in C2 ....and so on....of sheets("SP"), in relation to the row
> selected on the listbox of my second form.
>
> Thanks !!!
>
>
> --
> Oreg
> ------------------------------------------------------------------------
> Oreg's Profile:
http://www.excelforum.com/member.php...fo&userid=9195
> View this thread: http://www.excelforum.com/showthread...hreadid=474539
>

[Oreg]

Hi Rowan,

Once again.....thank you!!! That's exactly what I needed.

Hi Nigel,

Thanks for your response. If it weren't for people like you and Rowan, I would never find a solution.

A major thanks to both of you

Oreg