Excel 2010 Listbox - No Default Selection 1

[PWD]

Good morning, when my users open this worksheet it prompts them to select their name from a list in a Listbox in a Form. Once they have selected their name it goes off and populates the same cell in all the worksheets. Is there any way to have none of the names selected, or some other way around this, because if the person's name is already selected in the list they can't click it and carry on? For now I have added an extra row in the names range that I'm using as the RowSource - "Select Name" - but this seems a little clumsy, to me.

Many thanks,
D€$

 

[combo]

You can set ListIndex of the listbox to -1 to deselect all items. You can use an extra button to add user to the data range and listbox or to add temporary user or proceed without user, whatever you need.

combo

 

[PWD]

Thanks, but there doesn't appear to be a ListIndex property. Grr.

Many thanks,
D€$

 

[combo]

You need to set it by code.

combo

 

[SkipVought]

hi,

"it goes off and populates the same cell in all the worksheets"

Two things come to mind with that statement.

1) Why all these sheets, each with a name in the same cell. This might be legitimate OR it might be a non-normalized data structure where each sheet has the same data structure but contains data for the sheet name value -- BAD STRUCtURE!

2) So if its a legitimate structure (or not) why not reference the cell on the sheet that the name is entered on?

 

[PWD]

Hi Skip, yes on reflection I had all the name cells on the workbooks pointing to the name cell on the "Master/Front" worksheet. Must have just sleep-walked into this bit - this is a workbook a colleague has obtained and asked me to modify. However I'd still need to populate the master cell from the listbox.

@combo, how would I do this by code? Once set, would that property be visible?

Many thanks,
D€$

 

[combo]

It's visible, but not in the Properties window. You can find it in the Object browser or in intellisense list, after writing object's name and dot.
To set it:

Private Sub UserForm_Initialize()
Me.ListBox1.ListIndex = -1
End Sub

If you have event procedures associated with the listbox, you need a tag to inform that this is a startup change.

combo

 

[PWD]

I tried and ran this:

Private Sub NameForm_Initialize()
NameForm.NameListBox.ListIndex = -1
End Sub

But it just opens up the Name Form

This the code that I got from somewhere to use the name that has been selected, and I didn't (don't) realise the significance of the test for the ListIndex.

    If NameListBox.ListIndex <> -1 Then
        For I = 0 To NameListBox.ColumnCount - 1
            NameSelected = NameListBox.Column(I) '' & vbCrLf
        Next I
    End If

Many thanks,
D€$

 

[PWD]

Oh, I suppose this is testing that a name has been selected from the list?

Many thanks,
D€$

 

[combo]

Private Sub UserForm_Initialize(). It's an event procedure, should be in the same useform where your listbox is and has to have this name. In your case:

Private Sub UserForm_Initialize()
    NameListBox.ListIndex = -1
End Sub

ListIndex indicates which item is selected, it's 0-based, -1 for no selection, r/w property.

combo

 

[PWD]

Yes, just this second got that! What do I do if I've got similar forms, one for the initial opening of the book and one for changing the name once in the book? Presumably I'll need two event procedures with different names?

Many thanks,
D€$

 

[PWD]

Stupid Boy!! Of course I will - they're different forms!!!!

Many thanks,
D€$