Assigning a button to a specific subroutine RRS feed

  • Question

  • Hi All,

    I am curious.  I have a macro that I have assigned to a button so that it starts at the push of that button.  Now I am to the point in the macro that will need the user to input some data before the macro can continue to complete the report that I need it to do.  The information that will be needed will be to get a whole sheet of information from the internet and copy and paste it into a worksheet of excel.  But I am not sure how to temporarily stop the macro so the user can input this data.  One idea I have is to insert a second button that the macro won't go past until this button is pushed.  I can't seem to be able to do this though.  How can I insert a button in order to have the macro continue on once the user has inputted the needed data?  Below is the code I am working with so far.  Obviously there is much more code that is in this macro as a whole.  Right now I have pasted in a serial number from another sheet and now will paste in the test data for that serial number that will be found from the web and then imported by the user. 

    Sub Test_Serial_Against_Previous()
      Dim rowmax As Integer, rowindex As Integer
        rowmax = Worksheets("Serial Numbers").Range("B65536").End(xlUp).row
        For rowindex = 3 To rowmax
        Worksheets("Serial Numbers").Range("B" & rowindex).Copy
        Sheets("Stats").Range("B3").PasteSpecial xlPasteValues
        Call GetTestData

    End Sub

    Sub GetTestData()

    '   In this sub you will have to get test data off of the web
    '   And copy it into the sheet TestData in order to run minitab
    '   On it to get sigma information about the FIFO glass

    Dim colmax As Integer
        MsgBox "This will have instructions on what to do."
        colmax = Worksheets("TestData").Range("FF3").End(xlToLeft).Column
        Sheets("Stats").Range("C2").PasteSpecial xlPasteValues

    End Sub

    Thursday, February 5, 2015 3:08 PM

All replies

  • I would end the first macro after asking for the user to go fetch the data from the internet and paste it into the worksheet. 

    I would further instruct the user to switch to the Stats sheets when the pasting is finished, and I would use the Stats sheet's activate event to start the remainder of the macro - you could also use flags (written to a property or to a cells) in that event to make sure that the code is only run when the first macro has been run and the TestData sheet is not blank.

    Thursday, February 5, 2015 3:47 PM
  • I had thought of that, one problem I have is that I need to cycle through a few serial numbers that I need to get data for and do statistics on them.  Is there a break function I can put in that will pause the macro for a little bit until it is told to continue?
    Thursday, February 5, 2015 3:50 PM
  • No - you really should use a separate macro to call the other - you can pass a parameter to the master macro to control the looping, if you want.
    Thursday, February 5, 2015 7:00 PM