PDA

View Full Version : Solved: combo bug in userform (it makes roller)



danovkos
01-03-2011, 02:09 AM
Hi all,
at first happy new year to all Excel fans :).

Now,
i have problem with combo in my userform.
I have textbox, where i write searched text and combo, which shows only matched values (to text box i write e.g. "dan" and my combo should show only values, which contain word "dan"). But i want to write next character "y" (it will be "dany") and i want to refresh automaticaly my combo which will contain only values "dany".

Now it works with strange way. Look on picture. It doesnt refresh dropped down combo, but it makes very small roller in combo. :(

Here is my code from textbox.

Private Sub TB_kriesSearch_Change()

CommandButton7.SetFocus

TB_kriesSearch.SetFocus

On Error Resume Next

Sheets("Krieš").TB_Kries_ZMENY = ZmenyNew.TB_kriesSearch.Value
Dim rRange As Range
Dim VisibleRange As Range

Set rRange = Worksheets("Krieš").Range("k2:k75")
Set VisibleRange = rRange.SpecialCells(xlCellTypeVisible)
ZmenyNew.COMBOkries.Clear

For Each rcell In VisibleRange
ZmenyNew.COMBOkries.AddItem rcell
Next

TB_kriesSearch.SetFocus
COMBOkries.DropDown
End Sub


Pls. can you help me with this?
i am affraid, if i am clear :(.
thx to all

Bob Phillips
01-03-2011, 02:46 AM
Post the workbook.

danovkos
01-03-2011, 02:52 AM
i really want but it is impossible for me.
Because my wb is 20 MB big and contains many data which can not be public :(
I hoped, it will be only maybe some settings of combo or some command ...:(
Maybe helps when i say, that content of my combo fills from sheet, which is filtered based textbox in this sheet "krieš". Then i load content of filtered data from sheet "krieš" to combo in my userform.

I know, this is maybe imposible to figured out, where the bug is :(.

Bob Phillips
01-03-2011, 02:56 AM
Can't you create an obfuscated, cut-down version that demonstrates the issue?

danovkos
01-03-2011, 02:57 AM
maybe will help me, if i will know, how to get combo to default (non droped down) status.
Because then i can do followig.
1. drop UP
2. load new content
3. drop down
what do you mean?

danovkos
01-03-2011, 03:11 AM
I tried it, but was not succesfull :(.

My situation:
- i have one wb, where i sticking a data from many sources
- in one sheet i have userform, which depends on many sheets and datas from this wb
- in this userform is many conditions, which can not simulate without data :(

i am frustrated, but my userform works great (many functionality in my UF is from this forum).
Now is my wb closer to application as to excel sheet :).

But i am very thankfull, that you try to help me. Really.

mikerickson
01-03-2011, 07:24 AM
Drop UP is accomplished by moving the focus between controls. Which you have in these two lines.
CommandButton7.SetFocus
TB_kriesSearch.SetFocus

Perhaps
Private Sub TB_kriesSearch_Change()

CommandButton7.SetFocus

TB_kriesSearch.SetFocus

On Error Resume Next

Sheets("Krieš").TB_Kries_ZMENY = ZmenyNew.TB_kriesSearch.Value
Dim rRange As Range
Dim VisibleRange As Range

Set rRange = Worksheets("Krieš").Range("k2:k75")
Set VisibleRange = rRange.SpecialCells(xlCellTypeVisible)
ZmenyNew.COMBOkries.Clear

For Each rcell In VisibleRange
ZmenyNew.COMBOkries.AddItem rcell
Next
CommandButton7.SetFocus
TB_kriesSearch.SetFocus
COMBOkries.DropDown
End Sub

danovkos
01-03-2011, 07:34 AM
Drop UP is accomplished by moving the focus between controls. Which you have in these two lines.
CommandButton7.SetFocus
TB_kriesSearch.SetFocus

Perhaps
Private Sub TB_kriesSearch_Change()

CommandButton7.SetFocus

TB_kriesSearch.SetFocus

On Error Resume Next

Sheets("Krieš").TB_Kries_ZMENY = ZmenyNew.TB_kriesSearch.Value
Dim rRange As Range
Dim VisibleRange As Range

Set rRange = Worksheets("Krieš").Range("k2:k75")
Set VisibleRange = rRange.SpecialCells(xlCellTypeVisible)
ZmenyNew.COMBOkries.Clear

For Each rcell In VisibleRange
ZmenyNew.COMBOkries.AddItem rcell
Next
CommandButton7.SetFocus
TB_kriesSearch.SetFocus
COMBOkries.DropDown
End Sub

I thought the same (the reason, why i wrote it), but it didnt work. When one combo is droped down, setfocus command didnt cancel it. It is dropped down till value is choosed (its only my opinion).

mikerickson
01-03-2011, 07:52 AM
Have you tried giving the combobox the temporary focus instead of the Command Button?

danovkos
01-03-2011, 08:03 AM
what do you mean with temporary focus on combo?

mikerickson
01-03-2011, 08:17 AM
Private Sub TB_kriesSearch_Change()

CommandButton7.SetFocus

TB_kriesSearch.SetFocus

On Error Resume Next

Sheets("Krieš").TB_Kries_ZMENY = ZmenyNew.TB_kriesSearch.Value
Dim rRange As Range
Dim VisibleRange As Range

Set rRange = Worksheets("Krieš").Range("k2:k75")
Set VisibleRange = rRange.SpecialCells(xlCellTypeVisible)
ZmenyNew.COMBOkries.Clear

For Each rcell In VisibleRange
ZmenyNew.COMBOkries.AddItem rcell
Next
COMBOkries.SetFocus
TB_kriesSearch.SetFocus
COMBOkries.DropDown
End Sub

danovkos
01-03-2011, 08:23 AM
:rotlaugh: :rotlaugh: :rotlaugh:
fantastic mikerickson

thank you very very much

it works exctly as i wish
:)
:bow: :bow: :bow: :beerchug: