CODEWITHSUNDEEP
excelvba
OmarXShay
OmarXShay

Reputation: 13

How to code a Cancel button to not clear the form?

I'm trying to create a 'Clear All Contents' button that clears my form and basically resets it for the user.

I'm using the 'vbOKCancel' button constant. I thought hitting the 'Cancel' button would stop the code at that point from running any further. But when I hit the 'Cancel' button, it deletes all the data from my form.

Private Sub CommandButton23_Click()

    MsgBox "Are you sure you want to clear this entire form?", vbOKCancel
    
    Dim aRange As Excel.Range
    Set aRange = Range("E7").MergeArea
    
    Dim bRange As Excel.Range
    Set bRange = Range("E9").MergeArea
    
    Dim cRange As Excel.Range
    Set cRange = Range("E11").MergeArea
    
    Dim dRange As Excel.Range
    Set dRange = Range("E15").MergeArea
    
    Dim eRange As Excel.Range
    Set eRange = Range("E17").MergeArea
    
    Dim fRange As Excel.Range
    Set fRange = Range("E19").MergeArea
    
    Dim gRange As Excel.Range
    Set gRange = Range("E23").MergeArea
    
    Dim hRange As Excel.Range
    Set hRange = Range("N7").MergeArea
    
    Dim iRange As Excel.Range
    Set iRange = Range("N9").MergeArea
    
    Dim jRange As Excel.Range
    Set jRange = Range("O11").MergeArea
    
    Dim kRange As Excel.Range
    Set kRange = Range("Q11").MergeArea
    
    Dim lRange As Excel.Range
    Set lRange = Range("N13").MergeArea
    
    Dim mRange As Excel.Range
    Set mRange = Range("N15").MergeArea
    
    Dim nRange As Excel.Range
    Set nRange = Range("O17").MergeArea
    
    Dim oRange As Excel.Range
    Set oRange = Range("Q17").MergeArea
    
    Dim pRange As Excel.Range
    Set pRange = Range("N23").MergeArea
    
    Dim xRange As Excel.Range
    Set xRange = Range("G27").MergeArea
    
    aRange.ClearContents
    bRange.ClearContents
    cRange.ClearContents
    dRange.ClearContents
    eRange.ClearContents
    fRange.ClearContents
    gRange.ClearContents
    hRange.ClearContents
    iRange.ClearContents
    jRange.ClearContents
    kRange.ClearContents
    lRange.ClearContents
    mRange.ClearContents
    nRange.ClearContents
    oRange.ClearContents
    pRange.ClearContents
    xRange.ClearContents
    
End Sub

Upvotes: 1

Views: 133

Answers (1)

BigBen
BigBen

Reputation: 50162

Handle the result of the MsgBox:

Private Sub CommandButton23_Click()
    Dim response As VbMsgBoxResult
    response = MsgBox("Are you sure you want to clear this entire form?", vbOKCancel)

    If response = vbCancel Then Exit Sub

    ...
End Sub

Upvotes: 2

Related Questions