Modules not being deleted

[Code Numpty]

I have code in 2 modules as below. In some instances the last action deleting
the modules is not happening.

*****Module 3
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False

ThisWorkBook.Activate
Sheet1.Range("quote_date") = Sheet1.Range("quote_date").Value
Sheet1.Range("qdata5,qdata6").Font.ColorIndex = 2

'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If

Sheet1.Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Sheet1.Range("content") = Sheet1.Range("content").Value

Call NoDVinputMsg

Sheet1.Shapes("Group 31").Delete
Sheet1.Rows("1:1").Delete Shift:=xlUp
Sheet1.Shapes("Picture 14").Delete
Sheet1.Range("A:G").Interior.ColorIndex = xlNone

'Desperately trying to speed up delete column E!
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Sheet1.Range("base_p").Delete Shift:=xlToLeft
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

Sheet1.Range("comm_disclines").Delete Shift:=xlUp
Sheet1.Range("boxes").Borders.LineStyle = x1None
Sheet1.Range("delterms_box").ClearContents
Sheet2.Name = "Terms&Conditions"
Sheet2.Range("instructions").Delete
Sheet2.Shapes("Picture 1").Delete
Sheet1.Range("qdata1").Select
Dim vbCom As Object

Call logquote
Application.ScreenUpdating = True

Sheet1.Range("A1:F1").HorizontalAlignment = xlCenter
Sheet1.Range("A1:F1").VerticalAlignment = xlCenter
Sheet1.Range("A1:F1").MergeCells = True


On Error Resume Next

Set vbCom = ActiveWorkbook.VBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module3")

vbCom.Remove VBComponent:= _
vbCom.Item("Module4")

On Error GoTo 0

End Sub
*****Module 4
ub NoDVinputMsg()
Dim rng As Range, cel As Range
Set rng = Nothing ' only if rng previously set
On Error Resume Next
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
If Not rng Is Nothing Then
bDummy = rng.Validation.ShowInput
If Err.Number = 0 Then
' all same type, no need to loop
With rng.Validation
..InputTitle = ""
..InputMessage = ""
End With
Else
On Error GoTo 0
For Each cel In rng
With cel.Validation
..InputTitle = ""
..InputMessage = ""
End With
Next
End If
End If
End Sub
Sub logquote()
'
' logquote Macro
' Macro recorded 15/06/2007 by Sharon
'

'
Dim ThisWorkBook As String
Dim SheetName As String
Dim MyRanges(8) As String
Dim EmptyRow As Integer
Dim a As Integer 'to cyle through ranges

ThisWorkBook = ActiveWorkbook.Name
SheetName = ActiveSheet.Name

MyRanges(1) = "qdata1"
MyRanges(2) = "qdata2"
MyRanges(3) = "qdata3"
MyRanges(4) = "qdata4"
MyRanges(5) = "qdata5"
MyRanges(6) = "qdata6"
MyRanges(7) = "qdata7"
MyRanges(8) = "qdata8"

Workbooks.Open Filename:= _
"\\Impactsrv\shared\Templates\Quotes\Quote_Log.xls"
Workbooks("Quote_Log.xls").Activate

With Workbooks("Quote_Log.xls")
.Sheets("Quotes").Activate

With ActiveSheet

'find empty row
EmptyRow = 0
Do
EmptyRow = EmptyRow + 1
Loop Until IsEmpty(.Cells(EmptyRow, 1))

.Cells(EmptyRow, 1) = Date

'fill in other columns from named ranges
For a = 1 To UBound(MyRanges)
.Cells(EmptyRow, a + 1) = _
Workbooks(ThisWorkBook).Sheets(SheetName).Range(MyRanges(a))
Next a

End With

'save and close workbook
.Save
.Close
End With

'activate back to where you started
Workbooks(ThisWorkBook).Activate


End Sub
*****

How can I tell what is causing this malfunction? I take it a warning would
show if the Trust access to the Visual Basic Project setting was not checked.

 

[Peter T]

I assume you are talking about

vbCom.Remove VBComponent:= _
vbCom.Item("Module3") ' & ("Module3")

As it stands the code looks OK but If I follow, you are deleting modules
from the activeworkbook which actually is ThisWorkbook (you activated
thisworkbook).

Depending on what you are doing overall, sometimes writing (in this case
deleting stuff) to "self" can be problematic, particularly object modules,
not that that's what you are doing here.

I'd suggest disable the error handler and try doing it like this

set vbMod = vbCom("module3)
vbCom.Remove vbMod

Regards,
Peter T

 

[Patrick Molloy]

I doubt that code running in module3 can delete module3...how would that work?

 

[Peter T]

I doubt that code running in module3 can delete module3...how would that
work?

I didn't notice that !

Peter T

 

[Code Numpty]

Thanks Peter, to clarify

What I am referring to is deleting 2 modules as below.
=======================================
On Error Resume Next

Set vbCom = ActiveWorkbook.VBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module3")

vbCom.Remove VBComponent:= _
vbCom.Item("Module4")

On Error GoTo 0
=======================================

This is in Module 3.

What I am trying to do is delete all macros so that our customers do not get
any security warnings when they open the file.

Usually it is no problem but occasionally it doesn't delete them, having
carried all other actions in both modules.

 

[Peter T]

This seems to work, even though intuitively it shouldn't (bit like putting
your car in the crusher while driving it). When almost done call
DelModsOnTime

Sub DelModsOnTime()
Application.OnTime Now, "DelMods"
End Sub

Sub DelMods()
' in Module3

Dim vbComps As Object ' VBComponents
Dim vbComp As Object ' VBComponent

Set vbComps = ThisWorkbook.VBProject.VBComponents

Set vbComp = vbComps("module4")
vbComps.Remove vbComp

' delete 'this' module last of all
Set vbComp = vbComps("module3")
vbComps.Remove vbComp

End Sub

Make sure the absolute last thing you do is delete the module with the
running code, and that the code does not want to return to some other
calling proc. Hold breath.

Regards,
Peter T

 

[Code Numpty]

Thanks Peter I'll try this. My version works for me but not for some users so
hopefully your version will work for all!

 

[Peter T]

Maybe its a different problem altogether. The users for whom it doesn't
work - have they got "Trust access to VBP" ticked in macro Security, Trusted
publishers ?

Regards,
Peter T

 

[Code Numpty]

One didn't and immediately an error message came up and I knew what it was so
that's not causing the intermittent problem.

 

[Peter T]

For that user the code would never have worked without Trust Access to VBP
allowed, irrespective of any other issues.

Regards,
Peter T

 

[Patrick Molloy]

maybe create another workbook with the 'delete' code and run it against the
workbook that you want cleaned?