Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting mailed worksheet
Is there a way to do this with code to lock the sheet that is create here which is to e-mailed in this macro? I know I van protect th original however I would like the e-mailed copy only protected. PROTECTION SOMEWHERE HE Sub Mail_Range() Dim source As Range Dim dest As Workbook Dim strdate As String Set source = Nothing On Error Resume Next Set source = Range("A1:J100").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If source Is Nothing Then MsgBox "The source is not a range or the sheet is protect, pleas correct and try again.", vbOKOnly Exit Sub End If Application.ScreenUpdating = False Set dest = Workbooks.Add(xlWBATWorksheet) source.Copy With dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 ' Paste:=8 will copy the column width in Excel 2000 and higher ' If you use Excel 97 use the other example .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False End With strdate = Format(Now, "dd-mm-yy h-mm-ss") With dest .SaveAs "Selection of " & ThisWorkbook.Name _ & " " & strdate & ".xls" .SendMail "", "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Su -- parteegolfe ----------------------------------------------------------------------- parteegolfer's Profile: http://www.excelforum.com/member.php...fo&userid=3195 View this thread: http://www.excelforum.com/showthread.php?threadid=52399 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting mailed worksheet
hi parteegolfer
Before you save it protect the worksheet With dest ..Sheets(1).Protect "Ron" ..SaveAs "Selection of " & ThisWorkbook.Name _ & " " & strdate & ".xls" -- Regards Ron de Bruin http://www.rondebruin.nl "parteegolfer" wrote in message news:parteegolfer.24wawa_1142735701.1207@excelforu m-nospam.com... Is there a way to do this with code to lock the sheet that is created here which is to e-mailed in this macro? I know I van protect the original however I would like the e-mailed copy only protected. PROTECTION SOMEWHERE HE Sub Mail_Range() Dim source As Range Dim dest As Workbook Dim strdate As String Set source = Nothing On Error Resume Next Set source = Range("A1:J100").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If source Is Nothing Then MsgBox "The source is not a range or the sheet is protect, please correct and try again.", vbOKOnly Exit Sub End If Application.ScreenUpdating = False Set dest = Workbooks.Add(xlWBATWorksheet) source.Copy With dest.Sheets(1) Cells(1).PasteSpecial Paste:=8 ' Paste:=8 will copy the column width in Excel 2000 and higher ' If you use Excel 97 use the other example Cells(1).PasteSpecial xlPasteValues, , False, False Cells(1).PasteSpecial xlPasteFormats, , False, False Cells(1).Select Application.CutCopyMode = False End With strdate = Format(Now, "dd-mm-yy h-mm-ss") With dest SaveAs "Selection of " & ThisWorkbook.Name _ & " " & strdate & ".xls" SendMail "", "This is the Subject line" ChangeFileAccess xlReadOnly Kill .FullName Close False End With Application.ScreenUpdating = True End Sub -- parteegolfer ------------------------------------------------------------------------ parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951 View this thread: http://www.excelforum.com/showthread...hreadid=523999 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting mailed worksheet
Thanks Ron, This worked out well, I was putting the code in the wrong place but did have the right idea. I was placing the code higher in th application. Thanks Again -- parteegolfe ----------------------------------------------------------------------- parteegolfer's Profile: http://www.excelforum.com/member.php...fo&userid=3195 View this thread: http://www.excelforum.com/showthread.php?threadid=52399 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting worksheet | Excel Discussion (Misc queries) | |||
E-mailed worksheet reverts date | Excel Discussion (Misc queries) | |||
Un-Protecting a Worksheet | Excel Discussion (Misc queries) | |||
protecting the worksheet | Excel Worksheet Functions | |||
Protecting Worksheet | Excel Discussion (Misc queries) |