asked on

Excel VBA - Modifiable Listbox

Hi,

I have a listbox with 7 colomn and 1 heading row referenced by RowSource.

When it is display to the user, i want him to be able to modify any of the date within the listbox except for the heading.

How could i do that?

Thanks

Phetu

bullethead

I don't think you can edit data in a listbox directly. What about if you used the Click or Double Click event to edit the source data itself, something like:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

newval = InputBox("Enter new value?", "New value")
Range("A2").Offset(ListBox1.ListIndex, 0).Value = newval

End Sub

would work for the first column. Not sure how to get it to work on the other columns though?

Phetu

ASKER

HI bullethead,

Thanks for your comment.

I'm already using the DblClick event to popup a form to give the user the abality of changing the data but I found that's a long way to change simple data.

That's why i was looking for something more simple to change it.

Do you know about ActiveX Listbox, I think that there's one which does that, but i can't find it.

Phetu

bullethead

Sorry Phetu, I'm not aware of that control. There are some pretty good VB/VBA sites out there though - have you tried Googling for it?

As an alternative, the best I can think of is either:

a) 7 comboboxes (with headings, and editable, but will only display the heading and full list when the drop-down is clicked).

b) A combination of labels and multi-line textboxes. You could use labels to show the heading, and a multiline textbox for each column populated by something like:

For Each c In Range("a2:a7")
TextBox1.Text = TextBox1.Text & c.Value & vbCrLf
Next

If you wanted changes to feed back to the original data you could use something like:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

X = Split(TextBox1.Text, vbCrLf)

For i = 0 To UBound(X)
Cells(2 + i, 1) = X(i)
Next

End Sub

I know that's not a very satisfactory solution - I'll see if I can find anything about the control you mentioned.

ASKER CERTIFIED SOLUTION

bullethead

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TigerMan

Maybe you could look at:

Show the standard ToolBox
Click the "New Page" tab
RightClick - Additional Controls...
Add the Microsoft DataGrid control

This little ripper permits AddNew, Delete, and a bunch of other stuff - all programmatically controlled.

Dave

geofflilley

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
deleted and points refunded
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Geoff Lilley
EE Cleanup Volunteer