Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: UnProtection Code for VBA Project properties

  1. #1
    Join Date
    7th April 2003
    Posts
    41

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th January 2003
    Location
    Ístersund, Sweden
    Posts
    2,451
    In short: No
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  3. #3
    Join Date
    7th April 2003
    Posts
    41
    XL-Dennis,

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

    Thanks for your reply.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,707

  5. #5
    Join Date
    7th April 2003
    Posts
    41
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th April 2003
    Posts
    41
    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:
    VB:
     
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,707
    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 ?

  8. #8
    Join Date
    7th April 2003
    Posts
    41
    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:

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: January 30th, 2009, 12:20
  2. Lock VBA Project With Code
    By mrfitness in forum EXCEL HELP
    Replies: 7
    Last Post: January 20th, 2008, 01:31
  3. Set PlotArea Properties In Code
    By postman2000 in forum EXCEL HELP
    Replies: 1
    Last Post: September 11th, 2007, 13:00
  4. Unprotecting VBA Project via VBA code to change code
    By HarryFrohlich in forum EXCEL HELP
    Replies: 22
    Last Post: July 27th, 2006, 20:57
  5. properties window vba project
    By alexanderd in forum EXCEL HELP
    Replies: 1
    Last Post: June 14th, 2004, 05:22

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno