Error Handling

Rodney Jorgensen

Active Member
Joined
Nov 9, 2007
Messages
411
For the code below, I want to set up error handling to just leave the textbox blank on the form. If there is not an e-mail address in the range that it is checking then I want the textbox to be loft blank. I do not want a message box asking me what to do, just leave blank and resume the code.

Code:
    Sheets("Data Sort").Range("A2:A25").Select
    Selection.Find(What:="E-Mail", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    
    Me.txtE_mail.Value = ActiveCell.Value

Thanks
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Try

Code:
Dim Found As Range
Set Found = Sheets("Data Sort").Range("A2:A25").Find(What:="E-Mail", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)
If Not Found Is Nothing Then Me.txtE_mail.Value = Found.Offset(, 1)
 
Upvote 0
I think you have to delete the

after:=activecell

bit -the same thing happened to me.
 
Upvote 0
The line that is highlighted yellow is the Show form line, but when stepping through using F8, the line that it gives the error on is:
Code:
Set Found = Sheets("Data Sort").Range("A2:A25").Find(What:="E-Mail", LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)

Also, I deleted the After:AciveCell form it and it worked, except that the

Code:
If Not Found Is Nothing Then Me.txtE_mail.Value = Found.Offset(, 1)

Still is not blank. It still takes on the value of the active cell which is the last one from the code.

This is my complete code for the Userform Initialize. I do want to add the error handing to all the textboxes.

Code:
Private Sub UserForm_Initialize()

    Dim Found As Range
    
    Sheets("Data Sort").Range("A2:A25").Select
    Selection.Find(What:="Account", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    
    Me.txtAccount.Value = ActiveCell.Value
    
    Sheets("Data Sort").Range("A2:A25").Select
    Selection.Find(What:="Street", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    
    Me.txtStreet.Value = ActiveCell.Value
    
    Sheets("Data Sort").Range("D1").Select
    
    Me.txtCity.Value = ActiveCell.Value
    
    Sheets("Data Sort").Range("D2").Select
    
    Me.txtState.Value = ActiveCell.Value

    Sheets("Data Sort").Range("D3").Select
    
    Me.txtZIP.Value = ActiveCell.Value
    
    Set Found = Sheets("Data Sort").Range("A2:A25").Find(What:="E-Mail", LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False)
    If Not Found Is Nothing Then Me.txtE_mail.Value = Found.Offset(, 1)
    
    Me.txtE_mail.Value = ActiveCell.Value

    Sheets("Data Sort").Range("A2:A25").Select
    Selection.Find(What:="Phone", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    
    Me.txtPhone.Value = ActiveCell.Value
    
    Range("B2").Select
    Application.ScreenUpdating = True

End Sub

Note: Range D1:D3 gets its values before the form is opened with this code.
Code:
Sub Show_Address_Form()
    Dim i As Variant
    Application.ScreenUpdating = False

    Sheets("Data Sort").Range("A2:A25").Select
    Selection.Find(What:="City/State/Zip", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    i = ActiveCell.Address
    Range("D1") = "=LEFT(" & i & ",LEN(" & i & ")-14)"
    Range("D2") = "=MID(" & i & ",LEN(" & i & ")-12,2)"
    Range("D3") = "=RIGHT(" & i & ",10)"
    frmAddress.Show
    End Sub

This is all I have, I hope this helps.
 
Upvote 0
Try

Code:
Private Sub UserForm_Initialize()
Dim Found As Range
With Sheets("Data Sort").Range("A2:A25")
    Set Found = .Range("A2:A25").Find(What:="Account", LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Me.txtAccount.Value = Found.Offset(, 1).Value
    Set Found = .Find(What:="Street", LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Me.txtStreet.Value = Found.Offset(, 1).Value
    Me.txtCity.Value = Sheets("Data Sort").Range("D1").Value
    Me.txtState.Value = Sheets("Data Sort").Range("D2").Value
    Me.txtZIP.Value = Sheets("Data Sort").Range("D3").Value
    Set Found = .Find(What:="E-Mail", LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)
    If Not Found Is Nothing Then Me.txtE_mail.Value = Found.Offset(, 1)
    Set Found = .Find(What:="Phone", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Me.txtPhone.Value = Found.Offset(, 1).Value
End With
End Sub
 
Upvote 0
You are welcome. But I can't help but notice that you are northwards of 300 posts but you are still using Select/Activate when it isn't necessary. Learning VBA may be a blast but you should take the opportunity to really learn whilst you are at it.
 
Upvote 0
I have been out of the loop for about 4 months and had lost quite a bit of my knowledge, but with these current posts am getting it back.

The only text that I own for VBA is the Dummies book by John Walkenbach and that only goes so far.

The rest of my knowledge has come mostly from Mr. Excel, but from other sites as well. What I have found it hard to do is get the question "Why would you code it this way" answered on these posts because everyone that moderates these posts is very busy with that. I have just tried my hardest to figure out how and why the coding works, but sometimes, I have just had to accept that it just works and go forward.

I really do like vba coding and would like to get to the point where I could moderate also. This is why I do post many questions and try to get as much out of it as humany possible.

Any suggestions for me would be grealy appreciated.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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
Back
Top