Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
This has me stumped!
I have this macro to e-mail a sheet when a toolbar button is selected. can not seem to protect the sheet that is created from the code I hav below. Can anyone help with this problem, I am stuck! 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) ActiveSheet.Protect ("My PASSWORD") ' password protection t sheet(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=52407 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
This has me stumped!
It appears to me that perhaps this is the problem:
With dest.Sheets(1) ActiveSheet.Protect ("My PASSWORD") You are protecting the active sheet, not dest.Sheets(1) To protect dest.Sheets(1) it would be With dest.Sheets(1) .Protect ("My PASSWORD") -- - K Dales "parteegolfer" wrote: I have this macro to e-mail a sheet when a toolbar button is selected. I can not seem to protect the sheet that is created from the code I have below. Can anyone help with this problem, I am stuck! 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) ActiveSheet.Protect ("My PASSWORD") ' password protection to sheet(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=524077 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This has me stumped | Excel Discussion (Misc queries) | |||
Stumped | Excel Discussion (Misc queries) | |||
Stumped | Excel Discussion (Misc queries) | |||
stumped ?? | Excel Worksheet Functions | |||
Stumped Again | Excel Programming |