Hi I've just started developing in Office 2000 (previously only experienced in
Excel 95). Reading through this group I noticed a couple of other postings
relating to SetFocus but they didn't seem to carry the information I need.

I'm developing a Wizard/Template and everything's going swimmingly except I can't
seem to get any Textbox on any Userform to contain the insertion point upon
initialisation of the Userform.

e.g.
Private Sub UserForm_Initialize()
    MainCode.ReadBUINI
    MainCode.GetSenderDetails  ' Fill the Textboxes from an Ini file

    If tbName.text = "" then tbName.SetFocus  ' This line appears to do nothing!

End Sub

tbName is the name of a Textox that I want the user to be able to start
immediately typing into if it is empty. It is one of a number of Textboxes in
Frame2. There are 2 other frames on the Userform aswell.

If I press tab the insertion point does appear in the next Textbox in the Tab
Order. So I'm kind of halfway there but I'm going spare trying find a way to get
the Insertion Point to appear in tbName.

TIA
    Nick.

Hi Nick,

Put the SetFocus statement in the Userform activate event.
Should work.

Success,
Perry

Hi I've just started developing in Office 2000 (previously only experienced
in
Excel 95). Reading through this group I noticed a couple of other postings
relating to SetFocus but they didn't seem to carry the information I need.

I'm developing a Wizard/Template and everything's going swimmingly except I
can't
seem to get any Textbox on any Userform to contain the insertion point upon
initialisation of the Userform.

e.g.
Private Sub UserForm_Initialize()
    MainCode.ReadBUINI
    MainCode.GetSenderDetails  ' Fill the Textboxes from an Ini file

    If tbName.text = "" then tbName.SetFocus  ' This line appears to do
nothing!

End Sub

tbName is the name of a Textox that I want the user to be able to start
immediately typing into if it is empty. It is one of a number of Textboxes
in
Frame2. There are 2 other frames on the Userform aswell.

If I press tab the insertion point does appear in the next Textbox in the
Tab
Order. So I'm kind of halfway there but I'm going spare trying find a way to
get
the Insertion Point to appear in tbName.

TIA
    Nick.

Thanks Perry,

    After posting I browsed this group back a few pages and eventually found the
same advice (apologies for not searching harder before posting). It still didn't
work!

However, I discovered that by first setting focus to another, irrelevant, Textbox
and then to tbName I could achieve the desired effect. Is this normal behaviour
or have I got some, system specific, bug?

I also have ComboBox configured just as DropDown which I'd like to SetFocus to,
if the user tries to progress without selecting from the list, but the same
technique doesn't work for it. Any ideas?

TIA
    Nick

Hi Nick,

The problem with a combo designed as dropdown only
is that you can't (or at least far to difficult) trap the
validation.
DropDown combos jump to the first listitem if entries
do not exist.
Therefor, it's hard to trap an input validation.

Alternatively, you could try:
design it as a DropDownCombo and

'*****************
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Cancel = Not ComboValid(ComboBox1)
End Sub
'------------
'*****************
Function ComboValid(ComboItem As String) As Boolean
  For x = 0 To ComboBox1.ListCount - 1
    If Trim(ComboItem) <> ComboBox1.List(x) Then _
          ComboValid = False: Exit Function
  Next
End Function
'------------

If you want to use this function to be used for
more ComboDropDowns, use it this way:

'*****************
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Cancel = Not ComboValid(ComboBox1, ComboBox1.Name)
End Sub
'------
'*************
Function ComboValid(ComboItem As String, _
    ComboName As String) As Boolean
  For x = 0 To Controls(ComboName).ListCount - 1
    If ComboItem <> Controls(ComboName).List(x) Then _
          ComboValid = False: Exit Function
  Next
End Function
'-----------

Note:
In the last function, pass the value of the applicable combo as
first argument in the function, it's name as the second argument.

Success,
Perry

Thanks Perry,

    After posting I browsed this group back a few pages and eventually found
the
same advice (apologies for not searching harder before posting). It still
didn't
work!

