CODEWITHSUNDEEP
excelvbauserform
SanderJensen
SanderJensen

Reputation: 41

How would I add a validation check to this code?

I have this code to check for an ID number and thereafter copy the row in which the ID is located into another spreadsheet. If the ID number that is input in the userform does not exist I want there to be an error message that prompts you to try again and that also cancels all of the code that is executed when the ID number that has been input exists

I have tried a simple If statement but did not get it to work

Option Explicit

Private Sub CommandButton1_Click()

Dim wsSource As Worksheet
Set wsSource = Sheets("Arkiv")
Dim wsDestination As Worksheet
Set wsDestination = Sheets("DN")

Dim IDnum As String
IDnum = TextBox1.Text

Dim idRow As Long
idRow = wsSource.Columns("A:A").Find(what:=IDnum, lookat:=xlWhole).Row

Dim SourceAdresses() As Variant
SourceAdresses = Array("B" & idRow, "C" & idRow, "D" & idRow, "E" & idRow, "F" & idRow, "G" & idRow, "H" & idRow, "I" & idRow)

Dim DestinationAdresses() As Variant
DestinationAdresses = Array("D9", "E9", "I9", "C20", "D20", "E45", "g20", "H20", "I20")

Dim i As Long
For i = LBound(SourceAdresses) To UBound(SourceAdresses)
    wsDestination.Range(DestinationAdresses(i)).Value = wsSource.Range(SourceAdresses(i)).Value
Next i

wsDestination.Activate
Unload Me
MsgBox "Data is now available"

End Sub

I expect the user form to prompt an "Error, ID was not found/does not exist in the archive" message and cancel all following code from being executed and there after let you retry typing an ID

Upvotes: 0

Views: 38

Answers (1)

DDV
DDV

Reputation: 2384

I'm not sure of your entire process, but the below code (not tested) should be able to give you SOME idea what you need to do to create this error handling. Effectively, using a For loop will do this...

Option Explicit

Private Sub CommandButton1_Click()

    Dim wsSource As Worksheet
    Set wsSource = Sheets("Arkiv")
    Dim wsDestination As Worksheet
    Set wsDestination = Sheets("DN")

    Dim IDnum As String
    Dim idRow As Long
    Dim SourceAdresses() As Variant
    Dim DestinationAdresses() As Variant
    Dim i As Long, j As Long

    For j = 1 To 5

        IDnum = TextBox1.Text
        idRow = wsSource.Columns("A:A").Find(what:=IDnum, lookat:=xlWhole).Row

        If idRow = 0 Then
            MsgBox "Error finding ID number! Please try again!"
        Else
            Exit For
        End If

        If j = 5 Then
            'number of attempts exceeded - end program
            MsgBox "Could not locate the ID number! Closing program.."
            Exit Sub
        End If
    Next j

    SourceAdresses = Array("B" & idRow, "C" & idRow, "D" & idRow, "E" & idRow, "F" & idRow, "G" & idRow, "H" & idRow, "I" & idRow)
    DestinationAdresses = Array("D9", "E9", "I9", "C20", "D20", "E45", "G20", "H20", "I20")

    For i = LBound(SourceAdresses) To UBound(SourceAdresses)
        wsDestination.Range(DestinationAdresses(i)).Value = wsSource.Range(SourceAdresses(i)).Value
    Next i

    wsDestination.Activate

    Unload Me
    MsgBox "Data is now available"

End Sub

Upvotes: 1

Related Questions