[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Excel VBA - Modifiable Listbox

Posted on 2003-03-03
8
Medium Priority
?
4,356 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:Phetu
6 Comments
 
LVL 5

Expert Comment

by:bullethead
ID: 8063277
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?
0
 
LVL 2

Author Comment

by:Phetu
ID: 8063345
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
0
 
LVL 5

Expert Comment

by:bullethead
ID: 8063518
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 5

Accepted Solution

by:
bullethead earned 800 total points
ID: 8063543
One further thought - instead of popping up a form, you could use the keypress event to update the source data.

Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Range("A2").Offset(ListBox1.ListIndex, 0).Value = Chr(KeyAscii)

End Sub

I still can't figure out a way to change anything except the first column though. :(
0
 
LVL 5

Expert Comment

by:TigerMan
ID: 8063603
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
0
 
LVL 1

Expert Comment

by:geofflilley
ID: 10009182
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
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you looking to start a business? Do you own and operate a small company? If so, here are some courses you need to take before you hire a full-time IT staff.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question