Email Excel without Macro

johnou

New Member
Joined
Feb 27, 2002
Messages
4
I would like to send out the current Excel file without the VBA coding because it contains system password etc. Is that any way I can do that?

Thanks,

John
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
HI--

I do not know of such a way, password portect the modules if macros there, but thats no very safe for advanced users, bit like car raido and wondow left open!

or copy the sheet to new work book and mail that so VBA will not be attached

This assumes the VBA is not needed of cause
 
Upvote 0
On 2002-03-03 06:24, johnou wrote:
I would like to send out the current Excel file without the VBA coding because it contains system password etc. Is that any way I can do that?

Thanks,

John

Hi John, if you want to remove all code from a workbook then you try this.

Run this routine with the workbook that
you want to delete code from open... don't
worry if you have other workbooks open
it will prompt you to OK the deletion.

You don't have to referene the Libray files
This is automatically taken care of.

Option Explicit

'*****************************************************************************
'* This procedure removes all code and related structures from a workbook.
'*
'* Reference to the VB Extensibility library is Automatically set when
'* 1st run via the GUID routine.
'*
'* Removes from Workbooks all:
'* Regular modules = vbext_ct_StdModule
'* Class modules = vbext_ct_ClassModule
'* Userforms = vbext_ct_MSForm
'* Code in sheet and workbook modules = vbext_ct_Document
'* Non built-in references
'* Excel 4 macro sheets
'* Dialog sheets
'*
''

Sub RunThisFirst()
On Error Resume Next 'if it already exits
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 0
TellUser
RemoveAllCode
End Sub

Sub RemoveAllCode()
'XL2K:
'Dim VBComp As VBComponent, AllComp As VBComponents, ThisProj As VBProject
'XL97 & XL2K:
Dim VBComp As Object, AllComp As Object, ThisProj As Object
Dim ThisRef As Reference, WSht As Worksheet, Dlg As DialogSheet
Dim RemoveOK As Integer, Wbk As Workbook

For Each Wbk In Workbooks
If Wbk.Name <> ThisWorkbook.Name Then
'Ask the User Just in case!
RemoveOK = MsgBox("Remove All code from:= " & Wbk.Name & "?", vbYesNo)
If RemoveOK = vbNo Then GoTo Nxt
Set ThisProj = Wbk.VBProject
Set AllComp = ThisProj.VBComponents

For Each VBComp In AllComp
With VBComp
Select Case .Type
Case 1, 2, 3
'remove modules,Classes & Forms
AllComp.Remove VBComp
Case 100
'Remove Event codes
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End Select
End With
Next
'remove References to other projects
For Each ThisRef In ThisProj.References
If Not ThisRef.BuiltIn Then ThisProj.References.Remove ThisRef
Next
End If

Set ThisProj = Nothing
Set AllComp = Nothing

Application.DisplayAlerts = False

'Remove Excel4 Macrosheets
For Each WSht In Wbk.Excel4MacroSheets
WSht.Delete
Next

'remove Dialog sheets-note dialogstypically hidden
For Each Dlg In Wbk.DialogSheets
Dlg.Delete
Next

Application.DisplayAlerts = True

Nxt: Next

MsgBox "Done!" & Space(20), vbInformation + vbSystemModal

End Sub

Sub TellUser()
Dim msg As String
Dim Proceed As Integer

msg = "This routine will delete ALL Code" & vbCr
msg = msg & "from ALL currently open workbooks!" & vbCr
msg = msg & "If you do not want to delete them then" & vbCr
msg = msg & "answer NO at the prompt to delete the code." & vbCr & vbCr
msg = msg & "To start this routine click YES" & vbCr
msg = msg & "To cancel this routine Click NO" & vbCr

Proceed = MsgBox(msg, vbInformation + vbYesNo, "Proceed")
If Proceed = vbNo Then End
End Sub

HTH

Ivan
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top