Solved: ComboBox or ListBox Value

[John_Mc]

Hi all,

I have a form people use to recal data from Excel, examine the current values, overtype the existing data and then resave it back to the Excel sheet.

I want a listbox or combobox that will only let people select, for example "occupied" or "vacant" or "on hold".

The tricky part, is that I want the listbox to display the value that is currently held on the sheet (e.g "squatters"), but once a user clicks in the box, is then only alllowed to choose from the list i've set.

I'm comfortable with creating the list from a range etc. but have no idea how i can get the listbox or combobox to show the cell value to start with.

Any help you can give would be great, as this in the only thing i'm stuck on before i can finsih this project.

Many thanks,

John

[jindon]

Hi
cell value(s)
if your data structured vertical like A1:A100:

Me.ListBox1.List=Sheets("YourSheetName").Range("YourRange").Value

if your data structured horizontally like A1:Z1:

Me.ListBox1.Column=Sheets("YourSheetName").Range("YourRange").Value

you can also just create like:

Me.ListBox1.List=Array("occupied","vacant","on hold")

it is really depends on your data structure and how you want to polulate.

[John_Mc]

Hi Jindon,

thanks for taking the time to help me out, i appreciate it. I'm ok with creating the listbox working (I am currently using the RowSource property), but i'm stuck with getting a current value in the listbox.

In theory it would work like this...

They open the form, select the site name and number and then click get data.

THe form then shows the user what is currently held (e.q. squatters -which will always be in the same column, but could be anywhere between rows 2 and 1000 on sheet "CurrentData")

The user then clicks in the listbox and only gets the options now available (as set through the rowsource property looking at sheet "Variables").

The bit i'm stuck on is getting the current value in the listbox.

If you could offer any more advice I'd be grateful,

Many thanks,

John

[jindon]

OK

If you give us just a small sample data, it will help us to uderstand what you are exactly trying to do.

[John_Mc]

Hi Jindon,

please find an example attached.

On opening, a form appears. If you select unit2 from the top listbox and then click "Get Data", you will notice that the persons name appears in the name box on the form.

Excel has looked up unit 2 and brought back the details into the form (the name box is a text box).

However, i also want the two list boxes (status and tenure) to show me what is relvant for unit 2 ('on mars' and 'squatters'). At the moment, they just show me the options that are available to choose - not what is already there.

Ideally, you will click get data, and the listbox will show me that for unit 2, the status is "on mars" and the tenure is "squatters". however, as that is no longer an acceptable field, the user can click in the field and be given the normal choices to make (just occupied, vacant, on hold).

I hope i've explained myself clearly enough and let me say
thanks for your continued patience/help.

Cheers,

John

[jindon]

Hi

you have different array for lbTenure which is "STR", "LTR", "PERM"
but in the range you have different values???

what do you want to do with it?

[jindon]

Few changes made


Private Sub CommandButton1_Click()
Dim MyName As String
Dim ChosenUnitNumber As String
Dim r As Range
ChosenUnitNumber = lbUnitNumber.Value
With Sheets("sheet1")
With .Range("a5", .Range("a" & Rows.Count).End(xlUp))
Set r = .Find(ChosenUnitNumber, , , xlWhole)
Me.txtName.Text = r.Offset(, 4).Value
With Application
Me.lbStatus.ListIndex = _
.Match(r.Offset(, 1).Value, .Index(Me.lbStatus.List, 0, 1), 0) - 1
Me.lbTenure.ListIndex = _
.Match(r.Offset(, 2).Value, .Index(Me.lbTenure.List, 0, 1), 0) - 1
End With
End With
End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_activate()
With lbUnitNumber
.RowSource = "a5:a8"
End With
lbStatus.List = Array("Occupied", "Vacant", "On Mars")
lbTenure.List = Array("STR", "Squatting", "PERM", "N/A")

End Sub

[John_Mc]

Hi Jindon,

I don't understand all of your code, but i'll have fun taking it apart and learning from it.

It works perfectly and I can now amend it to fit the actual version.

I've posted this on another forum as well, but unfortunately nobody there could help me, so I thought i'd try here. So,

Thank you very much for spending the time to write the code for me - probably doesn't seem it, but all the work i've done so far would have been ruined if i couldn't get this bit sorted out. :thumb


Cheers,

John

[lucas]

Hi John,
Glad you got some help here. A note about cross posting which is ok but please let us know that you are...please read this. (http://www.excelguru.ca/XLKBA/XLKBA05.htm)

[jindon]

John,

I should have mentioned this.

I recomend you to make dropdownlist(data validation) for each columns,
because if the items in the column is not exactly the same as one in the list, it will give an error...

[John_Mc]

Hi Lucas,

After reading your link I understand the issue and have taken the point, (especially about the volunteer nature of the time) so i'm sorry for any inconvenince this might have caused, it certainly wasn't deliberate.

I'll make sure that similiar things are notified in advance for everyones benefit and continued 'netiquette'.

Cheers,

John

[John_Mc]

Hi All,

the original post was in this forum

http://www.ozgrid.com/forum/showthread.php?p=252731#post252731

Thanks
John

[lucas]

Hi John,
Thanks for your reply, and thanks for taking a minute to follow the link. Responders will appreciate your understanding.

[John_Mc]

Hi Jindon,

sorry, i'm back.... :yes

I've taken your advice about the data validation, thanks for posting the additional thought.

I've put your code into my s'sheet and it all works great (and i've used you code to change how i've done the rest, shortening it considerably).

When the listbox is activated, the user can click the arrow to scroll through the list. Is there anyway of excel autoselecting whatever is highlighted?

At the moment, the user scrolls through and then has to actually click his/her selection (for it to get the blue background and become active).

As i have quite a few list boxes on the form, this could get a little tiresome.

After this, everything should be finished (touch wood).

Thanks again for all your help,

John

[geekgirlau]

To open the drop-down, press [Alt-Down Arrow]. You can use [Home] and [End] to get to the top or bottom of the list, and [Page Up] and [Page Down] or the arrow keys to move through the items, but unfortunately data validation does not auto select items in the list.

[John_Mc]

Hi GeekGirlAu,

Project is all done now (apart from adding a little more error handling), so thanks for your advice.

I've notice some threads are marked as solved in the subject line, but others aren't. How do i mark it solved? For now i've just typed it in the subject line.

Enjoy tomorrows public holiday,

John

[lucas]

Hi John, will mark it solved for you. Thanks for letting us know. Forum is in the middle of an upgrade now so that link under thread tools is not working yet. Have a nice weekend.