Working in Excel 2003 but not Excel 2007. Can not protect sheet.

Hi,
User entered data on Excel Sheet(s) and send to receipant. The returned 
sheet(s) should be locked and should not be modfied by the receipant. It 
works in Excel 2003. However, it does not work in Excel 2007. User sent the 
worksheet(s) but the sheet(s) did not lock as in Excel 2003. Receipants can 
modify the sheet(s). I tried many different ways without success.    

Please help and thank you for your support.


Private Sub cmdEmail_Click()
    Dim cnt As Integer
    Dim destWb, srcWb As Workbook
    Dim tmpWin, actWin As Window
    Dim stWbPath As String
    On Error Resume Next

    If InStr(1, Sheets("Cluster A").Cells(3, 4), "Validated", vbTextCompare) 
Then
    Else
        MsgBox "Form incomplete. Form did not sent."
        Exit Sub
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set srcWb = ActiveWorkbook
    With srcWb
        Set actWin = Active.Window
        Set tmpWin = .NewWindow
        cnt = Sheets("Cluster A").Cells(5, 2)
        If cnt = 1 Then
            .Sheets("Cluster A").Range("I1:J49").ClearContents
            .Sheets("Cluster A").Shapes("Drop down 11").Cut
            .Sheets(Array("Cluster A")).Copy
        ElseIf cnt = 2 Then
            .Sheets("Cluster A").Range("I1:J49").ClearContents
            .Sheets("Cluster A").Shapes("Drop down 11").Cut
            .Sheets("Cluster B").Range("I1:J49").ClearContents
            .Sheets("Cluster B").Shapes("Drop down 12").Cut
            .Sheets(Array("Cluster A", "Cluster B")).Copy
        ElseIf cnt = 3 Then
            .Sheets("Cluster A").Range("I1:J49").ClearContents
            .Sheets("Cluster A").Shapes("Drop down 11").Cut
            .Sheets("Cluster B").Range("I1:J49").ClearContents
            .Sheets("Cluster B").Shapes("Drop down 12").Cut
            .Sheets("Cluster C").Range("I1:J49").ClearContents
            .Sheets("Cluster C").Shapes("Drop down 13").Cut
            .Sheets(Array("Cluster A", "Cluster B", "Cluster C")).Copy
        End If
    End With
    tmpWin.Close
    Set destWb = ActiveWorkbook
    stWbPath = Environ$("temp") & "\"
    If appVer < 12 Then
        destWb.SaveAs stWbPath & "Company A Form " & Sheets("Cluster 
A").Cells(3, 8) & ".xls"
    Else
        destWb.SaveAs stWbPath & "Company A Form " & Sheets("Cluster 
A").Cells(3, 8) & ".xls", FileFormat:=56
    End If
    
    For ptr = 1 To cnt
        destWb.Sheets(cnt).Select
        ActiveSheet.Unprotect "$$$ Company1"
        ActiveSheet.Cells.Select
        Selection.Locked = True
        Selection.FormulaHidden = True
        ActiveSheet.Protect Password:="$$$ Company1", DrawingObjects:=True, 
Contents:=True, Scenarios:=True
        ActiveSheet.Cells(3, 8).Select
    Next ptr
    destWb.SendMail Array("user1@company1.com", "user2@company1.com"), 
"Company A Form " & Sheets("Cluster A").Cells(3, 8) & " return."
    destWb.Close False
    MsgBox "Form has been sent to email receipants.", , "Send Form by Email"
    Kill stWbPath & "Company A Form " & Sheets("Cluster A").Cells(3, 8) & 
".xls"
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.Close False
End Sub
0
Utf
4/11/2010 10:58:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
814 Views

Similar Articles

[PageSpeed] 56

Sorry guys,  I found the problem, the code shoukd work after correcting one 
error.

Set actWin = Active.Window  shoud be  Set actWin = ActiveWindow

"Perry" wrote:

> Hi,
> User entered data on Excel Sheet(s) and send to receipant. The returned 
> sheet(s) should be locked and should not be modfied by the receipant. It 
> works in Excel 2003. However, it does not work in Excel 2007. User sent the 
> worksheet(s) but the sheet(s) did not lock as in Excel 2003. Receipants can 
> modify the sheet(s). I tried many different ways without success.    
> 
> Please help and thank you for your support.
> 
> 
> Private Sub cmdEmail_Click()
>     Dim cnt As Integer
>     Dim destWb, srcWb As Workbook
>     Dim tmpWin, actWin As Window
>     Dim stWbPath As String
>     On Error Resume Next
> 
>     If InStr(1, Sheets("Cluster A").Cells(3, 4), "Validated", vbTextCompare) 
> Then
>     Else
>         MsgBox "Form incomplete. Form did not sent."
>         Exit Sub
>     End If
> 
>     With Application
>         .ScreenUpdating = False
>         .EnableEvents = False
>     End With
>     Set srcWb = ActiveWorkbook
>     With srcWb
>         Set actWin = Active.Window
>         Set tmpWin = .NewWindow
>         cnt = Sheets("Cluster A").Cells(5, 2)
>         If cnt = 1 Then
>             .Sheets("Cluster A").Range("I1:J49").ClearContents
>             .Sheets("Cluster A").Shapes("Drop down 11").Cut
>             .Sheets(Array("Cluster A")).Copy
>         ElseIf cnt = 2 Then
>             .Sheets("Cluster A").Range("I1:J49").ClearContents
>             .Sheets("Cluster A").Shapes("Drop down 11").Cut
>             .Sheets("Cluster B").Range("I1:J49").ClearContents
>             .Sheets("Cluster B").Shapes("Drop down 12").Cut
>             .Sheets(Array("Cluster A", "Cluster B")).Copy
>         ElseIf cnt = 3 Then
>             .Sheets("Cluster A").Range("I1:J49").ClearContents
>             .Sheets("Cluster A").Shapes("Drop down 11").Cut
>             .Sheets("Cluster B").Range("I1:J49").ClearContents
>             .Sheets("Cluster B").Shapes("Drop down 12").Cut
>             .Sheets("Cluster C").Range("I1:J49").ClearContents
>             .Sheets("Cluster C").Shapes("Drop down 13").Cut
>             .Sheets(Array("Cluster A", "Cluster B", "Cluster C")).Copy
>         End If
>     End With
>     tmpWin.Close
>     Set destWb = ActiveWorkbook
>     stWbPath = Environ$("temp") & "\"
>     If appVer < 12 Then
>         destWb.SaveAs stWbPath & "Company A Form " & Sheets("Cluster 
> A").Cells(3, 8) & ".xls"
>     Else
>         destWb.SaveAs stWbPath & "Company A Form " & Sheets("Cluster 
> A").Cells(3, 8) & ".xls", FileFormat:=56
>     End If
>     
>     For ptr = 1 To cnt
>         destWb.Sheets(cnt).Select
>         ActiveSheet.Unprotect "$$$ Company1"
>         ActiveSheet.Cells.Select
>         Selection.Locked = True
>         Selection.FormulaHidden = True
>         ActiveSheet.Protect Password:="$$$ Company1", DrawingObjects:=True, 
> Contents:=True, Scenarios:=True
>         ActiveSheet.Cells(3, 8).Select
>     Next ptr
>     destWb.SendMail Array("user1@company1.com", "user2@company1.com"), 
> "Company A Form " & Sheets("Cluster A").Cells(3, 8) & " return."
>     destWb.Close False
>     MsgBox "Form has been sent to email receipants.", , "Send Form by Email"
>     Kill stWbPath & "Company A Form " & Sheets("Cluster A").Cells(3, 8) & 
> ".xls"
>     With Application
>         .ScreenUpdating = True
>         .EnableEvents = True
>     End With
>     ActiveWorkbook.Close False
> End Sub
0
Utf
4/12/2010 2:40:01 AM
In message <9797C16F-51C4-4D1C-BB86-2CAE5C29FE17@microsoft.com> of Sun,
11 Apr 2010 19:40:01 in microsoft.public.excel.programming, Perry
<Perry@discussions.microsoft.com> writes
>Sorry guys,  I found the problem, the code shoukd work after correcting one
>error.
>
>Set actWin = Active.Window  shoud be  Set actWin = ActiveWindow

My suspicion was that option explicit would have identified your
problem. I tried the following in Excel 2003.
1) I copied and pasted your example.
2) Several lines had wrapped into invalid code. I unwrapped to fix.
3) The next line found your error:
Option Explicit    ' Force explicit variable declaration.
4) It found 2 more which I "fixed" with the following at module level:
Const appVer = 12
Dim ptr As Integer

