Edit cell contents via userform listbox
 

Thanks Dave

I have not used listboxes before and I have struggled to find onlin
examples. Perhaps what I want cannot be done!

Now I am getting somewhere! I can edit because I know the cel
references but my user can only see a listbox on a userform - they hav
no idea of the location of the text. Is it possible to select the cel
via the active line of the listbox?
I would like it to work as follows
Listbox1 covers D1:D100 - each cell contains text
The user scrolls through the listbox and selects the text to edit
they click command button1 to put the cell value into textbox1
textbox1 is edited as required
they click commandbutton2 to update the cell

any ideas

Kenn

--
N E Bod
-----------------------------------------------------------------------
N E Body's Profile: http://www.excelforum.com/member.php...fo&userid=1126
View this thread: http://www.excelforum.com/showthread.php?threadid=27006

Edit cell contents via userform listbox
 

Maybe something like:

Option Explicit
Dim myCell As Range
Dim myRng As Range
Private Sub CommandButton1_Click()
With Me.ListBox1
If .ListIndex 0 Then
Me.TextBox1.Value = .Value
Set myCell = myRng(.ListIndex + 1)
.ListIndex = -1
End If
End With
End Sub
Private Sub CommandButton2_Click()
If myCell Is Nothing Then
'do nothing
Else
myCell.Value = Me.TextBox1.Value
Me.TextBox1.Value = ""
Me.ListBox1.List = myRng.Value
End If
End Sub
Private Sub UserForm_Initialize()
Set myRng = Worksheets("sheet1").Range("d1:d100")
Me.ListBox1.List = myRng.Value
End Sub






N E Body wrote:

Thanks Dave

I have not used listboxes before and I have struggled to find online
examples. Perhaps what I want cannot be done!

Now I am getting somewhere! I can edit because I know the cell
references but my user can only see a listbox on a userform - they have
no idea of the location of the text. Is it possible to select the cell
via the active line of the listbox?
I would like it to work as follows
Listbox1 covers D1:D100 - each cell contains text
The user scrolls through the listbox and selects the text to edit
they click command button1 to put the cell value into textbox1
textbox1 is edited as required
they click commandbutton2 to update the cell

any ideas

Kenny

--
N E Body
------------------------------------------------------------------------
N E Body's Profile: http://www.excelforum.com/member.php...o&userid=11263
View this thread: http://www.excelforum.com/showthread...hreadid=270067

--

Dave Peterson