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