I find Option Explicit regularly protects me from idiocy.
I suggest the OP should use it in all his code.

However it does not protect me from hiding a variable with code like

Dim foo as Range

Sub bar()
    Dim foo as Variant
....
    foo.value = "fubar"
End Sub

Such code is a disaster waiting to happen if the second Dim statement is
deleted. I know of no automatic method of detecting such problems.

It is something I would like to see in a VBA Lint, if somebody would
write such code. cf. <http://en.wikipedia.org/wiki/Lint_%28software%29>
-- 
Walter Briscoe
0
Walter
4/12/2010 11:12:10 AM
Reply:

Similar Artilces:

MS Excel expense sheet password lost
Hello. i'm trying to edit the a ms excel expense statement/re-imbursement template. unfortunately, it's password protected (and nobody at work knows what it is). does anyone know of a program that can crack an ms excel password or know a way of over coming this problem? all i want to do is duplicate, re-arrange, and rename the sheets. wil. Hi Wil check out http://www.mcgimpsey.com/excel/removepwords.html for information Cheers JulieD "Wil" <nospam@nospam.com> wrote in message news:Xns9622D5640ED93nospamnospamcom@207.46.248.16... > Hello. i'm trying ...

"Page setup" "sheet" in Excel problem
When attempting to print row titles on each page I am having difficulty. In the preview mode I click "setup" "sheet", but I am unable to enter to rows that I want repeated. Any suggestions In Normal, Page Break Preview, or Page Layout view, choose File/Page Setup... In article <475FACA6-B203-4F67-9F4B-147FDE132132@microsoft.com>, Vern T <Vern T@discussions.microsoft.com> wrote: > When attempting to print row titles on each page I am having difficulty. In > the preview mode I click "setup" "sheet", but I am unable to enter to ro...

