+ Reply to Thread
Results 1 to 7 of 7

Msg box response is ignored

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-21-2007
    Location
    Lima, Peru
    MS-Off Ver
    2000, 2007, 2010
    Posts
    674

    Re: Msg box response is ignored

    Hi

    Try the following code, which splits up your macro in to two parts one for the button and one for the code you wish to call.

    Private Sub CommandButton1_Click()
    
    Dim Msg, Style, Title, Response
    Msg = "Send Scaffold to destruct?"
    Style = vbYesNo + vbCritical
    Title = "Scaffold Destruct"
    
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
    Call scaffold
    Else
    End
    End If
    
    End Sub
    
    Sub scaffold()
    
    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 Sub
    Regards

    Jeff

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Msg box response is ignored

    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
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1