Disable a open event

[bamaisgreat]

I have a form that opens up everytime the workbook is opened. In the code below it does a save as, when this happens I need the New Workbook to not have this form opening up . Im not sure if Im real clear on this but however I just don't need that form in the new workbook but I do need to keep all the modules. Thanks

Private Sub Workbook_Open()

    Worksheets("Application").Activate
    frmApplication.Show
End Sub

 Worksheets("Application").Cells(115, 2).Value = Me.txtfinaldate.Value
    Worksheets("Application").Cells(115, 6).Value = Me.txtsignature.Value

    
    Sheets("Application").Copy
    Application.DisplayAlerts = False
    With ActiveWorkbook
    .SaveAs Filename:="H:\All\Application\Applications Completed\" & _
    Format(Now(), "mm-dd-yyyy hh-mm-ss") & " NEW APPLICATION " & Sheets("Application").Range("C5"), FileFormat:=xlNormal
    .Close
    End With
    
    Range("A1:J1").Select
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        End If
    Next ctl
    frmApplication.TextBox1.SetFocus
    Worksheets("Application").Range("A5:B5").Select

 

[sijpie]

So when does the form need to show? Never on opening? then remove the line
frmApplication.Show
from the Workbook_Open code.
Or do you not want to show it after the file was Saved As?

That would be quite difficult.

You can do things on saving (like setting a flag in a cell that you could read when opening the file), but Windows does not differentiate between save and save as wrt to the events that are passed.

 

[bamaisgreat]

Thanks go the reply. Yes I basically have a workbook that a user form opens when the workbook opens and when the code is executed and the save as is done I want that user form not to be in the newly saved workbook Basically I don't want the user form in the new work book. I have read up on deleting code within code and it seems difficult.

 

[sijpie]

Yes that is difficult A work-around could be that you do the Save-as through a macro. So in your workbook you have a Save-as button that opens the file dialogue for the user to pick a new name. If that is successful, then before the save is done, you first set a flag in a hidden cell (for instance the cell below the button) andthen the save is done. What you need to chanfge then in he Workbook_Open macro is to check for the value in the hidden cell. If set to true the form is not shown. I use such an approach quite often.

 

[bamaisgreat]

Ok, I see what your saying. To keep it simple lets say I have the text "Form" in cell A2 . Is there some type of code I could add to the Workbook_Open to make it look for this text. If the text "Form" is in cell A2 then open Worksheets("Application").Activate frmApplication.Show if cell A2 is empty then Do Not Show the Form. Is this possible ? Im not sure how to write the code to do this. This is what I have

Private Sub Workbook_Open()
Worksheets("Application").Activate
frmApplication.Show
Endsub

 

[sijpie]

Yes,

ion the 2nd piece of code that you show above you need to make a change to clear the A2 cell

    With Worksheets("Application")
        .Cells(115, 2).Value = Me.txtfinaldate.Value
        .Cells(115, 6).Value = Me.txtsignature.Value


        .Copy   'this creates a copy in a new workbook
    End With
    
    ' Set flag to not open userform in this new workbook (workbook_open())
    Range("A2").Clear
    
    Application.DisplayAlerts = False
    With ActiveWorkbook
    
        .SaveAs Filename:="H:\All\Application\Applications Completed\" & _
                Format(Now(), "mm-dd-yyyy hh-mm-ss") & " NEW APPLICATION " & _
                Sheets("Application").Range("C5"), FileFormat:=xlNormal
        .Close
    End With
    
    Range("A1:J1").Select
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        End If
    Next ctl
    frmApplication.TextBox1.SetFocus
    Worksheets("Application").Range("A5:B5").Select

And your Workbook_open() sub becomes:

Private Sub Workbook_Open()
    Worksheets("Application").Activate
    If Range("A2").Value = "Form" Then
        frmApplication.Show
    End If
End Sub