auto caps don't work
in outlookk 2003, even tho i have set tools, options, spelling to autocorrect the first word of a sentence. it doesn't do it. i have never had this problem before, but now i have a new laptop and can't get this feature to work. -- tom martin Are you using Word as your editor? If so, your settings are there. Otherwise, learn to type. http://www.broderbund.com/jump.jsp?itemID=4713&itemType=CATEGORY&path=1%2C2%2C4713&ysmchn=GGL&ysmcpn=Typing&ysmcrn=sr2br29go633go202pi10ai50&ysmtrm=sr2br29go633go202pi10ai50+mavis+beacon&ysmtac=PPC&ovtac=PPC&SR=s...

Help in Excel 2003
In the older Excel 2000, when I right-click on a heading in the Help Table of Contents, I get a choice of Open all, Close all, or Print .... Choosing "Print ..." then gives me a dialog box with two choices--Print the selected topic, or Print the selected heading and all subtopics. Is there an easy way in Excel 2003 to get this choice of Printing the selected heading and all subtopics? Right-clicking on a Table of Contents heading in Exccel 2003 does not present me any choices; it does nothing. Perhaps there is a setting I can change somewhere to make the Excel 2003 Help sys...

Hyperlinks to DB dont work now that DB is converted
We have converted all our Access 97 databases to Access 2000- not without a lot of issues. Most of them have now been researched and resolved, some re-coded, etc. We have one issue that we cannot resolve. We used to have hyperlinks on our Intra-net to several Access Databases. These were essentially "shortcuts" to the databases so that the general population could have easy access. Now that we did our Access conversion, the hyperlinks on our intranet do not work. Clicking on the link gives an option to open the file from its current location or download it to a local loc...

Send to Onenote on Windows 7, driver won't work?
I downloaded the driver from David Rasmussen, but every time I open OneNote after printing to XPS I get the message "Unhandled exception has occured in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Object reference not set to an instance of an object." If I click continue it still doesn't work. This was one of my favorite features - PLEASE PLEASE PLEASE find me a way to use OneNote! I lived off of it last year. Rilli wrote: > I downloaded the d...

working with temporary working files..
I keep getting the message that I cannot save a temp. working file. This work has been save on a cd and transfered back on to my desk top. Help ...

