display and change listbox data
 

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

display and change listbox data
 

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" wrote in message
...

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

display and change listbox data
 

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

display and change listbox data
 

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.

display and change listbox data
 

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


--
Oreg
------------------------------------------------------------------------
Oreg's Profile: http://www.excelforum.com/member.php...fo&userid=9195
View this thread: http://www.excelforum.com/showthread...hreadid=474539

display and change listbox data
 

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

display and change listbox data
 

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" wrote in message
...

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

display and change listbox data
 

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


--
Oreg
------------------------------------------------------------------------
Oreg's Profile: http://www.excelforum.com/member.php...fo&userid=9195
View this thread: http://www.excelforum.com/showthread...hreadid=474539