cmBox.setfocus doesn't give cmBox focus. Why?

[EirikDaude]

Why doesn't the combobox "cmbKategori" retain focus after I tab out of it after entering an invalid value? Have I misunderstood something about how events execute? The messagebox shows up, so I don't think so, but I am obviously doing something wrong :P

Private Sub cmbKategori_AfterUpdate()
Dim mbox As Integer

With ufVelgKøyretøy
If Not .cmbKategori.MatchFound And Not .cmbKategori.Value = "" Then
mbox = MsgBox("The text you've entered is not a valid value.", vbOKOnly Or vbCritical, "Invalid value!")
.cmbKategori.SetFocus
.cmbKategori.SelStart = 0
.cmbKategori.SelLength = Len(.cmbKategori.Value)
.cmbUtstyr.Enabled = False
Else
.cmbUtstyr.Enabled = True
Call lagutstyrsliste
.cmbUtstyr.SetFocus
End If
End With
End Sub

[Bob Phillips]

Private Sub cmbKategori_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim mbox As Integer

With ufVelgKøyretøy
If Not .cmbKategori.MatchFound And Not .cmbKategori.Value = "" Then
mbox = MsgBox("The text you've entered is not a valid value.", vbOKOnly Or vbCritical, "Invalid value!")
.cmbKategori.SelStart = 0
.cmbKategori.SelLength = Len(.cmbKategori.Value)
KeyCode = 0
.cmbUtstyr.Enabled = False
Else
.cmbUtstyr.Enabled = True
Call lagutstyrsliste
.cmbUtstyr.SetFocus
End If
End With
End Sub

[EirikDaude]

Thanks for the reply! However the code you posted clears whatever I type into the first combobox and then jumps to the next one, which isn't really what I wanted. I guess it could work if I added some checks for what keys are pressed though... Are there any other ones than enter, escape and tab / shift+tab which can change the focus in the userform? And I still don't see why the setFocus in my first code-example fails to do what I want, when the messagebox does...

[Bob Phillips]

I tested in my form and it worked fine. Post your workbook so we can see what is going on.

[EirikDaude]

It's a work in progress, in particular I'm not sure if the lists will keep looking like they do. But I think it should be ok for testing purposes.

[Bob Phillips]

It doesn't behave for me quite as you described, but I did omit one important bit


Private Sub cmbKategori_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim mbox As Integer

With Me

If KeyCode = 13 Or KeyCode = 9 Then 'only check on return or tab

If Not .cmbKategori.MatchFound And Not .cmbKategori.Value = "" Then

mbox = MsgBox("Teksten du har skrive inn er ikkje ein gyldig verdi.", vbOKOnly Or vbCritical, "Ugyldig verdi")
.cmbKategori.SelStart = 0
.cmbKategori.SelLength = Len(.cmbKategori.Value)
KeyCode = 0
.cmbUtstyr.Enabled = False
Else

.cmbUtstyr.Enabled = True
Call lagutstyrsliste
.cmbUtstyr.SetFocus
End If
End If
End With
End Sub

[EirikDaude]

Yep, that works! Thanks again :) However I am wondering if there are other ways to exit the combobox, for instance, will KeyCode = 9 trigger on shift+tab? And it seems that I can still exit the combobox by using the mouse and clicking e.g. ok or [x], but I guess my original code took care of that, so I'll just have to keep them both...

[Bob Phillips]

The KeyDown has another argument, Shift, that detects if the shift key has been pressed. In this case, it seems you don't care whether or not it is shifted, KeyCode will be 9 for Tab and Shift-Tab.

[EirikDaude]

I see. Anyway, it seems I was a bit quick to celebrate - afterupdate firing on any (?) exit from the box and failing to reset the focus brings back the original problem I posted...

My guess is that afterupdate does something I don't expect after the code is executed, but I am having some trouble figuring out exactly how it behaves :P

[Aflatoon]

Your code does set the focus back but then the focus changes naturally to the next control in the tab order. You could use the BeforeUpdate event instead:


Private Sub cmbKategori_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) Dim mbox As Integer


