Edit cell contents via userform listbox
 

Many many thanks Dave

I have just tried the code and it did EXACTLY what I wanted.
There is a small bug where if commandbutton2 is clicked twice th
details are erased from the cell!
Im sure I can fix it with a "If textbox1.value=empty" trap

Regards

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
 

Not a bug--a design choice <vbg.

What happens if they really wanted to clear out that cell? Shouldn't you allow
them to do that?

If no, how about this:

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:

Many many thanks Dave

I have just tried the code and it did EXACTLY what I wanted.
There is a small bug where if commandbutton2 is clicked twice the
details are erased from the cell!
Im sure I can fix it with a "If textbox1.value=empty" trap

Regards

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