Edit cell contents via userform listbox

[N E Body]

Hi

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

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

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

Any suggestions and advice would be much appreciated!

Kenny
Win 2000 - Excel 9

 

[Dave Peterson]

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