Need help again.

[klarowe]

I am setting up seperate sheet Macros that will produce input boxes for part numbers whenever a sheet is opened and the selected cells are blank. I have the code working great, but if I choose not to fill in the boxes and then save, because of my save-as code, it selects each sheet to select cell A1 (to bring all sheets to the top) and then every single sheet inputbox pops up before allowing me to close. Is there a way to change/modify the codes so that the sheet code will ONLY run when the user selects the sheet (not when a macro selects it)???

InputBox Code Example (different for each sheet):

Private Sub Worksheet_Activate()
Run1:    If ActiveSheet.Range("C4") = "" Then
        GoTo 1
        Else: GoTo Run2
1:      PartNumber1 = Application.InputBox("Enter 1st Stage Shroud Part Number", "Part Number", vbOKOnly)
        ActiveSheet.Range("C4") = PartNumber1
        If PartNumber1 = False Then ActiveSheet.Range("C4") = ""
        SerialNumber1 = Application.InputBox("Enter 1st Stage Shroud S/N", "Serial Number", vbOKOnly)
        ActiveSheet.Range("G4") = SerialNumber1
        If SerialNumber1 = False Then ActiveSheet.Range("G4") = ""
    End If
    
Run2:  If ActiveSheet.Range("C11") = "" Then
        GoTo 2
    Else: GoTo Run3
2:      PartNumber2 = Application.InputBox("Enter 2nd Stage Shroud Part Number", "Part Number", vbOKOnly)
        ActiveSheet.Range("C11") = PartNumber2
        If PartNumber2 = False Then ActiveSheet.Range("C11") = ""
        SerialNumber2 = Application.InputBox("Enter 2nd Stage Shroud S/N", "Serial Number", vbOKOnly)
        ActiveSheet.Range("G11") = SerialNumber2
        If SerialNumber2 = False Then ActiveSheet.Range("G11") = ""
    End If
    
Run3:  If ActiveSheet.Range("C18") = "" Then
        GoTo 3
    Else: GoTo Run4
3:      PartNumber3 = Application.InputBox("Enter 3rd Stage Shroud Part Number", "Part Number", vbOKOnly)
        ActiveSheet.Range("C18") = PartNumber3
        If PartNumber3 = False Then ActiveSheet.Range("C18") = ""
        SerialNumber3 = Application.InputBox("Enter 3rd Stage Shroud S/N", "Serial Number", vbOKOnly)
        ActiveSheet.Range("G18") = SerialNumber3
        If SerialNumber3 = False Then ActiveSheet.Range("G18") = ""
    End If
    
Run4:  If ActiveSheet.Range("C25") = "" Then
        GoTo 4
    Else: GoTo Ex
4:      PartNumber4 = Application.InputBox("Enter 4th Stage Shroud Part Number", "Part Number", vbOKOnly)
        ActiveSheet.Range("C25") = PartNumber4
        If PartNumber4 = False Then ActiveSheet.Range("C25") = ""
        SerialNumber4 = Application.InputBox("Enter 4th Stage Shroud S/N", "Serial Number", vbOKOnly)
        ActiveSheet.Range("G25") = SerialNumber4
        If SerialNumber4 = False Then ActiveSheet.Range("G25") = ""
    End If
Ex:  Exit Sub
End Sub

Before Save-Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.Run "SequentiallyNumberVisiblePagesOnly"
    Dim ws As Worksheet
   Const TopLeft As String = "A1"
    For Each ws In Sheets
        Application.ScreenUpdating = False
        Application.GoTo ws.Range(TopLeft), Scroll:=True
    Next
        Application.ScreenUpdating = True
        Sheets("Home").Activate
End Sub

Thanks again.

 

[ColinKJ]

Hi klarowe,

In your Private Sub Workbook_BeforeSave, try:

Application.EnableEvents = False
For Each ws In Sheets
        Application.ScreenUpdating = False
        Application.GoTo ws.Range(TopLeft), Scroll:=True
    Next
Application.EnableEvents = True

 

[klarowe]

Worked like a charm. Thanks!!