?
Solved

Excel VBA - Modifiable Listbox

Posted on 2003-03-03
8
Medium Priority
?
4,326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 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