Using ListView on Excel VBA UserForm

[steve.t]

Hi all,

I have a simple Personnel worksheet. Unique identifier is EmployeeNumber. Columns contain Surname, First Name, Position, Phone, Date of Birth, Address, Emergency Contact, etc. Can have a few or a few hundred records.

I would like an Add/Edit UserForm with a ListView control of the type "Report", showing (eg) Employee Number,Surname & First Name (with Headers and a vertical scroll bar), showing about four or five records and capable of scrolling through all records. Picture this Userform:

Near the top of the form, the ListView control, showing the first four(or five) records. Clicking on a record highlights the row.

Above the listView Control, three TextBox controls in a row, one each for the fields in the ListView control. Typing in any of these will find the corresponding record and display and highlight it in the ListView control.

Below the ListView control, a collection of TextBox controls of selected fields (eg Position, Phone, Address, etc). These must be editable.

Some CommandButton controls to add a new employee, save edited data, exit, etc.

Can anyone help with code or sample file. I know as much about ListView controls as I do about rocket science (I'm an HR person), so assume no prior knowledge, ie make it simple to follow please.

Cheers and thanks,
Steve T:bug:

[lucas]

see attached example file for you to work with. It doesn't do everything you want but if you want to learn some vba it's a start...

[steve.t]

Thanks lucas, that's sooooooo cooooool!!

Nearly there. I really, really, really would like to learn about the ListView control.

So if we could just replace the ListBox with a ListView...

Anyone??

Cheers,
:beerchug:
Steve

[lucas]

Thanks lucas, that's sooooooo cooooool!!

Nearly there. I really, really, really would like to learn about the ListView control.

So if we could just replace the ListBox with a ListView...

Anyone??

Cheers,
:beerchug:
Steve
Can I ask why and what you think the advantage would be?

[Norie]

Steve T

A listview control might be pretty but you do realise that it's not a standard control?

That means that there is every chance that if other users need to use what you are creating then they might not have access to it.

[steve.t]

Hi lucas, Norie,

Issue solved with ".ColumnCount = x" and ".ColumnHeads = True" in the ListBox. The only thing I don't know is how to put a border between the columns in the ListBox. There is a border between columns in the Header row, but not in the data part of the ListBox. Is it possible to put in borders?

I like ListView because it is pretty (looks nicer than the ListBox, from what I've seen), but I take Norie's very valid point about the availability of the ListView to users. I will stay with the ListBox.

Thank you for your help. It is great to get into a forum where people are prompt with good advice and help. It doesn't happen often.

Cheers,
Steve

[steve.t]

Back to the experts,

Having thought about this again, all the machines/users have the same version of the software as mine, so if I have the ListView control, then other users have also.

I do prefer the look of the ListView over ListBox, so that's what I will pursue.

But to do things with a ListView control that Steve Lucas's file does with the ListBox and the Labels requires (probably slightly) different syntax for a new "ListView1_Change()" routine.

Can anyone help?

Cheers,
Steve T

[metev]

hi steve,

I?m wondering how are you able to use such a control in VBA.As far as I know VB uses such controls as ListView , but VBA not.Im just trying to add a vertical scrollbars to a ListView and it?s not so simple.With a ListBox both Scrolls appear automaticaly.If you somehow added a vertical scrollbar pls feedback!

Greetings!
Valeri

[steve.t]

Valeri,

See KB article "Coloring listviewitems based on a condition" by Charlize. It tells you how to insert a Listview control as well as how to format textcolor in the listview. See the secton "How to use" to add a Listview control to your toolbox, and there is a sample file file as well.

Keep an eye on this thread. Is I learn things I will keep posting.

Cheers,
Steve

[inddon]

see attached example file for you to work with. It doesn't do everything you want but if you want to learn some vba it's a start...

Hello Lucas,

Your soulution still there (been 7 years :-) ). Could you please attach/send the file for reference.

Thanks & regards
Don

[Jan Karel Pieterse]

Note also, that if ever your company (or any user) switches to 64 bit Office, the listview will not work...