Edit cell contents via userform listbox

[N E Body]

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

 

[Dave Peterson]

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