Msg box response is ignored

Hello,

I am trying to achieve a response from a msgbox which will either continue with the sub or exit. I think my code is right but it doesn't seem to work and just carrys on regardless. Any help would be appreciated.

Private Sub CommandButton1_Click()

Dim MSG1 As Integer

MSG1 = MsgBox("Send Scaffold to destruct?", vbCritical)

If MSG1 = vbCancel Then

Exit Sub

Else
 
Worksheets("sheet2").Activate

ActiveSheet.Range("b4").Select

Do

If IsEmpty(ActiveCell) = False Then

    ActiveCell.Offset(1, 0).Select
   
End If

Loop Until IsEmpty(ActiveCell) = True
    
    
    
    Worksheets("sheet1").Range("A4:h4").Copy
    
    Worksheets("sheet2").Paste Destination:=ActiveCell.Offset(0, -1)
          
    Worksheets("sheet1").Activate
    
    Worksheets("Sheet1").Rows(4).Delete

End If
   
End Sub

Welcome to the forum. One of the important forum rules is to wrap [code] [ /code] tags around your posted code. Please go back to your original post, click on GO ADVANCED, then highlight the code and click on the # icon...thanks, it makes the forum SO much easier to read, and it's a rule! Notice how much easier it is to read my code below.

Meanwhile...try this:

Private Sub CommandButton1_Click()

If MsgBox("Send Scaffold to destruct?", vbYesNo + vbCritical) = vbNo Then Exit Sub

Worksheets("sheet2").Activate
ActiveSheet.Range("b4").Select

    Do
        If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell) = True

Worksheets("sheet1").Range("A4:H4").Copy
Worksheets("sheet2").Paste Destination:=ActiveCell.Offset(0, -1)

Worksheets("sheet1").Activate
Worksheets("Sheet1").Rows(4).Delete

End Sub

I have added Code Tags this time, please be sure to read the Forum rules before posting again.

This code asks for a response then copies the data. Note it does not use the loop - which would take longer, nor is it necessary to activate or select sheets and ranges

Option Explicit

Private Sub CommandButton1_Click()
    Dim NextCl As Range

    Select Case MsgBox("Send Scaffold to destruct?", vbYesNo Or _
                                                     vbQuestion Or vbDefaultButton1, "Action")
        Case vbYes
            With Worksheets("sheet2")
                Set NextCl = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
                Worksheets("sheet1").Range("A4:h4").Copy NextCl
                Worksheets("Sheet1").Rows(4).Delete
            End With
        Case vbNo
            Exit Sub
    End Select
End Sub

aaron.ronnie

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

You habe indicated that the replies have resolved your pronlem - Please mark your thread as Solved

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


Re Wrapping VBA code

The easiest way to use code tags is to click on the # icon in the text formatting window

This will add
[ Code] [\ Code]
to your message window with the cursor between the ][ brackets - this is were your VBA code goes.
You will not see your code displayed correctly until you preview or post your message

Note:- there is no space before the word Code like I have shown in the example above

On posted correctly
your VBA code will
appear in its own 
scrollable window
within your message