Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA error on protected worksheet
I used the following macro in a workbook (which is attached to a comman
button) to make a copy of the active sheet and e-mail it to co-worker. My problem is that when I protect the worksheet the macr no longer works. Is there some way to add the instructions to th macro to unprotect the worksheet, run the macro, and then re-protec the worksheet?? Private Sub CommandButton1_Click() Sub Mail_ActiveSheet_Body() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = " .CC = "" .BCC = "" .Subject = "Client Change of Address" .HTMLBody = SheetToHTML(ActiveSheet) .Send 'or use .Display End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub Public Function SheetToHTML(sh As Worksheet) 'Function from Dick Kusleika his site 'http://www.dicks-clicks.com/excel/sheettohtml.htm 'Changed by Ron de Bruin 04-Nov-2003 Dim TempFile As String Dim Nwb As Workbook Dim myshape As Shape Dim fso As Object Dim ts As Object sh.Copy Set Nwb = ActiveWorkbook For Each myshape In Nwb.Sheets(1).Shapes myshape.Delete Next TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss" & ".htm" Nwb.SaveAs TempFile, xlHtml Nwb.Close False Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) SheetToHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Set Nwb = Nothing Kill TempFile End Function Any help would be greatly appreciated!! Susan M -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA error on protected worksheet
I'm a VBA beginner, however I have fixed a similar problem with the
following. Sub Your_Procedure ActiveWorkbook.Sheets("Your sheet name").Unprotect (YourPassword) YourCode ActiveWorkbook.Sheets("Your sheet name").Protect (YourPassword) End Sub --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit text format in non-protected cells in protected worksheet | Excel Discussion (Misc queries) | |||
Macro error when worksheet is protected | Excel Discussion (Misc queries) | |||
error accessing a protected worksheet | Excel Worksheet Functions | |||
Excel error message in protected sheets | Excel Programming | |||
Protected cell error - Excel XP & Win98 | Excel Programming |