Solved: Worksheet ListBox Selection

[simora]

Hi:

I am trying to modify this attached sheet so that when I select a ShrinkName from the list, it also enters the matching Account Number which is in Column B on the List sheet (Sheet2), and also the Salesperson Name from Column C

How can I do this VBA code to accomplish this?

Thanks

[GTO]

Greetings Simora,

Maybe:

In the Userform's Module:

Private Sub cmdOK_Click()

If lbxList.ListIndex < 0 Then
'***ADD
lbxList.ListIndex = 0
'***END ADD
Value = lbxList.List(0)
Else
Value = lbxList.Value
End If
Me.Hide
End Sub

In the Worksheet Module:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim TargetCell As Range

Set TargetCell = Intersect(Selection, [B2:B110])

If Not TargetCell Is Nothing Then
If TargetCell.Cells.Count = 1 Then
frmListPicker.Initialize TargetCell.Cells(1, 1), [List]
frmListPicker.Show
If Not Cancelled Then
TargetCell.Cells(1, 1) = Value
'***ADD
TargetCell.Cells(1, 0).Value = _
ThisWorkbook.Worksheets("List").Cells(frmListPicker.lbxList.ListIndex + 1, "B").Value
TargetCell.Cells(1, 3).Value = _
ThisWorkbook.Worksheets("List").Cells(frmListPicker.lbxList.ListIndex + 1, "C").Value
'***END ADD
End If
Unload frmListPicker
End If
End If
End Sub

Please try in a junk copy of your wb.

Hope that helps,

Mark

[simora]

Thanks GTO:

I can get it to work using this MOD


Else
Value = lbxList.Value
rsRow = 1 + lbxList.ListIndex
ActiveCell.Offset(0, 2) = Worksheets("List").Cells(rsRow, 2).Value
End If
Me.Hide
rsRow = 0

End Sub


It gives me the correct results if I only select from the ListBox, however, If I start to type ( In field txtValue )and then select a name from the Listbox, I'll get an incorrect value.

Is there an easy way to preserve the ListBox order to get the correct listIndex even if txtValue.Value is Not nothing?

[mdmackillop]

I got an error with Mark's code. Try this which has a minor change

[GTO]

Thanks GTO:

I can get it to work using this MOD
...
It gives me the correct results if I only select from the ListBox, however, If I start to type ( In field txtValue )and then select a name from the Listbox, I'll get an incorrect value.

Is there an easy way to preserve the ListBox order to get the correct listIndex even if txtValue.Value is Not nothing?

Okay, a little muddy-headed on my part. I glossed right past the effect that using the textbox will have on lessening the entries in the listbox, vs. the list of entries from the sheet.

Try the attached. Please note optional changes before incorporating into your code:

I moved the Public variables out of the object module to the standard module.
I rem'd out (commented out) both the Value=Value and Malcom's suggestion. I am rather not to par this weekend, but as I can tell, this would be the current value of the cell selected. It appeared to me that its inclusion is to give an initial value to the textbox, and thus effect the initial entries in the listbox.Reference the Public variables, this means that anyplace that there was a frmListPicker.Value, it is just 'Value' now. Malcom and others could provide a more articulate explanation, but in short, its easier to 'see' into a a standard module than into a class or object module. Actually, while I did not test, AFAIK, if the userform was not loaded when we ran into one of the public variables, I believe it would foul, as the object, and thus the class doesn't really "exist" (is not instantiated?).

I would add that naming variables like "Value", or the same as other properties etc, seems confusing to me. I believe I am in the majority in this opinion, as I've never seen anyone tout this as a good idea.

See how this works. I think it would be a lot easier to search the original list (on the sheet) for the value/entry selected on the listbox, given the dynamic nature of the listbox entries.

Hope this helps,

Mark

[simora]

mdmackillop:

Your code had the same problem that my MODs had. When the user typed directly into the textBox, ( In field txtValue ) and then selected a name from the Listbox, we'll get an incorrect value.
I also did get an error with Mark's original code. ( Now since modified and working )



GTO:

Your modified code does the trick. Thanks.

RE: I would add that naming variables like "Value", or the same as other properties etc, seems confusing to me.

I agree 100% with you on this. I had adopted and modified the code myself. ( Sorry about that )

RE: I think it would be a lot easier to search the original list (on the sheet) for the value/entry selected on the listbox, given the dynamic nature of the listbox entries.

I was thinking that a search might get messy with a whole lot of other issues, but it was an " if all else fail kinda thing" . Also, I wanted to give the user an opp to start typing the name they were hoping to find / select.
You do have a valid point anyway.

Thanks ALL for the suggestions.

[GTO]

...I also did get an error with Mark's original code. ( Now since modified and working )
...
RE: I think it would be a lot easier to search the original list (on the sheet) for the value/entry selected on the listbox, given the dynamic nature of the listbox entries.

I was thinking that a search might get messy with a whole lot of other issues, but it was an " if all else fail kinda thing" . Also, I wanted to give the user an opp to start typing the name they were hoping to find / select. ..

I realize its moot, but was the error because I forgot (my bad) to mention already having moved the Public variables last time? I am asking as I was experiencing some little glitch, in that if VBIDE was open, then running at full speed resulted in the "project will reset, OK?" warning. If I stepped through, I would not get the warning, nor if VBIDE was closed. I figured goblins and had given up.

Not worth too much effort, just in case a fast check might reveal what I was missing.

As to the last bit, maybe we are misunderstanding each other, but the user should be able to type in the textbox just as before. Is that not working on your end???

[simora]

GTO:

RE: the user should be able to type in the textbox just as before. Is that not working on your end???

Yes. Its working!

Your code MODs are working as intended. I've tested it and so far, NO ERRORS! Thanks.

The original code gave the error when I ran it, but I had already worked out a way to get the Account info etc...etc... so long as the user did not type into the TextBox. I did not quite replicate the error in your code while trying to step through it. Strange.

You later code SOLVED that issue. I like the approach you used to take the changing ListBox into consideration etc.
Its all working.

Thanks.