Disable a open event

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
831
Office Version
  1. 365
Platform
  1. Windows
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
Code:
Private Sub Workbook_Open()

    Worksheets("Application").Activate
    frmApplication.Show
End Sub
Code:
 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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Yes,

ion the 2nd piece of code that you show above you need to make a change to clear the A2 cell
Code:
    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:
Code:
Private Sub Workbook_Open()
    Worksheets("Application").Activate
    If Range("A2").Value = "Form" Then
        frmApplication.Show
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top