lundi 28 octobre 2019

Excel VBA stop code where conditions are met

I'm struggling getting my code to work.

I have a button on the excel sheet that when triggers 1. checks required fields value is 0, if not then message box and end code

  1. checks if the reference number already exists on a master tab, if the reference exisits, message box and end code

  2. if 1 and 2 pass then perform a copy and paste as values for 3 ranges then message box.

I've tried a number of options but can't get it to work

    Function Mand() As Boolean
   'checks that mandatory fields have been updated

   If Sheets("INPUT").Range("C11") > 0 Then MsgBox "Mandatory Fields Missing" & vbNewLine & "Changes Not Saved!"
   Mand = True

    End Function


    Function RecEx() As Boolean
    'checks that the reference number does not exisit on the High Level master list

    dup = WorksheetFunction.CountIf(Sheets("High_Level_List").Columns(1), Sheets("INPUT").Range("C17"))
    If dup > 0 Then MsgBox "This Record Exists!!!" & vbNewLine & "If saving an update, use the Save Changes button"
    RecEx = True   

    End Function


    Sub RegisterNewRec()
    ' checks 2 functions, if either are TRUE then exit, otherwise update master

    If Mand Then Exit Sub

        If RecEx Then Exit Sub

    End If

    Dim rng As Range
    Set rng = Sheets("INPUT").Range("AO2:CX2")
    Sheets("High_Level_List").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value

    'more code that updates master

    MsgBox "Record added to Master"

    End Sub

Aucun commentaire:

Enregistrer un commentaire