Changing items in Data Validation List via ListBox 1

[adamstuff]

I still can't understand why the simplest things are the hardest for me to find. I can find things like list inversion, sorting, Martian characters, ad nauseum, but not anything about how to change the list used to populate a listbox. I have gone through tutorials and tips and found nothing. My brain is so full of esoterica now that I don't know where to start.
I have a userform with Listbox with 15 items that is populated from a Data Validation list on a sheet. I don't have any code for the Listbox, just the listbox property "ControlSource" of the Sheet cells of the Validation List.
The purpose of the Listbox is to allow the users to change the items in the Data Validation List. My thought is that the ControlSource prevents me from changing anything. I use the Data Validation pull down list in 175 places on the spreadsheet.
I want the users to select an item in the Listbox, type over that value, and have that value replace the one in the Data Validation List. I don't want to add to the list, just replace that item.
I don't know if I should populate the Listbox by by using "Additem" in the subform Activate and then some sort of "Case" statement in the "Change" event of the Listbox. How do I identify which item in the Listbox has focus and how to put the changed item the same place into the Data Validation list.
TIA

Heisenberg was probably right.

 

[SkipVought]

Hi,

How about cathing the KeyPress and Mouse Down events. Presumably, you have a LIST for your Data>Validation - LIST feature. I NAMED it, AdamsList...

Dim sValue As String, nIndex, nPrevIndex


Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    nIndex = ListBox1.ListIndex
    If nIndex = nPrevIndex Then
        sValue = sValue + Chr(KeyAscii)
    Else
        sValue = Chr(KeyAscii)
    End If
    Range("AdamsList")(nIndex + 1) = sValue
    nPrevIndex = nIndex
    
End Sub

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim r As Range
    ListBox1.Clear
    For Each r In [AdamsList]
        ListBox1.AddItem r.Value
    Next
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[adamstuff]

Fantastic Skip.
I think I've got the gist of it. I'm not familiar with some keywords and their operation, though.

(ByVal KeyAscii As MSForms.ReturnInteger) Takes a keystroke and gives it back as a number?
ListBox1.ListIndex Gives back a numerical sequence of the number of items in the (userform)listbox, or maybe their values?
sValue didn't get dimensioned. I assume it's a string? Capturing keystorkes?
Based on the way my meager mind reads it, every time a key is pressed, it gets put into nIndex, nPrevIndex, sValue, sValue gets put into the spreadsheet validations list.

In the MouseDown, I have no understanding of "ByVal"
ListBox1.Clear deletes all items in the userform listbox? The listbox gets repopulated by the "ControlSource" property of the listbox?

I thought this would have been done via the ChangeEvent of the list box. Silly me! How does one ever get to know all the keywords and how they function, or better yet, where to look? I don't think the Macro Recorder would have helped in this case, would it?
Thanks again, Skip. You'll get a star on your reply to this one.
Especially on a holiday. Being a vet, the day means a lot to me.
======================================
I Am Proud To Be An American
I am a legal immigrant with US Citizenship
I pay taxes
I am a veteran
I speak English
I vote
I shoot back
=====================================


Heisenberg was probably right.

 

[SkipVought]

(ByVal KeyAscii As MSForms.ReturnInteger) Takes a keystroke and gives it back as a number?
[blue]
ListBox1_KeyPress is a Listbox event that captures KeyPress values, The value is the ASCII value of the key pressed.
[/blue]
ListBox1.ListIndex Gives back a numerical sequence of the number of items in the (userform)listbox, or maybe their values?
[blue]
The ListIndex is the position index of the values selected in the listbox.
[/blue]
sValue didn't get dimensioned. I assume it's a string?
[blue]
sValue IS defined at the module level -- check the code I posted.
[/blue]
Capturing keystorkes?
Based on the way my meager mind reads it, every time a key is pressed, it gets put into nIndex, nPrevIndex, sValue, sValue gets put into the spreadsheet validations list.
[blue]
Each time a key is pressed, it first checks to see if the same value is selected as the previous time a key was pressed. If its the same as the previous, the keyed value is concatenated, otherwise it is not. The keyed value is assigned to the item in the LIST correspondinf to the item seleted.
[/blue]
In the MouseDown, I have no understanding of "ByVal"
ListBox1.Clear deletes all items in the userform listbox? The listbox gets repopulated by the "ControlSource" property of the listbox?
[blue]
All this does is each time the mouse selects a diffrent listbox item, the listbox is cleared and repopulated with the CURRENT list, since the user can change the list at any time.
[/blue]
I thought this would have been done via the ChangeEvent of the list box. Silly me!
[blue]
These are BOTH listbox events.
[/blue]

[highlight red][white]
**Thank you for your service[/white][blue] to our country!**[/blue][/highlight]

Skip,
[sub]
[red][/red]
[/sub]

 

[adamstuff]

Skip, I really do appreciate your thoughtfulness in your reply. Hopefully you got the star. Can I give you another?

Guess I was blind here:
sValue didn't get dimensioned. I assume it's a string?
sValue IS defined at the module level -- check the code I posted.

Of course, now my mind is really confused. You coded
Dim sValue As String, nIndex, nPrevIndex
And what I thought I saw was something like
Dim sValue, nIndex, nPrevIndex As String
I saw what I was expecting and not what was there. If there is a difference, could you please explain? For some reason, I can't get my head around nIndex, nPrevIndex hanging out there by themselves.

