Remove Workbook Open Event after initial open

[MrsTFB]

I have a workbook that has a simple workbook open event (below). Later, I save the workbook with another name (unique to creator) and would like the workbook_Open event to be removed. I don't want it to happen anymore after the new save. Can I do that programmatically? I'm very basic to this stuff, so be simple with me please. Thanks!

MrsTFB in Tennessee

Private Sub Workbook_Open()

Application.ScreenUpdating = False

Range("AZ2").Select
ActiveCell.Value = ActiveCell.Value + 1

ActiveWorkbook.Save

Load SignIn
SignIn.Show

End Sub

 

[SkipVought]

Hi,

How bout this (with your unique workbook name"...

Private Sub Workbook_Open()
   if ThisWorkbook.Name = [b]"YourUNIQUEWorkbookName.xls"[/b] then
     Application.ScreenUpdating = False
   
      Range("AZ2").Select
      ActiveCell.Value = ActiveCell.Value + 1
    
      ActiveWorkbook.Save
    
      Load SignIn
      SignIn.Show
    end if 
End Sub

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[SkipVought]

Mrs T,

How bout this - (using your unique workbook name)

Private Sub Workbook_Open()
  if ThisWorkbook.Name = "MyUNIQUE WorkbookName.xls" then   
    Application.ScreenUpdating = False
   
    with Range("AZ2")
       .Value = .Value + 1
    end with
    
    ThisWorkbook.Save
    
      Load SignIn
      SignIn.Show
   end if  
End Sub

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[MrsTFB]

Well, I've totally messed up the entire workbook now. When I try to open it, I get a Microsoft Visual Basic Error "Errors occurred during load" and only an OK and Help button. The Help tells you to check the error log for more information. A second box occurs after OK, also Microsoft Visual Basic error with large red X "Can't load or unload this object." Then the filename is in the title bar, but no code in VBA so that I can change what I've messed up.

Any ideas here? I've made several changes today, so I would hate to restore from last nights back up, but that is an option.

Thanks in advance,
Beth

 

[PHV]

You may add this line before the ThisWorkbook.Save
Application.ScreenUpdating = False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Can you load without enabling macros?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[MrsTFB]

Nope! Even with Diasable Macros, same error. What could I have done?? I hadn't added the If statement yet!

Beth

 

[SkipVought]

Restore a previous day's version of this workbook. I have had to do that on occasion.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[MrsTFB]

The Filename is no longer in the title bar, and I'm using Excel 2000, if that helps anything. Thanks for any ideas.

Beth