Edit cell contents via userform listbox

N

N E Body

Thanks Dave

I have not used listboxes before and I have struggled to find onlin
examples. Perhaps what I want cannot be done!

Now I am getting somewhere! I can edit because I know the cel
references but my user can only see a listbox on a userform - they hav
no idea of the location of the text. Is it possible to select the cel
via the active line of the listbox?
I would like it to work as follows
Listbox1 covers D1:D100 - each cell contains text
The user scrolls through the listbox and selects the text to edit
they click command button1 to put the cell value into textbox1
textbox1 is edited as required
they click commandbutton2 to update the cell

any ideas

Kenn
 
D

Dave Peterson

Maybe something like:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top