Consulting

Results 1 to 7 of 7

Thread: How to set focus back into the textbox after an error message?

  1. #1
    VBAX Regular
    Joined
    Apr 2016
    Posts
    14
    Location

    Post How to set focus back into the textbox after an error message?

    I have a user form with several textboxes and when user inputs data , it gets stored on excel sheet. I am having trouble setting the cursor back into the textbox after an error message, while I am doing the data validation in my last command button called save. Although, the problem does not seem to appear, while I am doing the data validation in the exit events of the individual text boxes. The code is as seen below.

    Code:
    Private Sub CmdSave1_Click()
    
     Dim row As Long
      Dim c As Range
     row = ActiveCell.row
       
       
      For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).row)
       'Call txt_BPName1_Exit(c)
       
                If c.Value = txt_BPName1 Then
                    MsgBox " Duplicate Found.Please enter unique Base Product"
                    txt_BPName1.SetFocus  '>>> the cursor does not return to textbox here.
                    'cancel = True
                    End If
                    Next
                    txt_SPName1.SetFocus
                    'Exit Sub
                 
                    For Each c In Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).row)
                    
                 
            If c.Value = txt_SPName1 Then
               MsgBox "Cell " & c.Address & " Duplicate sub Product Found."
                  'txt_SPName1.SetFocus
                  cancel = True
                  
                  End If
                  Next
                  txt_loc1.SetFocus
                  'Exit Sub
    I have attached the file here for reference. please check out Add_InventoryBP Userform for full code.

    Thanks,
    Kann
    Attached Files Attached Files

  2. #2
    It works for me if you exit the procedure after setting focus, which is what you want to do, right?
      For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).row)
    
       'Call txt_BPName1_Exit(c)
       
                If c.Value = txt_BPName1 Then
                    MsgBox " Duplicate Found.Please enter unique Base Product"
                    txt_BPName1.SetFocus  '>>> the cursor does not return to textbox here.
                    Exit Sub
                End If
      Next c

  3. #3
    VBAX Regular
    Joined
    Apr 2016
    Posts
    14
    Location
    Exit sub does bring back the cursor to the textbox. But, I don't want to exit the sub at that point, because I have other lines of code that I need the program to do before it should exit. Is there a substitute for exit sub ?. or Even if you have to exit out of a sub, is there a way to enter back again.?

    I had originally the data validation happening within the exit events of each textbox. But, now need to make it happen within the save button.

  4. #4
    I don't know how to stop any code and have it start over again after you've entered a value in a user form text box.

    I think this is why most people use the exit event because it runs automatically and the user just tries again, no buttons to push after fixing the problem.

    I guess you could have an input box popup asking you for a new value and then you could have a jump to point to take you back before the loop and make it start all over again, but I don't know how to get you back to the form to enter a value there and have the loop just start over. I would say assign a variable for what's in the textbox that way you can change that variable with the input box value if a duplicate is found. Maybe you could assign the input box value using the name of the text box I don't know, I would have to test it to find out.

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Kann,

    I'm not an Excel expert, but what you are saying really makes no sense. Look at your code. Why would you want to tell a user he or she as entered and invalid value and then continue an write that invalid value the sheet?

     Dim bInvalid As Boolean
     bInvalid = False
      For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).row)
        If c.Value = txt_BPName1 Then
          MsgBox " Duplicate Found.Please enter unique Base Product"
          txt_BPName1.SetFocus
          bInvalid = True
          Exit For
        End If
      Next
      If Not bInvalid Then 'What is the point of running this code if you have already set the focus to txt_BPName1?
        For Each c In Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).row)
          If c.Value = txt_SPName1 Then
            MsgBox "Cell " & c.Address & " Duplicate sub Product Found."
            txt_SPName1.SetFocus
            bInvalid = True
            Exit For
          End If
        Next
      End If
      
      'So even if the values in the textboxes validatated abuve are "Invalid",  you still want to
      'write them to your sheet???
    Greg

    Visit my website: http://gregmaxey.com

  6. #6
    That's not what I get out of it. He wants to be able to fix the issue by being directed back to the form where the user would enter a new value and you would somehow be put right back into the code without having to hit the save button again. My solution which I personally don't like, is to have an input box come up ask for a new value and use a goto to re-enter the for loop with the new value.

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    skywriter,

    Personally, I would disable the Save command button until the conditions for its successful execution was right.
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •