Solved: Delete a Sub Not needed anymore

[ioncila]

Hi there

I need some help to this, please:

I have this workbook which is a master file and everytime I create a new file from, it deletes automatically the first sheet ("EnterOption") and a certain module ("Module11"). This works very fine.

For Module deletion I have this sub in another module:
[vba]Sub DeleteModule()
Dim VBComp As Object
Set VBComp = ActiveWorkbook.VBProject. _
VBComponents("Module11")
ActiveWorkbook.VBProject.VBComponents.Remove VBComp
End Sub[/vba]
However, when I open the new file, it gives a "Compile Error: Sub or function not defined" in ThisWorkbook Module.

I think this happens because in ThisWorkbook Module remains some references to the deleted sheet and module.

[vba]Private Sub Workbook_Open()
Worksheets("EnterOption").Activate

'This code goes in your ThisWorkbook Module
Call HideShowSheets("EnterOption", True)
End Sub[/vba]
So this sub is no longer needed. May I delete it in the same way I delete "Module11"? How do I pass over this issue?

Thanks
Ioncila

[Jan Karel Pieterse]

Can I ask why you're removing the module?

[GTO]

What is in 'Module11'?

Adittedly a total guess at this point, but if 'Module11's' code is all aimed at the 'EnterOption' sheet, I would look at moving the code to the sheet's module. That way the code goes bye-bye with the sheet.

As to the ThisWorkbook code, I would probably just wrap the code in an IF test.

Option Explicit
    
Sub exa()
    
    If ShExists("Sheet3") Then
        MsgBox "Your code here..."
    End If
    
End Sub
Function ShExists(ShName As String, _
                  Optional WB As Workbook, _
                  Optional CheckCase As Boolean = False) As Boolean
    
    If WB Is Nothing Then
        Set WB = ThisWorkbook
    End If
    
    If CheckCase Then
        On Error Resume Next
        ShExists = CBool(WB.Worksheets(ShName).Name = ShName)
        On Error GoTo 0
    Else
        On Error Resume Next
        ShExists = CBool(UCase(WB.Worksheets(ShName).Name) = UCase(ShName))
        On Error GoTo 0
    End If
End Function

Hope that helps,

Mark

[Aussiebear]

Adittedly a total guess at this point,

Your right there Mark, its a total guess at this point!!

[GTO]

Yes, on top of which I would probably have waited for Jan's question to be answered. Oh well, 'slow-hand' and can't even play the guitar...

[Aussiebear]

Don't worry you are still up there with the best ( in my books)

[ioncila]

Can I ask why you're removing the module?

Adittedly a total guess at this point

This may be a large post, but since I apperently figured that my question was misunterstood, here's a better explanation:

Master file has a sheet named "EnterOption" with 4 buttons: Create New File, Go to Arqhive, Cancel/Close and Update.

First option will create a new file with a certain group of sheets. So when new file is created, I need to remove "EnterOption" sheet and code that supports the other options:

MODULE 11
[VBA]Sub CreateNewFile() 'Create New File

Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFileName As String
Dim NewFile As String

Application.ScreenUpdating = False 'Prevents screen refreshing.

'Save File Path
CurrentFile = ThisWorkbook.FullName

NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _
"Excel Files 2007 (*.xlsx), *.xlsx," & _
"All files (*.*), *.*"

'Open SaveAs Window
NewFile = Application.GetSaveAsFilename( _
InitialFileName:=NewFileName, _
fileFilter:=NewFileType)

'SaveAs New File
If NewFile <> "" And NewFile <> "False" Then
ActiveWorkbook.SaveAs Filename:=NewFile, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

'Insert New Sheet to prevent an error in new file
Dim MyNewSheet As Worksheet
Set MyNewSheet = Sheets.Add
MyNewSheet.Name = "EliminarEstaFolha"

'Delete "EnterOption" from the new file
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name = "Entrada" Then ws.Delete
Next
End If
'Delete Module 11 from the new file
'have to go to Tools>Macro>Security - Trusted Publishers
'and check Trust access to Visual Basic Editor before running the code
'Thanks http://www.ozgrid.com/VBA/delete-module.htm
DeleteModule
'Dim vbCom As Object
'Set vbCom = Application.VBE.ActiveWorkbook.VBProject.VBComponents
'vbCom.Remove VBComponent:=vbCom.Item("Module11")
'Go to a specific sheet específico
Dim sh As Worksheet
For Each sh In Sheets
sh.Visible = True
Next
Application.GoTo Reference:=Worksheets("MQT").Range("A1"), Scroll:=True

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Sub GoToArquivo() 'Go To Archive Option
ActiveWorkbook.FollowHyperlink "C:\Testes"
ActiveWorkbook.Close SaveChanges:=False
End Sub