Excel lookup #2
I am using the date time picker control in cell a1 on page a in m spreadsheet. In cell b1 on on page 2 i have an if statement to choos cell a1 on page a if the date field is populated otherwise prin "Update Test Number". I can't populate cell b1 on page b with the valu from the date time picker control. Anyone know how to do this -- Message posted from http://www.ExcelForum.com ...

How does hibernate work
I have a question on the details of how hibernate works. I know it's saving everything in memory to the hiber file. Since power seems to be truly off at hibernation I assumed the flag to tell the PC to resume from the hiber file rather than normal booting must be either saved on disk or flashed to a bit of bios rom space, or maybe in space maintained by the clock battery. But the cure for a laptop that is stuck in a loop of constantly resuming from hibernating is to unplug it and take the battery out for a while so where actually is the flag to signal the system that it's in hi...

Search does not work for one workbook
I have workbook, but search (spotlight) often does not work for it. Drives me crazy. What can this be. I see peoples names in there. Kevs OS 10.6.2 INTEL imac Office 2004 Hi Kevs: It could be the file format the workbook is saved in. What extension does the file-name have? The extension tells the system what kind of data a file contains, which controls which importer Spotlight uses to read the file: if there is no extension you could get strange results. Cheers On 6/01/10 2:47 AM, in article C7689F21.5718F%forums55@verizon.net, "forums55@verizon.net"...

Can't open files
A friend left a garbled message on my telephone answering machine asking me for help but I don't use Vista or iTunes, so I'm hoping some kind soul on here can help. Basically any file he downloads (it may be a photo of his grandson sent as an email attachment in jpg format, or a program such as skype.exe, or a pdf document) cannot be opened. When double-clicked, any downloaded file opens up iTunes and presents the message "iTunes cannot open this file". I know it's not much to go on but his message recorded badly. I'll try to contact him and get more...

Starting Excel #2
How can make sure that a workbook with multiple sheets always starts on a specific sheet. Hi put the following type of code into your workbook module ('Thisworkbook'): sub workbook_open() me.worksheets("sheet1").activate end sub "Gerrym" wrote: > How can make sure that a workbook with multiple sheets > always starts on a specific sheet. > ...

Non-working work week
Is there any way to make an entire week a non-working work week? As in, a task is going to take 25 days, but the 4th of July occurs in the middle and you want to show that during that week (or 2), no work will occur. Besides creating a new task or extending the task during. Thanks in advance! Hi RVE, Open up the calendar, "Tools", 'Change working time..', then simply select the days you want as non working. Make sure that the calendar you change is the Project calendar and is the basis for all other calendars such as individual resource calendars. Hope thi...

Excel 2003 versus Excel 2007 calculation speed
I'm running an interesting test on a PC of Excel 2003 and Excel 2007. I ran a Monte Carlo simulation in Excel 2003. The workbook has 50 input cells (cells where random values per distributions are input) and 200 calculation (formula) cells. So, it is basically a small workbook. No conditional formatting. No charts. The VBA code calculates random values per distributions and puts into the input cells. The workbook is then recalculated and the values of the input and output cells (20 of the formula cells) are recorded. Only cell values are recorded - formatting is not set on ...

Viso 2003 evaluation
Why oh why, do I get 'page not found' when I try to order/download the evaluation copy? I believe it's because the evaluation period is over. You have to purchase the full version now... The beta is indeed complete, but MS will be providing a trial (evaluation) version soo. Randall Arnold "Daniel L. Belton" <abuse@spam.gov> wrote in message news:Q5Inb.27657$oC5.3758@clmboh1-nws5.columbus.rr.com... > I believe it's because the evaluation period is over. You have to purchase > the full version now... ...

Graphing work and remaining work over time
I would like to generate a graph showing total actual work and remaining work for a top level task over time (week), This would enable me to show the projected remaining work through the duration of the project as it decreases from N to 0, and show the actual work completed though the duration of the project as it increases from 0 to N. The canned visual reports don't allow me to do this (that I can see anyway). Maybe I'm missing something. I also can't see how to customize a visual report that will do what I want. Using Microsoft Project 2007 (not the Server edit...

