Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Vb help

Posted on 2011-02-27
9
Medium Priority
?
276 Views
Last Modified: 2012-05-11
Please can you help me alter the code on my workbook.

Basically I would like for the listbox to display the up/down arrows when the userform is opened. So was Textbox1 to equal blank, then the arrows would display along with the listed range.

The picture describes what I would like to achieve but currently I would have to input "A" to achieve this.

Also something weird happens if I type "A" into textbox1 and then press the down arrow on my keyboard and then down again, for some reason it jumps to "Akrotiri" as opposed to descending down the range i.e. "Albania"

Many thanks for your help!

Gary



 help gary-test4.xls
0
Comment
Question by:Gazza83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 34993265
Hi, see how this goes.  I've made the full list populate when the text box is empty.

The reason the list box was changing is because when an item is selected, it also updates the text box for the selected value, resulting in it being cleared.

I have prevented that, but I don't know how it's going to go with identifying the selection now.

Regards,

Rob.
gary-test4.xls
0
 
LVL 6

Expert Comment

by:TinTombStone
ID: 34993310
Should now start with the listbox loaded
Copy-of-gary-test4.xls
0
 

Author Comment

by:Gazza83
ID: 34993370
Thanks Rob, works well only now the mouse click selection function is disabled.

I was wondering if on selection, the listbox could remain on the value that was last selected.

Something like the attached? help
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Gazza83
ID: 34993384
Thanks Tom, I've tried the attached and it works on start up. The only thing is if I hit the down arrow on my keyboard twice, it jumps to Akrotiri as oppposed to moving through the 'drop down'
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34993526
OK, test this out.  It could be right.  I have removed the TextBox1_Change event, copying the code to a FillListBox sub.  This way, whenever the text box changes due to the list box being selected, it doesn't re-populate the list box.  As for the keystrokes, I've called FillListBox on the KeyUp event of the TextBox, instead of the Change event.

Regards,

Rob.
gary-test4.xls
0
 

Author Comment

by:Gazza83
ID: 34993674
Hi Rob, the code on the file you uploaded works brilliantly, just how I want it. The one problem is that Textbox4 doesn't seem to work:-

the old code had something like:-

End If
    Set rng1 = Sheets("DataSheet").Range("D1:D300").Find(TextBox1.Value, , xlValues, xlWhole)
    If Not rng1 Is Nothing Then TextBox4.Value = rng1.Offset(0, 1)
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 34993751
Ah, change this:
Private Sub ListBox1_Change()
    On Error Resume Next
    If ListBox1.ListIndex > -1 Then
        TextBox1.Text = ListBox1.Value
    End If
    On Error GoTo 0
End Sub


to this
Private Sub ListBox1_Change()
    On Error Resume Next
    If ListBox1.ListIndex > -1 Then
        TextBox1.Text = ListBox1.Value
        CheckIfComplete
    End If
    On Error GoTo 0
End Sub


Regards,

Rob.
0
 

Author Closing Comment

by:Gazza83
ID: 34993819
Many thanks for your help!
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34993827
No problem. Thanks for the grade.

Regards,

Rob.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question