Sub CloseFile() 'Cancel/Close
ActiveWorkbook.Close SaveChanges:=False
End Sub

Sub HideShowSheets(sExceptionSheet As String, bHide As Boolean)
Dim iX As Integer

For iX = 1 To ThisWorkbook.Sheets.Count
Sheets(iX).Activate
If ActiveSheet.Name <> sExceptionSheet Then
If bHide = True Then
Application.ActiveSheet.Visible = False
Else
Application.ActiveSheet.Visible = True
End If
End If
Next iX

End Sub
[/VBA]

In another module

[VBA]Sub DeleteModule()
Dim VBComp As Object
Set VBComp = ActiveWorkbook.VBProject. _
VBComponents("Module11")
ActiveWorkbook.VBProject.VBComponents.Remove VBComp
End Sub[/VBA]

In "EnterOption" sheet

[VBA]Option Explicit
Private Sub Worksheet_Activate()
ActiveSheet.ScrollArea = "A1:L40"
End Sub

'This code goes in Sheet1, change the name to match your command buttons name.

Sub CaixaSenha_Click()
Dim sPassword As String
Dim sh As Worksheet

sPassword = InputBox("Enter Your Password")
If sPassword = "mypassword" Then


For Each sh In Sheets
sh.Visible = True
Next

Else
MsgBox "Password incorrect!"
Exit Sub
End If
End Sub[/VBA]

And in ThisWorkbook Module

[VBA]Option Explicit
Private Sub Workbook_Open()
Worksheets("EnterOption").Activate
'This code goes in your ThisWorkbook Module
'Private Sub Workbook_Open()
Call HideShowSheets("EnterOption", True)
End Sub[/VBA]

I know these codes appears like collages and may there be a better way to write that, but myknowledge of VBA is short (too short).
Anyways, this works very fine until I open the new file.

Now I'm going to try Mark's suggestion.

Thanks.

[ioncila]

One little question: I've tried the approach below in ThisWorkbook Module but still gives the same error

[VBA]Option Explicit

Private Sub Workbook_Open()
Worksheets("EnterOption").Activate

If ShExists("EnterOption") Then
Call HideShowSheets("EnterOption", True)
End If
End Sub
Function ShExists(ShName As String, _
Optional WB As Workbook, _
Optional CheckCase As Boolean = False) As Boolean
If WB Is Nothing Then
Set WB = ThisWorkbook
End If

If CheckCase Then
On Error Resume Next
ShExists = CBool(WB.Worksheets(ShName).Name = ShName)
On Error GoTo 0
Else
On Error Resume Next
ShExists = CBool(UCase(WB.Worksheets(ShName).Name) = UCase(ShName))
On Error GoTo 0
End If
End Function[/VBA]

[GTO]

Hi there :-)

You are doing what I suppose we all do from time-to-time; rushing a bit and not taking time to think it through.

How can we Activate a sheet that doesn't exist? Of course we cannot, so just move that line to within the IF test.
[vba]
Private Sub Workbook_Open()

If ShExists("EnterOption") Then
Worksheets("EnterOption").Activate
Call HideShowSheets("EnterOption", True)
End If
End Sub
[/vba]

Have a great day,

Mark

[ioncila]

You are doing what I suppose we all do from time-to-time; rushing a bit and not taking time to think it through.

How can we Activate a sheet that doesn't exist? Of course we cannot, so just move that line to within the IF test.

Yes. That's the reason (stop to think and look again).
I followed your suggestion of code, and still the same error.
So I stopped and took a little time doing other things. And after a while I just realized that I was looking at the wrong error line.

[VBA]Call HideShowSheets("Entrada", True)[/VBA]
This line is and always has been my problem.

Why? Because the relative sub is in the deleted module.
So I cut it and copied to another module and voilá:
Solved my problem.

But I know that wouldn't be possible if I wasn't talking to you.
Thank you very much for the light and for another step in my VBA learning stair.

Ioncila

[GTO]

Dear Ioncila,

Thank you for posting back. I assumed (silly on me) that it was because 'Entrada' had been deleted. Well done!

Mark

[mikerickson]

Rather than deleting the sub, why not remove all the lines from it, so it does nothing.

So
[VBA]Sub HideShowSheets(someString as String, someBool as Boolean)
'lots of lines
End Sub[/VBA]
becomes
[VBA]Sub HideShowSheets(someString as String, someBool as Boolean)

End Sub[/VBA]
That way the calling lines will compile.

[ioncila]

Rather than deleting the sub, why not remove all the lines from it, so it does nothing.

Thanks for the tip. Code works much better.

Ioncila