Deleting a module

[Francis Hookham]

Deleting a module
Having Saved As... (thanks to Bernard Rey and J M McGimpsey) can ŒUserForm1¹
and ŒModule1¹ be deleted so the file can be sent as a straight forward sheet
without potentially suspicious macros?

The line
ActiveSheet.Shapes("RunButton").Cut
removes a button which calls the UserForm but is it possible to go further?
I guess not but it would be great if possible

Sub SaveAs()
ActiveWorkbook.SaveAs Filename:=Range("C4") & " " & Range("A3") & _
" " & Format(Date, "d mmm yy")
ActiveSheet.Shapes("RunButton").Cut

End Sub

Francis Hookham

 

[Bernard Rey]

These lines, slightly adapted from Laurent Longre, should do that:


Sub DeleteAllVBA()
'by L Longre, mpfe
Dim VBC As Object
With ActiveWorkbook.VBProject
For Each VBC In .VBComponents
If VBC.Type = 100 Then
With VBC.CodeModule
..DeleteLines 1, .CountOfLines
..CodePane.Window.Close
End With
Else: .VBComponents.Remove VBC
End If
Next VBC
End With
MsgBox "Modules & macros of the current Workbook have been deleted.", _
vbInformation
End Sub




Francis Hookham:

 

[Francis Hookham]

Many thanks Bernard BUT I'm way behind you

The original single sheet workbook is named:
Drawing Register.xls
and the Saved AsŠ workbook typically has name:
1627 NEEPSEND RIVERSIDE 11 Oct 05.xls

The programme hangs at the line:
For Each VBC In .VBComponents

I show below that subroutines concerned - SaveIssueSheet is called by a
button in UserForm1

(The workbook is a Drawing Register for my architect son where he wants to
save a new file of each issue of drawings and who sent to, separate from
the original workbook which has all issues and all recipients) (I have
started masking copious comments in the hope that my son will be able to
understand how the macros work and maybe one day find time to start writing
them himself)

Can you help please?

Francis Hookham
_____________________________________________

Sub SaveIssueSheet()
'This to save a separate sheet with the job name, number
'and today's date

'open everything up
ViewAll 'calls a macro of that name

'save the original sheet
ActiveWorkbook.Save

'close rows and columns to issue recipients/drawings only
'CloseToIssuePrints 'calls a macro of that name (not written yet)

'Save As... naming new file with name/number and date
SaveAs 'calls a macro of that name

'That removes the buttons and the underlying VBA
DeleteAllVBA 'calls a macro of that name
End Sub

Sub SaveAs()
'save the sheet under another name. job number/name and date
ActiveWorkbook.SaveAs Filename:=Range("C4") & " " & Range("A3") & _
" " & Format(Date, "d mmm yy")
End Sub

Sub CloseToIssuePrints()
SetVariables
IssueColumn = Cells(3, Columns.Count).End(xlToLeft).Column
EndRow = Cells(Rows.Count, 3).End(xlUp).Row
StartRow = DrawingHeaderRow + 1
Cells(1, 1) = EndRow
End Sub

Sub DeleteAllVBA()
'by L Longre, mpfe, via Bernard Rey, MVP, 12/10/05
Dim VBC As Object
With ActiveWorkbook
For Each VBC In .VBComponents
If VBC.Type = 100 Then
With VBC.CodeModule
.DeleteLines 1, .CountOfLines
.CodePane.Window.Close
End With
Else: .VBComponents.Remove VBC
End If
Next VBC
End With
MsgBox "Modules & macros of the current Workbook have been deleted.", _
vbInformation
End Sub

 

[Bernard Rey]

Sorry for this late reply, I haven't been around for a while.

Maybe you have already found out what's going wrong. But just in case:

In your version you made (unwillingly, I guess) a major change. You
shortened this line:

With ActiveWorkbook.VBProject

to a "hanging":

With ActiveWorkbook

Simply adding the ".VBproject" part to your existing project will make
things work right.

In addition to the existing I'd suggest to add a:

Application.ScreenUpdating = False

At the beginning of the macro (and the opposite instruction later if you
want to see the warning. So that macro could be:

--------------------------------------------------

Sub DeleteAllVBA()
'by L Longre, MVP, via Bernard Rey, MVP, 12/10/05
Dim VBC As Object
Application.ScreenUpdating = False ' to avoid the flickering screens
With ActiveWorkbook.VBProject
For Each VBC In .VBComponents
If VBC.Type = 100 Then
With VBC.CodeModule
.DeleteLines 1, .CountOfLines
.CodePane.Window.Close
End With
Else: .VBComponents.Remove VBC
End If
Next VBC
End With
Application.ScreenUpdating = True ' so you can see the message
MsgBox "Modules & macros of the current Workbook have been deleted.", _
vbInformation
End Sub

-------------------------------------------------

Is it better this way?




Francis Hookham a écrit :

 

[Francis Hookham]

Oh dear!
'Runtime error "1004":
Programactic access to Visual Basic is not trusted'

Any suggestions?

Another issue - can the SaveAs file be files in the same folder as the
original rather than in the documents folder?

Francis Hookham
_______________________________________________________

 

[Bernard Rey]

Francis Hookham:

Oh dear!
'Runtime error "1004":
Programactic access to Visual Basic is not trusted'

Any suggestions?

Nope. Start form the beginning, check through, step by step, etc. Your code
was running here (I only had to add the missing ".VBProject") and I think
there is no reason why it won't run on your machine.

Just make sure there are no "invalid" lines (they appear in red) this
sometimes happen when pasting lines from an email. Deleting the blank space
on the beginning of lines usually helps in that case.

Another issue - can the SaveAs file be files in the same folder as the
original rather than in the documents folder?

Yes, of course. There are different ways one is to get the path first, and
then indicate the path when saving. Something like this:

In your main macro, add a line to get the path and then pass it to the
"SaveAs" macro:

Sub SaveIssueSheet()
'.../...
'save the original sheet
ActiveWorkbook.Save
MyPath = ActiveWorkbook.Path & Application.PathSeparator

'.../...
'Save As... naming new file with name/number and date
SaveAs (MyPath) 'calls a macro of that name

'.../...
End Sub
Then modify the "SaveAs" macro in order to use the current path:

Sub SaveAs(ByVal MyPath As String)
'save the sheet under another name. job number/name and date
ActiveWorkbook.SaveAs Filename:=MyPath & Range("C4") & " " & _
Range("A3") & " " & Format(Date, "d mmm yy")
End Sub

With these modifications, the code still runs fine here.

 

[Francis Hookham]

Bernard - apologies for coming back but I am at a loss to understand why
it stops each time at

With ActiveWorkbook.VBProject

I hesitate to suggest send in the workbook direct for you to look at but
I shall otherwise have to scrap the idea of removing the VBA which does
not really matter, having deleted the button which calls the UserForm so
the macros would not be easily run

Francis Hookham

 

[Bernard Rey]

Francis Hookham a écrit :

Bernard - apologies for coming back but I am at a loss to understand why
it stops each time at

With ActiveWorkbook.VBProject

I hesitate to suggest send in the workbook direct for you to look at but
I shall otherwise have to scrap the idea of removing the VBA which does
not really matter, having deleted the button which calls the UserForm so
the macros would not be easily run

OK, no problem, send you Workbook over. There must be a reason why it does
run on my machine and not on yours, let's find out

Don't forget to remove the "DontSpamMe." part in my address...


---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

--
Bernard Rey - Toulouse / France
MVP - office:macintosh http://faq.office.macintosh.free.fr