PDA

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?

GTO
05-17-2010, 04:08 AM
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:

GTO
05-17-2010, 04:31 AM
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

GTO
05-17-2010, 10:05 AM
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

GTO
05-17-2010, 02:32 PM
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