However, I discovered that by first setting focus to another, irrelevant,
Textbox
and then to tbName I could achieve the desired effect. Is this normal
behaviour
or have I got some, system specific, bug?

I also have ComboBox configured just as DropDown which I'd like to SetFocus
to,
if the user tries to progress without selecting from the list, but the same
technique doesn't work for it. Any ideas?

TIA
    Nick

Perry,

Thanks again, you must have spent a fair amount of time for such a considered and
in-depth response. I will store your code for future use but hope you won't feel
too crushed when I tell you that is not my current problem. To demonstrate here
is part of the code I am using...

    If tbName.Value = "" Or tbName.Value = " " Then
        MsgBox "You have not entered a Name!", vbExclamation, _
                "Sender Name missing"    'This works!
        tbTitle.SetFocus
        tbName.SetFocus    'This works!
        Exit Sub
    End If
    If ddUnit.ListIndex = -1 Then
        MsgBox "You have not selected a Business Unit!", vbExclamation, _
               "Business Unit missing"   'This works!
        tbTitle.SetFocus
        ddUnit.SetFocus    'This does not work!
        Exit Sub
    End If

...If the user hasn't selected anything from the DropDown 'ddUnit' it remains
blank and the ListIndex remains at -1 so validation works fine and the
appropriate MsgBox is displayed. It is the SetFocus which, while 'quirky' with
TextBoxes, appears to not work at all with my ComboBox/DropDown (in properties
the ddUnit ComboBox Style is set to '2 - fmStyleDropDownList').

When I tab to the DropDown, from the TextBox above, the flashing cursor appears
in the blank DropDown control without defaulting to any list entries. It would be
nice to duplicate that behaviour after the user 'OK's the MsgBox.

Cheers,
       Nick

Hi Nick,

Feeling not too crushed and didn't spend that much time, don't worry.
However, I have an alternative for you:

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ComboBox1.ListIndex = -1 Then
  MsgBox "You did not make a selection"
  Cancel = True
  SendKeys "%{down}"            '<----  ALT+DOWN drops down the combo
End If
End Sub

This code extract will intercept the tab upon no selection,
and will force the combo to drop down.

Success,
Perry

Perry,

Thanks again, you must have spent a fair amount of time for such a
considered and
in-depth response. I will store your code for future use but hope you won't
feel
too crushed when I tell you that is not my current problem. To demonstrate
here
is part of the code I am using...

    If tbName.Value = "" Or tbName.Value = " " Then
        MsgBox "You have not entered a Name!", vbExclamation, _
                "Sender Name missing"    'This works!
        tbTitle.SetFocus
        tbName.SetFocus    'This works!
        Exit Sub
    End If
    If ddUnit.ListIndex = -1 Then
        MsgBox "You have not selected a Business Unit!", vbExclamation, _
               "Business Unit missing"   'This works!
        tbTitle.SetFocus
        ddUnit.SetFocus    'This does not work!
        Exit Sub
    End If

...If the user hasn't selected anything from the DropDown 'ddUnit' it
remains
blank and the ListIndex remains at -1 so validation works fine and the
appropriate MsgBox is displayed. It is the SetFocus which, while 'quirky'
with
TextBoxes, appears to not work at all with my ComboBox/DropDown (in
properties
the ddUnit ComboBox Style is set to '2 - fmStyleDropDownList').

When I tab to the DropDown, from the TextBox above, the flashing cursor
appears
in the blank DropDown control without defaulting to any list entries. It
would be
nice to duplicate that behaviour after the user 'OK's the MsgBox.

Cheers,
       Nick

Perry,

Success indeed! Many thanks for your patience and persistance.
The following code extract works a treat and  the result is, I
think, better than the solution I was looking for...

    If ddUnit.ListIndex = -1 Then
        MsgBox "You have not selected a Business Unit"
        ddUnit.SetFocus
        SendKeys "%{down}"
        Exit Sub
    End If

What a star!

Cheers,
        Nick.