With Me.cmbKategori
If .Value <> "" Then
If Not .MatchFound Then
mbox = MsgBox("Teksten du har skrive inn er ikkje ein gyldig verdi.", _
vbOKOnly Or vbCritical, "Ugyldig verdi!")
Me.cmbUtstyr.Enabled = False
.SelStart = 0
.SelLength = Len(.Value)
Cancel = True
Else
Me.cmbUtstyr.Enabled = True
Call lagutstyrsliste
Me.cmbUtstyr.SetFocus
End If
End If
End With
End Sub

[EirikDaude]

That works like a charm - thanks a ton for your help. I guess this is something like the Excel equivalent of DoCmd.CancelEvent? I was trying to read up on others with the same problem (http://stackoverflow.com/questions/18990437/why-is-my-setfocus-ignored), but that solution seemed to only work for Access.

However I got a different error when the code got a bit further downstream, after tabbing out of a valid choice:
http://i.imgur.com/bfTwLcJs.png (http://imgur.com/bfTwLcJ) + <TAB> => http://i.imgur.com/egXvqT8s.png (http://imgur.com/egXvqT8) => http://i.imgur.com/CiLRj11s.png (http://imgur.com/CiLRj11)

Any idea of what might be the issue? I certainly can't seem to pin it down :P

[Aflatoon]

That is not an error I'm familiar with. Does it happen if you leave the second combobox enabled at design time and don't change that at run time?

[EirikDaude]

Yep. Seems to be some kind of catchall for errors which aren't specified? And it is a bit odd that the "sub lagutstyrsliste()" if that was part of the problem....

Anyway, here's an updated version of the workbook if anyone wants to have a go at finding what causes the error: 11705 (cmbUtstyr is enabled on initialization in this one)

Going home from work soonish anyway, so this might have to wait for tomorrow :P

[Bob Phillips]

Try this

[snb]

If you change the style of the combobox to dropdownlist. you don't need any check

[EirikDaude]

Both of those works great - with a few very minor issues.

In xld's case the warning triggers twice upon exiting the first combobox while it has an invalid value. I am also having trouble seeing what the changes you've made are? It seems you have edited the tab-order of the elements and removed the setfocus statement from the BeforeUpdate event? Is there something else I'm missing? Otherwise it works great :peace:

With snb's solution the second combobox isn't cleared out if the first one is changed and the OK button stays visible. That should be trivial to fix though. It is also so much easier than what I tried to do, floundering with all sorts of events and stuff :nya: Hopefully I'll remember reading better through the properties of my controls the next time I come across a similar problem... The simpler generation of the list is also a boon for when I'll have to expand this form to work for the entire project. I think I'll try to read up a bit on what else you can do with filtering too. All in all, I think I'll try to adapt this, as it just is so much easier and thus also is likely to be more robust. A couple of questions about it though (I'll try to figure out these on my own too):

Is it possible to grey out the OK button instead of having it hidden 'till there are valid choices in the comboboxes? Not a huge deal, I just find it somewhat more aesthetically pleasing.
Ideally I want the second combobox greyed out too, until it contains choices, but again this is just because I think it would look a bit better. Functionally it works great as it is too.
Why did you choose to use labels instead of frames? To get linebreaks? :biggrin:
I'm having some trouble understanding your Filter- / Evaluate- / Transpose-formula. I am assuming you somehow are filtering the list based on the letters preceeding the list-items, but I can't figure out how transpose is doing in there.... I think what it does is create an array containing the item in column E if the neighbouring cell contains the correct letter, and otherwise let it contain a tilde? And then the filter-function remove all the array entries which are tildes? Correct? :reading:

Hmm... I think that is it - I'll come back to pester you some more if I get stuck again ;)

And again, thanks a lot for all your help! It really makes it so much easier to improve when there are experts like you willing to help. :bow:

Oh, and I am still somewhat curious about what caused the error in my latest attempt at quality-checking the input - does anyone have an idea?

[Bob Phillips]

That is exactly what I did.

[snb]

Is it possible to grey out the OK button instead of having it hidden 'till there are valid choices in the comboboxes? Yes
Ideally I want the second combobox greyed out too, until it contains choices, but again this is just because I think it would look a bit better. You'll manage
Why did you choose to use labels instead of frames? To show you don't need setfocus even if the control isn't frame contained
I'm having some trouble understanding your Filter- / Evaluate- / Transpose-formula. You can only use 'filter' on a 1-dimensional array. Transpose converts a 2-dimensional one into a 1-dimensional array

[EirikDaude]

You'll manage
I think I did...

Private Sub cmbKategori_Change()

With ufVelgKøyretøy
.cmbUtstyr.ListIndex = -1
If .cmbKategori.ListIndex > -1 Then
.cmbUtstyr.Enabled = True
.cmbUtstyr.BackColor = vbWhite
.cmbUtstyr.List = Filter(Evaluate("transpose(if(sheet1!D1:D200=""" & .cmbKategori.List(.cmbKategori.ListIndex, 0) & """,sheet1!E1:E200,""~""))"), "~", False)
End If
End With
End Sub
Thanks for all your help :)

[snb]

Is it possible to grey out the OK button instead of having it hidden 'till there are valid choices in the comboboxes? Yes
Ideally I want the second combobox greyed out too, until it contains choices, but again this is just because I think it would look a bit better. You'll manage
Why did you choose to use labels instead of frames? To show you don't need setfocus even if the control isn't frame contained
I'm having some trouble understanding your Filter- / Evaluate- / Transpose-formula. You can only use 'filter' on a 1-diemensional array. Transpose converts a 2-dimensional one into a 1-dimensional array

[snb]

You did, but I'd prefer:


Private Sub cmbKategori_Change()
with cmbUtstyr
.ListIndex = -1
If cmbKategori.ListIndex > -1 Then
.Enabled = True
.BackColor = vbWhite
.List = Filter(Evaluate("transpose(if(sheet1!D1:D200=""" & cmbKategori.List(cmbKategori.ListIndex, 0) & """,sheet1!E1:E200,""~""))"), "~", False)
End If
end with
End Sub

[EirikDaude]

Heh, I actually went with the other option when it came time to finalize it - and I like to keep the name of the userform in the event too, just in case it is triggered from somewhere else:

Private Sub cmbKategori_Change()
ufVelgKøyretøy.cmbUtstyr.ListIndex = -1
With ufVelgKøyretøy.cmbKategori
If .ListIndex > -1 Then
ufVelgKøyretøy.cmbUtstyr.BackColor = vbWhite
ufVelgKøyretøy.cmbUtstyr.Enabled = True
ufVelgKøyretøy.cmbUtstyr.List = Filter(Evaluate("transpose(if(sheet1!D1:D200=""" & .List(.ListIndex, 0) & """,sheet1!E1:E200,""~""))"), "~", False)
End If
End With
End Sub
Not that it makes much difference, unless I am missing something again.:yes

[Aflatoon]

Strictly speaking it would be better to replace ufVelgKøyretøy with Me or leave it out. I'm not sure what difference you think it would make if the code were triggered from somewhere else?

[EirikDaude]

Then Me would be the form triggering the change, wouldn't it?

- edit - Upon doing some searching and reading it seems I was mistaken, according to page 445 of John Walkenbach's "Excel 2007 - Power Programming with VBA" (http://books.google.de/books?id=HxhXwdUSTe0C&lpg=PA428&ots=V_-GArF2Yo&dq=vba%20me%20or%20userform%20name&hl=no&pg=PA445#v=onepage&q&f=false), I don't need to name the userform when within its code module, and using "Me" within it always references the userform whose code module I'm in.

[GTO]

...I don't need to name the userform when within its code module, and using "Me" within it always references the userform whose code module I'm in.

Hi Erik,

Although I would lean towards explicitness (Qualifying with Me), you can leave it out within the object's (the userform in this case) exactly as you said. This is true of other objects/classes also. For instance, if you are writing in Sheet1's module (which is an object module), Me.Range("A1") or Range("A1") will refer to A1 in Sheet1, regardless of which sheet is active. And yes, Me will always refer to the object (in your case, the userform) .

Hope that helps,

Mark