SetFocus VBA Not Working

Irene83

New Member
Joined
May 20, 2014
Messages
8
Hello,
I am new to VBA. Could you, please, tell me what's wrong with the code

Code:
Private Sub HOSPITAL_Change()

    If HOSPITAL.Value <> "Other" Then
        OTHHOSP_label.Visible = False
        Me.OTHHOSP.Visible = False
        Me.OTHHOSP.Value = Null
    End If
    
    If HOSPITAL.Value = "Other" Then
        OTHHOSP_label.Visible = True
        Me.OTHHOSP.Visible = True
        Me.OTHHOSP.SetFocus
        Me.OTHHOSP.ValidationRule = "<> Null"
        Me.OTHHOSP.ValidationText = "Please, name other hospital"
        Me.Refresh
    End If

End Sub

Private Sub OTHHOSP_LostFocus()
    'If OTHHOSP.Value = "" Then
     '     MsgBox "Please, name other hospital"
      '    OTHHOSP.SetFocus
    'End If
    If IsNull(Me.OTHHOSP) Then
        Me.OTHHOSP.SetFocus
        MsgBox "Please, name other hospital"
    End If
End Sub

Validation rule for some reason does not start working until I enter something in the field and then delete it to make it Null. That's why I added LostFocus event. When I am loosing focus from the field, I get the intended message, however the SetFocus command does not work.

Thank you.

Regards,
Irene
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,410
Office Version
  1. 365
Platform
  1. Windows
What you haven't said is what you're trying to do, but you are probably trying to do it with the wrong event. The Change event fires every time you add/delete one character from a textbox. As for validation rules on controls or their bound fields, it will not become evident unless you break the rule, and the odd thing about your choice of events is that the first time you enter the control and type something, it is NULL until the control loses focus.

You can either create a validation rule at the table level or control, but it will not be evaluated until the control updates. Suggest you try the Before_Update event if you want to enact data entry constraints before they become visible or actually update a record. The After_Update event is another possibility, but it cannot be cancelled whereas the other can. Whether or not that is of concern depends on what you're trying to do.

Suggest you research the "order of events for.." the control type you want to use.
 
Last edited:

Irene83

New Member
Joined
May 20, 2014
Messages
8
Thank you for your reply.
I understand that Change event fires on changing the field and nothing should happen until I actually type something. But in my code it sets validation rule for the field OTHHOSP after changing the field HOSPITAL, so the validation rule should be already working before I try to change anything.
And I still cannot understand why
Code:
Private Sub OTHHOSP_LostFocus()
    If IsNull(Me.OTHHOSP) Then
        MsgBox "Please, name other hospital"
        Me.OTHHOSP.SetFocus
    End If
End Sub
produces a message, but does not return back to the field, because LostFocus precedes GotFocus in order chain.

Thank you.
Regards,
Irene
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,410
Office Version
  1. 365
Platform
  1. Windows
Without benefit of knowing exactly what controls are what and the process, I see the first part this way:
With a new form open, I go to "Hospital" textbox or combo box (whichever it is) and start to type the word "Other". As soon as I type O, the change event fires and the control is Null. I keep going until I type the r in Other and the change event fires for the last time (if I don't backspace or whatever). After this last fire of the change event, the control value is still Null because I haven't moved the focus, so it will NEVER be true.

As for the focus problem, I guess you can't set focus to a control within the lost focus event for that control. I've never tried, because the proper method would be to put your validation code into the Before_Update event because it makes more sense to not move off the control if validation fails. So the correct method would be

Code:
Private Sub OTHHOSP_BeforeUpdate(Cancel as Integer)
    If IsNull(Me.OTHHOSP) Then
        MsgBox "Please, name other hospital"
        Cancel = True
    End If
End Sub
However, doing this for a Null value doesn't make sense to me. If I fill out this control, then change my mind about another one and delete the contents of this control before going back to correct a previous one, the validation will not allow me to leave this control and the message will continually pop up each time I try to make my corrections elsewhere.
 
Last edited:

Irene83

New Member
Joined
May 20, 2014
Messages
8
Thank you, you have described exacxtly what the code should do.
I have a combo box for the HOSPITAL, and on choosing "Other", a textbox appears, where one should enter the hospital name.
Using your code here
Code:
Private Sub HOSPITAL_Change()

    If HOSPITAL.Value <> "Other" Then
        OTHHOSP_label.Visible = False
        Me.OTHHOSP.Visible = False
        Me.OTHHOSP.Value = Null
    End If
    
    If HOSPITAL.Value = "Other" Then
        OTHHOSP_label.Visible = True
        Me.OTHHOSP.Visible = True
        Me.OTHHOSP.ValidationRule = "<> Null"
        Me.OTHHOSP.ValidationText = "Please, name other hospital"
        Me.Refresh
        Me.OTHHOSP.SetFocus
    End If
End Sub
Private Sub OTHHOSP_BeforeUpdate(Cancel as Integer)
    If IsNull(Me.OTHHOSP) Then
        MsgBox "Please, name other hospital"
        Cancel = True
    End If
End Sub

produces the wollowing error: The expression On Change you entered as the event propery setting produced the following error: Ambiguous name detected: OTHHOSP_BeforeUpdate.
I agree, that I probably should not set strict validation rules, and a simple reminder message would be enough.
Thank you.

Regards,
Irene
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,410
Office Version
  1. 365
Platform
  1. Windows
Well, it doesn't do that for me. Aside from that, I should repeat that checking for null makes no sense, especially since I've tried it. It is always null until the focus moves off of the control. I'd suggest the following approach (you should not copy the entire code block; rather, create your subs in the normal fashion, then copy/paste the text between the Sub/End Sub parts. Creating a procedure by dumping the whole thing in the editor can result in it being dis-connected from the form or control):
Code:
Private Sub HOSPITAL_AfterUpdate()
If HOSPITAL <> "Other" Then
   OTHHOSP_label.Visible = False
     With Me.OTHHOSP
      .Visible = False
      .Value = ""
     End With
ElseIf HOSPITAL = "Other" Then
   OTHHOSP_label.Visible = True
   Me.OTHHOSP.Visible = True
   Me.OTHHOSP.SetFocus
End If
End Sub

Code:
Private Sub OTHHOSP_LostFocus()
If IsNull(Me.OTHHOSP) Or Me.OTHHOSP = "" Then
  MsgBox "Please, name other hospital"
End If
End Sub
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,576
Messages
5,765,194
Members
425,266
Latest member
CPAgirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top