Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

UnProtection Code for VBA Project properties

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • UnProtection Code for VBA Project properties

    I have put protection on my VBA code covering a large number of spreadsheets, all with the same base template - Tools>VBAProject properties>properties. Unfortunately I want to insert a workbook and run some other code across all of the spreadsheets.

    I'm not keen on openning all spreadsheets and having to unprotect the code for each one before running the new code.

    Is there some code that will do this for me.

    Thanks

  • #2
    In short: No
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

    Comment


    • #3
      XL-Dennis,

      Damn. I guess this is another lesson. Time to look for another option.

      Thanks for your reply.

      Comment


      • #4
        Just to rub salt into the wound Dennis is correct as usual. It's a security thing..

        Comment


        • #5
          Double Damn,

          Clearly I'll need to look at the problem from a different angle. I'm sure that I'll find some sort of solution.

          Thanks again for your input Dave.

          Comment


          • #6
            Dave,

            My issue is that in the templates, I copied cells from one sheet to another. Unfortunately I copied the wrong cells in the templates, specifically;

            "='Sheet1'!$H$4", instead of "='Sheet2'!$H$13". and
            "='Sheet2'!$H$27", instead of "='Sheet2'!$H$15".

            The next step in my process was to consolidate the data from all the template spreadsheets into another single workbook. Part of the code was:
            Code:
                               
              wbResults.Sheets("Sheet1").Range("A4:D4").Copy
              wbConsolidate.Sheets("Sheet1").Range("A65536").End _
              (xlUp).Offset(1, 0).PasteSpecial xlValues
               Application.CutCopyMode = False
            Here A4 and B4 are correct. "C4" is where "='Sheet2'!$H$13" should be and "D4" is "='Sheet2'!$H$15"

            The solution that I was going to do was to insert a new sheet into the templates and copy and paste the desired cells - until XL-Dennis gave me the 'no can do'.

            Is there a way to amend the above code to copy the correct cells?

            Hope this make sense?
            Thanks in advance.

            Comment


            • #7
              I go somewhat lost at this bit;
              "Here A4 and B4 are correct. "C4" is where "='Sheet2'!$H$13" should be and "D4" is "='Sheet2'!$H$15"" The only range I see is: Range("A4:D4").

              How about Edit>Replace ?

              Comment


              • #8
                My apologies.

                I'll try to reword it.

                I have multiple workbooks based off the same template. Sheet ("Totals") is the sheet in each template (workbook) where data from is copied and pasted. In the Sheet ("Totals") cell C4, I copied "='Sheet1'!$H$4", instead of copying "='Sheet2'!$H$13". Also copied ='Sheet2'!$H$27" into D4, instead of "='Sheet2'!$H$15" into D4.

                The data copied into A4 and B4 was copied correctly - making the range ("A4:D4").

                The next step in my process was to take the data from all the Sheet ("Totals") and consolidate them into a seperate single worksheet. Part of the code was:

                Code:
                wbResults.Sheets("Totals1").Range("A4:D4").Copy 
                wbConsolidate.Sheets("Consolidate").Range("A65536").End _ 
                (xlUp).Offset(1, 0).PasteSpecial xlValues 
                Application.CutCopyMode = False
                Does that make sense?

                As much as I am loving what Excel and VBA can do, I am realising that I have an amazing amout to learn.

                Thanks
                Brett

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X