PDA

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



jenroyce
04-08-2011, 02:20 AM
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! :hi:

Bob Phillips
04-08-2011, 02:36 AM
Just unprotect it first


Activesheet.Unprotect


then protect it afterwards

Activesheet.Protect

jenroyce
04-08-2011, 02:40 AM
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? :dunno

jenroyce
04-08-2011, 03:53 AM
When i tried doing it myself with this code:
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



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

Please help :(

GTO
04-08-2011, 03:56 AM
This seemed to work for me. Double check your password. Note that I tacked on the current date that you asked about.
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

jenroyce
04-08-2011, 04:00 AM
Im getting a Run-time error '1004': Application-defined or object defined error when i click the button :(

GTO
04-08-2011, 04:07 AM
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
04-08-2011, 04:09 AM
its highlighting : ActiveSheet.Protect "test"

GTO
04-08-2011, 04:23 AM
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
04-08-2011, 06:38 AM
My apologies,

here:


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

GTO
04-08-2011, 07:52 AM
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
04-08-2011, 05:03 PM
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
04-09-2011, 08:31 PM
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
04-10-2011, 05:01 AM
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
04-10-2011, 06:22 PM
Thanks Aussiebear!!