View Full Version : Solved: Delete a Sub Not needed anymore
ioncila
05-17-2010, 03:52 AM
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:
Sub DeleteModule()
Dim VBComp As Object
Set VBComp = ActiveWorkbook.VBProject. _
VBComponents("Module11")
ActiveWorkbook.VBProject.VBComponents.Remove VBComp
End Sub
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.
Private Sub Workbook_Open()
Worksheets("EnterOption").Activate
'This code goes in your ThisWorkbook Module
Call HideShowSheets("EnterOption", True)
End Sub
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
05-17-2010, 04:05 AM
Can I ask why you're removing the module?
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
05-17-2010, 04:24 AM
Adittedly a total guess at this point,
Your right there Mark, its a total guess at this point!! :devil2:
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...:dunno
Aussiebear
05-17-2010, 05:15 AM
Don't worry you are still up there with the best ( in my books)
ioncila
05-17-2010, 07:53 AM
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
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
In another module
Sub DeleteModule()
Dim VBComp As Object
Set VBComp = ActiveWorkbook.VBProject. _
VBComponents("Module11")
ActiveWorkbook.VBProject.VBComponents.Remove VBComp
End Sub
In "EnterOption" sheet
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
And in ThisWorkbook Module
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
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
05-17-2010, 09:30 AM
One little question: I've tried the approach below in ThisWorkbook Module but still gives the same error
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
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.
Private Sub Workbook_Open()
If ShExists("EnterOption") Then
Worksheets("EnterOption").Activate
Call HideShowSheets("EnterOption", True)
End If
End Sub
Have a great day,
Mark
ioncila
05-17-2010, 02:26 PM
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.
Call HideShowSheets("Entrada", True)
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
Dear Ioncila,
Thank you for posting back. I assumed (silly on me) that it was because 'Entrada' had been deleted. Well done!
Mark
mikerickson
05-17-2010, 09:15 PM
Rather than deleting the sub, why not remove all the lines from it, so it does nothing.
So
Sub HideShowSheets(someString as String, someBool as Boolean)
'lots of lines
End Sub
becomes
Sub HideShowSheets(someString as String, someBool as Boolean)
End Sub
That way the calling lines will compile.
ioncila
05-18-2010, 11:09 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.