Kill a Sub using VBA

[MrsTFB]

I have an open event in an Excel workbook that after Saving the workbook as a new name, I want to disable the sub. The disable would occur with a click event on a button on the SignIN Form. Is this possible?

Here's the code:

Private Sub Workbook_Open()

Application.ScreenUpdating = False


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


ActiveWorkbook.Save

Load SignIn
SignIn.Show



End Sub

 

[SkipVought]

Hi,

Disable WHAT sub? What do you not want to happen?

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[fumei]

Yes, You have an Open Sub that runs then disables.......????

Gerry

 

[HughLerwill]

MrsTFB,

Disable or destroy? To do the latter place all the code you want to destroy in a module and after running it do...

Application.DisplayAlerts = False 'supress the warning message
WB.Sheets("ModuleName").Delete
Application.DisplayAlerts = True

the code can appear in the module being deleted (suggest very late though!)

Deleting the module has the advantage 1) the code is hidden from recipients 2) the final file is smaller.

regards Hugh,

 

[BrainB]

Perhaps something like this. The first run changes the cell and locks it. Subsequently when the workbook is opened an error is produced which skips the lines of code. The other cells in the sheet need to be unlocked so the user can make changes.

Private Sub Workbook_Open()
    Dim MyCell As Range
    '-------------------
    On Error GoTo GetOut
    Application.ScreenUpdating = False
    Set MyCell = Worksheets("DCNTemp").Range("AZ2")
    MyCell.Value = MyCell.Value + 1 ' produces an error if the cell is locked & protected
    MyCell.Locked = True
    Worksheets("DCNTemp").Protect
    ActiveWorkbook.Save
    Worksheets("DCNTemp").Range("a1").Select
GetOut:
      Load SignIn
      SignIn.Show
End Sub

Regards
BrianB
Use CupOfCoffee to speed up all windows applications.
It is easy until you know how.
================================

 

[MrsTFB]

This is found in the This Workbook section of the project, and it is a Workbook_Open sub. I just want to delete it after the workbook has been saved as a new name, which occurs in the SIgnIn form that the sub initiates.

SOrry to be so unclear. I will try this code after the file has been renamed. That way my original file will always have the Workbook_Open sub, which is what I want.

Application.DisplayAlerts = False 'supress the warning message
WB.Sheets("ModuleName").Delete
Application.DisplayAlerts = True


Thanks for your help. I'll test and return.

MrsTFB in Tennessee

 

[MrsTFB]

Nope, I thought that would work, but it wants an object required. I'm betting that I have to give it a more direct address of the module, since I'm deleting it from a form and the module is in the Workbook properties. I'm not very proficient at VBA, just a rookie.

Thanks for any help!

MrsTFB

 

[HughLerwill]

How about just Sheets("ModuleName").Delete

You must place the actual ModuleName within the quotes.

regards Hugh,

 

[MrsTFB]

Nope, same "Object Required". All I really care about is that the SignIn form is removed from the module. If it incremented the number and saved the workbook wouldn't matter. Just don't load and display the form. Would that be easier?

MrsTFB

 

[HughLerwill]

Try;

ActiveWorkBook.Sheets("ModuleName").Delete

Are you sure the ModuleName you are specifying is correct.

You say the code is currently located on a form (UserForm?) try moving it to the end of the code you want to remove. So say your code is in MySub in Module1

Sub MySub

'do stuff

ActiveWorkBook.Sheets("Module1").Delete

End Sub
Thus the sub runs once and then deletes the module it is in.

If that dos'nt work please post the actual code you are using and tell us exactly where it is.

regards Hugh

 

[MrsTFB]

It's housed in ThisWorkbook object and this is the exact code. It's not actually in a Module per se. When I moved it into a module, the event did not occur.

Private Sub Workbook_Open()

Application.ScreenUpdating = False


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


ActiveWorkbook.Save

Load SignIn
SignIn.Show



End Sub

 

[MrsTFB]

I fixed it another way. In the code, right after the SaveAs ("anothername") I set range ("az243") to equal 1. Then in the AutoOpen code, I put the if statement that if that range = 1, then exit sub.

It's not pretty, but it works.

Thanks for your input.

MrsTFB in Tennessee