One last request. Is there a place or book that YOU recommend for a guy as thick-headed as me? I can't believe the amount of time I have put into researching for so little return. I don't like to ask questions until I'm stumped and that has been happening all too often on my little project.

Thanks again for taking the time out of your holiday to help me. It is deeply appreciated.


Heisenberg was probably right.

 

[SkipVought]

Dim sValue, nIndex, nPrevIndex As String
I saw what I was expecting and not what was there. If there is a difference, could you please explain?
[blue]

Dim sValue, nIndex, nPrevIndex As String

means sValue is a variant by default, nIndex is a Variant by default and nPrevIndex is a String
[/blue]
For some reason, I can't get my head around nIndex, nPrevIndex hanging out there by themselves.
[blue]
As previously explained, they are Variants by default. I probably should have defined them as such

Dim sValue As String, nIndex As Integer, nPrevIndex As Integer

[/blue]


Skip,
[sub]
[red][/red]
[/sub]

 

[adamstuff]

That was a GREAT explanation! You've just corrected a major mistake I've been making!
Thanks again.
I keep clicking the the star link, but it doesn't seem to be doing anything today. Must be on holiday too!

Heisenberg was probably right.

 

[adamstuff]

Skip,
I must be the world's biggest dunderhead. I'm getting a "run time error (lotsa numbers): unspecified error" at the "ListBox1.Clear" line. My listbox name is lboTypes so I have "lboTypes.Clear"
I've tried everything I could think of relating to "Clear" and what I could be doing wrong, but I can't find it. I've commented out and got a "Run time error 70: permission denied" at the "lboTypes.AddItem r.Value" line. "Entry_List" is my listbox range name. I don't have any "CountA(A:A)" stuff in the range name, because I'm not sure what all that is or if it's needed.
This is my code:

Private Sub lboTypes_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim r As Range
        lboTypes.Clear
    For Each r In [Entry_List]
        lboTypes.AddItem r.Value
    Next
End Sub

Please help this poor guy. Thanks

Heisenberg was probably right.

 

[SkipVought]

    For Each r In [b]TheSheetObject[/b].[Entry_List]

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

I just noticed that your error was on the lboType.Clear statement. Sorry.

Assuming that your listbox is on the sheet, your listbox ALSO needs the Sheet Object refrenced.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

BTW, I'd add a line of code to reset the nPrevIndex each time the mousedown event occurs in the listbox...

Private Sub lboTypes_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim r As Range
    With Sheet1.lboTypes
        .Clear
        For Each r In Sheet1.[Entry_List]
            .AddItem r.Value
        Next[b]
        nPrevIndex = .ListIndex[/b]
    End With
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[adamstuff]

Hi Skip,
Congratulations on being Tipmaster of the Week! You deserve it just for putting up with me! I wish I could get back to you as fast as you get back to me, but my Excel project is extracurricular, even though it's for the job.
Unfortunately, I still have exactly the same problem as earlier with the Debug highlighting the .Clear statement and getting a "run time error (lotsa numbers): unspecified error".
My listbox is on the userform, not the sheet. Here's a copy/paste of the code.

 Private Sub lboTypes_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim r As Range
    With lboTypes
        .Clear
        For Each r In Sheet1.[Entry_List]
            .AddItem r.Value
        Next
        nPrevIndex = .ListIndex
    End With
End Sub

I did a Me.lboTypes to double check and the prompts came up with lboTypes, so I know that's OK. I swapped the lboTypes and the Sheet1.[Entry_List] to see what would happen and it cleared the Sheet1.[Entry_List]. I didn't get an error on the .Clear when I did that, so it's a clue.
It looks like the procedure just doesn't like lboTypes and I can't see why not. Is there something in the listbox proerties I may have gotten wrong?
Just curious... Does the For loop have to be inside the With statement? I guess the AddItem is why the loop is inside the With? I just don't understand what my Excel 2003 has against the .Clear. It's just not clear to me. (oooh, that hurt) I hate to keep bothering you. Thanks ever so much for the effort you've expended, even if I never solve this bug.
Thanks

Heisenberg was probably right.

 

[adamstuff]

Skip,
Just as a quick test, I stuck a .SetFocus in front of the .Clear.

With lboTypes
        .SetFocus
        .Clear
        For Each r In Sheet1.[Entry_List]

The procedure got past the .SetFocus and still got hung at the .Clear. Guess there's something weird there. FWIW.
HTH


Heisenberg was probably right.

 

[PHV]

What about this ?
lboTypes.RowSource = "Sheet1!Entry_List"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[adamstuff]

Hi PHV,
No matter where I put lboTypes.RowSource = "Sheet1!Entry_List" it doesn't change anything. I tried in the form Activate; in front of the With; Inside the With, before the .Clear. I get the same error, highlighting the .Clear. Everything seems to work, EXCEPT the .Clear.
Thanks

Heisenberg was probably right.

 

[PHV]

My suggestion was this:
Private Sub lboTypes_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
With lboTypes
.RowSource = "Sheet1!Entry_List"
nPrevIndex = .ListIndex
End With
End Sub

The help file clearly states that the Clear method fails if the control is dependant.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886