Cursor over data point on Excel Chart
For an x-y graph in Excel where the x values are dates and the y values are stock data, I have formatted it so I see the dates along the horizontal x axis. All good so far, but when I place my cursor pointer over a data point, it flashes up the numeric value for the date, then the price point like this: (37934, 1254) I wonder if there is a formatting technique to force it to show the date in date format. It would be easier for me to identify a particular date since I space out the date labels along the axis and can't determine with precision the exact date of a point I've placed th...

Excel 2000
Hello I am new and exhausted from trying to find an answer to m problem. I enter a date into a cell (ex. 020206 which should auto format t 02/02/2006) but when I hit enter I get a weird date that comes u 1/23/7436. This happens with any date I enter. I tried to custom format the cel ##/##/## but that doesn't work, and I tried "text to columns" but ha no success. Any help with detailed solutions would be appreciated. thank -- vt ----------------------------------------------------------------------- vtz's Profile: http://www.excelforum.com/member.php?action=getinfo&us...

GP8 Manufacturing
Hello Everybody. Our Shop works 5 days a week (closed weekends). We have a WC that works 6 days a week (down Sundays only). We have setup both the Shop and WC Calendars to reflect this reality. We would expect that, when scheduling an MO that routes thru this WC, the WC calendar will override the Shop Calendar? However, our tests are not showing this to be true - We use a Backwards Infinite Scheduling Method and it always skips the weekend when calculating the start day of an MO regardless of the WC. Does anyone have any experience working with the WC Calendars? Please share your wis...

Calendar Creation in Excel
I was tasked with creating a calendar in Excel, that could then be printed and used in our department. The calendar had to be created to specific requirements: 1. The last three months of 2004 had to be included; 2. The full of 2005 had to be included; 3. The first three months of 2006 had to be included; 4. The reverse of the calendar had to show the full year for 2004 and the full year for 2006; Items 1, 2 & 3 had to follow on from each other and had to be in the format with the days down the sides and the months along the top. As I couldn't find a...

Enabling IMF on Exchange 2003 SP2
I did not have IMF1 on Exchange 2003 Sp1. We rely on ORF from www.vamsoft.com for our anti-spam needs but I wanted to play with IMF since it is already there. However, this stumped me: ---------------------------------------------------------------------- http://download.microsoft.com/download/f/b/5/fb5c54af-fe5c-48e9-be97-f9e8207325ab/Ex_2003_SP2_RelNotes.htm Because Intelligent Message Filter is included in Exchange Server 2003 SP2, it is no longer an add-on and, therefore, a container must be created within Active Directory´┐Ż directory service as follows: Cn=UCE Content Filter,cn=Message...

outlook2003 send button does not work
send button does not work i have tryed a new email acc. and reinstalling with no luck. now i am useing express and then export to outlook ...

Inserting Pictures into Excel cell
I would like to insert a Picture or a word file into an excel worksheet cell, but keep it hidden behind the cells below. Usually, when I insert a word file, etc. it appears on top in full and covers cells underneath. Is there a way of inserting a file in a cell, keep most of it hidden so that when you click on the cell, the whole thing appears. When you click on another cell, it hides again? Please let me know in simple clear English. Hi AFAIK not really, when you insert pictures into excel they don't actually go into a cell ... the "best" option, might be to insert the...

Drag and Drop does not work
Hi all. This is funny: When I try to drag and drop a message from "Inbox" to "Backup" I get an error message. When I right click and select "move to folder"- it does work. ANY CLUES? TIA Guy Not a single one. No Outlook version = no clues. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Guy Cohen asked: | Hi all. | | This is funny: | When I try to drag and drop a message from "Inbox" ...

Reference between sheets
am trying to creat an excel file that will have few sheet. The first one will contain a list of items, the other one will be the details. For example: Sheet1: 1. Pump 2. Computer 3. Laser Head Sheet2: Pump Pump Head Pump Motor Pump tubing .... ... I thought that may be the hyperlink is a good idea, but may be anyone has a better idea? Thanks, Joe Joe As I asked earlier, what are you trying to achieve? Andy. "Galsaba" <galsaba@aol.com> wrote in message news:20031104053514.28920.00000070@mb-m23.aol.com... > am trying to creat an excel file that will have few sheet....