Solved: *Automatically Unprotect & Protect sheet - Macro command button

[jenroyce]

Hi,

I have created a button that will automatically send a range of cell thru Microsoft Outlook, thing is i wanted to protect the formulas and the VBA code. When I manually protect it (ALT+T+P+P then password), then click the button its telling me that its password protected (see attached file).

I would like to get some help on the codes to:
1. automatically unprotect the sheets and protect them again after the email has been sent

2. for code *.Item.Subject = "End of Day prod" to include today's date after the word "prod"

3. Create another button to be able to Move and Copy Sheet 1's values and format without formulas

Really really appreciate it!

[Bob Phillips]

Just unprotect it first

[vba]
Activesheet.Unprotect
[/vba]

then protect it afterwards
[vba]
Activesheet.Protect
[/vba]

[jenroyce]

Thank you for your quick reply

As i am new to VBA, i would like to request to kindly guide me how should i do it? maybe post the complete code here or incorporate it to the file i attached? pwease?

[jenroyce]

When i tried doing it myself with this code:
[VBA]Sub Send_Range()
ActiveSheet.Unprotect "password"
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:Q29").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True
' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "Team Production Today"
.Item.To = "E-Mail_Address_Here"
.Item.Subject = "End of Day prod [Date of the Report]"
.Item.Send
ActiveSheet.Protect "password"
End With
End Sub

[/VBA]

Im getting a Run-time error '1004': Application-defined or object defined error.

Please help

[GTO]

This seemed to work for me. Double check your password. Note that I tacked on the current date that you asked about.
[vba]Sub Send_Range()
ActiveSheet.Unprotect "test"
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:Q30").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "Team Production Today"
.Item.To = "E-Mail_Address_Here"
.Item.Subject = "End of Day prod " & Format(Date, "mmmm dd, yyyy")
.Item.Send
End With
ActiveSheet.Protect "test"
End Sub[/vba]

[jenroyce]

Im getting a Run-time error '1004': Application-defined or object defined error when i click the button

[GTO]

Presuming Outlook is running, I am not seeing what is going Kaboom(!). Put your cursor in the sub someplace, start pressing the F8 key to step thru. At what line does she fall over?

[jenroyce]

its highlighting : ActiveSheet.Protect "test"

[GTO]

I am afraid I am at a loss. Replacing any sensitive info with similar fake data, I would suggest attaching the wb with the code exactly as you are running now.

[jenroyce]

My apologies,

here:

[vba]

Sub Send_Range()
ActiveSheet.Unprotect "21havefaith"
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:Q29").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "Team Production Today"
.Item.To = "E-Mail_Address_Here"
.Item.Subject = "End of Day prod " & Format(Date, "mmmm dd, yyyy")
.Item.Send
End With
ActiveSheet.Protect "21havefaith"
End Sub

[/vba]

[GTO]

Please attach the wb, so we can see how its failing. Below the quick reply box click the Go Advanced button. In the new window, there's a <Manage Attachments> button.

[jenroyce]

Hi

The original file im afraid have sensitive data, this doc however should emulate the real document im going to use it on because im using the exact same code and still get the error

[RonMcK]

Jen,

I won't be able to work on this until Monday at the office; I don't use MSO on my laptop.

From reading your code, I assume that the email is getting sent but the worksheet fails to take protection?

Have a blessed Sunday.

Thanks,

[Aussiebear]

Tested using 2007 and it sent the email to my address, and the workbook is still protected.

It also sent the button image, so you may need to shift the button to a position outside of the selected range A1:Q29

[jenroyce]

Thanks Aussiebear!!