ExcelBanter - Edit cell contents via userform listbox

You could use a refedit control on your userform and ask for the cell to update,
plop the value into a textbox and allow the user to edit it. Then have them
click a button to update that cell with the changes.

I used an application.inputbox to ask for the cell.

Option Explicit
Private Sub CommandButton1_Click()
Dim myCell As Range

Set myCell = Nothing
On Error Resume Next
Set myCell = Application.InputBox(prompt:="select a cell", Type:=8).Cells(1)
On Error GoTo 0

Set myCell = myCell.Parent.Cells(myCell.Row, "D")

With Me.TextBox1
.Value = myCell.Value
.Tag = myCell.Address(external:=True)
End With

End Sub
Private Sub CommandButton2_Click()

Dim myCell As Range

Set myCell = Nothing
On Error Resume Next
Set myCell = Range(Me.TextBox1.Tag)
On Error GoTo 0

If myCell Is Nothing Then
Beep
'something bad happened
Else
myCell.Value = Me.TextBox1.Value
Me.TextBox1.Value = ""
End If
End Sub

N E Body wrote:

Hi

I have a range of cells (D2:D100) Which the user needs to edit - The
user can only access these cells via a userform. The userform
currently has a listbox to allow the user to see the contents of the
cells.

How can I set it up to allow the user to select a cell - edit the text
- update the cell with the edited text?

I tried VBA help and tried AddItem etc but could not get it to edit the
cell.

Any suggestions and advice would be much appreciated!

Kenny
Win 2000 - Excel 97

--
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