Change User's Listbox Selection (1 Viewer)

[NMeeker]

Hi, I have a ListBox on a form. The listBox is populated by a range, then an item "New Event" is added to the end of the list.

When "New Event" is selected, I would like an input box to be shown to the user. Then the InputBox's information "NewEvent" will be added to the top of the ListBox's list. I would like the new entry, "NewEvent", to be show to the user as the newly selected item in the ListBox.

Currently I am having trouble getting the new item to be shown as if it is selected. I have 3 lists on the same form, and the other 2 show a blue highlight over the item that has been selected by the user. I would like the same "blue highlight" to be shown on the "NewEvent" after it has been added to the list.
Any Help would be GREATLY appreciated.

This is what I have so far:

Private Sub SELECTEVENT_Click()
'______add new event to list
Dim NewEvent As String

If SELECTEVENT.Value = "New Event" Then
 
    SELECTEVENT.RemoveItem (SELECTEVENT.ListIndex)
    SELECTEVENT.AddItem "New Event"
    
   NewEvent = Application.InputBox("Please enter name of New Event.", _
                    "New Event", Left:=1, Top:=1)

    If Not NewEvent = "False" Then
        If NewEvent <> "" Then
            SELECTEVENT.AddItem NewEvent, 0
        End If
    End If
End If
End Sub

 

[DCrake]

When using AddItem and RemoveItem you can only perform these actions if you are using a value list and not a table/query.

Which method are you using to populate your listbox?

 

[NMeeker]

I am using AddItem....

I am pulling information from ranges on several different worksheets, then taking out duplicates, then sorting a-z then adding the "New Event" to the end of the list. AddItem worked better for my purposes. I could post my code for adding, removing duplicates, and sorting if it would help...

but I think my problem has something to do with selecting an item. I don't really know how to do it so that it will show as though it were selected. I honestly don't know much about the .Selected or .ListIndex I don't even know if those would be the right choices... I can get the new item to be added to the list, but i cant figure out how to make it appear selected to the user. Any ideas?

 

[ShredDude]

To get the "Blue Highlighted" row to appear you need to Select the row. A statement in your code such as this will Select the Row indicated. Of course you have to know which row you wish to select.

me.lboxMyList.selected(1)=true

If the item you just added is at the end of the list you might try

me.lboxMyList.selected(lboxMyList.listcount-1)=true

If it's not the end of the list, perhaps you could identify the required row by another means, such as the value of the bound column, or any other column for that matter with something like this:

r = 1
Do Until Me.lboxMyList.Column(2, r) = "XYZ"
    r = r + 1
Loop
me.lboxMyList.selected(r)=true

The above will loop through your list, from the top, until it finds a row whose third column is egual to XYZ and then Select that row. Modify the concept accordingly for your situation and efficiency considerations.

To synchronize the rows selected in multiple listboxes, consider putting some code in the BeforeUpdate event of each listbox that will select the appropriate corresponding row in the related list boxes. That way you can keep the Blue Bars aligned across the different lists

Hope that gets you going. Good luck with it.

Shred

 

[NMeeker]

Hey, Thanks for the attempt Shred, but I am still having troubles with it. I have tried messing with .Selected before, and though my hopes were up this time again, noting...

I think my problem is that I am using an InputBox to get the name of the item I will be adding to the box. I am just guessing here... But, I think the InputBox takes the form out of focus, and then .Selected doesn't work right. I'll post a bit of my code, and maybe you or someone else can help me out... I hope...

ListBox is filled with .AddItem

Private Sub ListBox1_Click()
     'ListBox1 add new event to list
     Dim NewEvent As String
     Application.ScreenUpdating = False
     
If ListBox1.Value = "New Event" Then

    'these two lines seem to be necessary to prevent the InputBox
    'from showing twice, and causing "NewEvent" to be added to the list twice.
    ListBox1.RemoveItem (ListBox1.ListIndex)
    ListBox1.AddItem "New Event"

   NewEvent = Application.InputBox("Please enter the name of your New Event.", _
               "New Event", Left:=1, Top:=1)

    If Not NewEvent = "False" Then
        If NewEvent <> "" Then
        ListBox1.AddItem NewEvent
        End If
    End If
    Me.ListBox1.Selected(ListBox1.ListCount - 1) = True
