Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modules not being deleted
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" wrote: 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" wrote in message ... 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" wrote: 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 "Code Numpty" wrote in message ... 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(xlCellTypeBl anks).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(xlCellTypeAllVa lidation) 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(My Ranges(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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modules not being deleted
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 "Code Numpty" wrote in message ... 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(xlCellTypeBl anks).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(xlCellTypeAllVa lidation) 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(My Ranges(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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modules not being deleted
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modules not being deleted
For that user the code would never have worked without Trust Access to VBP
allowed, irrespective of any other issues. Regards, Peter T "Code Numpty" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleted text character returning numbers when deleted. | New Users to Excel | |||
If No is deleted in middle of sequence,Nos alter after deleted No | New Users to Excel | |||
Public, Private, Event modules, Forms modules,,, | Excel Programming | |||
Basic question - modules and class modules - what's the difference? | Excel Programming | |||
When to code in sheet or userform modules and when to use modules | Excel Programming |