End If
     Application.ScreenUpdating = True
End Sub

 

[DCrake]

This is a crude way but it ill get you what you want

The value from the input box is passed the string variable NewEvent, which inturn is used in the listbox.AddItem code.

So after that has taken place, try this

For x = 0 to me.listbox.listcount-1

   If me.listbox.ItemData(x) = NewEvent Then
             Me.listbox.Selected(x) = true
   End If
Next

If effect it reads the value of each item in the list box an compares it to the string NewEvent, if it matches then select this item.

Alo have you thought aout what happens if the user types in an event that already exists in the listbox

David

 

[ShredDude]

Are you doing this in Excel? Or Access?

Application.Screenupdating = False

Screenupdating is a property of the Excel's Application Object, not Access'. To accomplish similar task in Access, try

application.echo false

The "Selected" method I referred to in my previous post is not a member of the Excel Object Model. It is a method within the Access Object model.

If you use DCrake's code in Access, consider putting an Exit For statement in there to jump out of the loop once it has found what you're looking for.

To accomplish your task in Excel, you simply assign the listbox's ListIndex property to the row you want, like this:

listbox1.listindex =4

That will give you your Blue Highlighted row on the fifth row of Listbox1, in Excel, as well as in Access. No need really to use the Selected(5)=True approach.



Good Luck.

 

[NMeeker]

Okay, Between the two of you, I am definitely getting closer to solving this mess. David, Thank You for all of your help. Though, your last bit of code did not solve my problem yet. However, I am getting closer. I am getting random successes now which are better than before. I do not know what is causing it to work, or what is causing it not to work. But, I am definitely narrowing it down.

I have noticed that because I am trying to call this from a Click event, every time I add a new item, or change the .Selected, it tries to do the .AddItem twice. This causes two copies of my added item.

So, I am currently trying to re-write almost all of what I have, so I may find a way to only call it one time. I am thinking about calling a sub, which would check for "New Event" to be selected. And maybe, have it set a Boolean if found. Any other suggestions?

 

[NMeeker]

hmmm I am definitely using excel, and was not expecting to post about the same time as you. I will definitely look into that though my repeating of added items still exists as is..

 

[ShredDude]

Your first lines of code:

If ListBox1.Value = "New Event" Then

    'these two lines seem to be necessary to prevent the InputBox
    'from showing twice, and causing "NewEvent" to be added to the list twice.
    ListBox1.RemoveItem (ListBox1.ListIndex)
    ListBox1.AddItem "New Event"

would seem to accomplish nothing. You're saying if the currently selected item in the Listbox (assuming it's one column) is "New Event", then delete it, and then add another item back in with the value "New Event". You're right back where you started.

Then your next bit of code, asks the user to type in some name, and whatever they type in the inputbox will be added to the Listbox. So, if they type in "New Event", you'll end up with another row containing "New Event", and then it would be selected if you were doing this in Access. But the line with .Selected should probably fail in Excel I'd expect.

For coding VBA for Excel, you'll probably find www.excelforum.com more useful than this site.

 

[NMeeker]

you are very right, those lines have ended up useless... I have come up with a solution to my problem... and though I did it by completely re-writing this at least 3 times... after the last three days of testing, writing, re-writing, and searching the web, the answer became very simple.

My trouble was, that the selected item would not "appear" as if it were selected... therefore, I refreshed the form.. I don't know if this is a normal trouble I was having, but I have seen a number of other posts with people having similar troubles. So here is the answer.... at least for excel...

        Call Me.Hide
        Call Me.Show

Thats right... simple.... place these two lines at the end of your sub and they refresh the form before showing the newly highlighted listbox selection...
Good luck to all this may help... Enjoy