Posts by jamesparker_1

  • Code Protection Stops Re-Naming Codename

    Re: Code Protection Stops Re-Naming Codename


    Quote from Andy Pope

    Worked in my test.


    Do you have a reference to the Extensibility Library?
    If not and you do not have Option Explicit the Constant vbext_pp_locked will be Empty. The IF test will give you the opposite of what you want.


    vbext_pp_locked = 1



    Hi Andy.


    Apologies but I dont understand. What is the Extensibility Library? and how do i set up option explicit?


    Thanks


    James

  • Code Protection Stops Re-Naming Codename

    Re: Vba Code Protection Stops Renaming Of Codename


    Quote from Andy Pope


    If Thisworkbook.VBProject.Protection =vbext_pp_locked then
    Msgbox "Locked"
    else
    ' do stuff
    End if


    Hi Guys. Happy New Year.


    I like the above idea. However, it only seems to work when I have the VBA code Open, If VBA editor is closed it goes to the 'Do Stuff section of the if statement.


    James

  • Code Protection Stops Re-Naming Codename

    Hi


    I have a Userform that allows a user to select a year eg (2007) from a listbox called yearbox where Yearbox.Value = AddYear. When the user Presses 'OK' the following code runs:



    This is fine when the VBA code is unprotected. But when I protect it so to stop people viewing it the Macro jumps straight to the errorHandler. I get the feeling it is something to do with the line that renames the Sheet CodeName :


    Code
    ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).Name = wf


    But some advice would be great. I would rather not leave the code unprotected when i distribute my worksheet.


    Thanks


    James

  • Update Formula Links

    Re: Update Formula Links


    Hi ScottyP

    Yeah, I put this question up last night and it accidentally posted twice. I assume the administrators deleted both posts before i came in this morning!

    I like the idea! I shall give it a go and let you know if it works!

    Cheers!

    James

  • Update Formula Links

    Re: Update Formula Links


    Hi Venkat1926

    Yeah that works for individual cells but I need something that will do a whole sheet full of formula all at once! There are too many formula cells use this method for.

    Also, what i need is the VBA code that will do this automatically so that when the macro I already have creates the 'WF Tracker 2008' sheet, i want it to also refresh all the formula on the original sheet.

    Thanks

    James

  • Update Formula Links

    Hi All


    I have a sheet with formula in various cells that are referenced to a sheet that doesnt initially exist. For example one formula on the sheet is:


    Code
    =IF('WF Tracker 2008'!$D$5=AN$4,INDEX('WF Tracker 2008'!$B$6:$P$10, MATCH($E36,'WF Tracker 2008'!$B$6:$B$10,), MATCH(AN$7,'WF Tracker 2008'!$B$6:$P$6,)),IF(AN26="N/A",AN31,"N/A"))


    and initially, sheet 'WF Tracker 2008' doesnt exist so the formulas all produce #ref! values which I expect.


    My problem is that I Run a macro that creates the sheet 'WF Tracker 2008' but the formula dont want to refresh/update unless I individually go into each cells formula in the formula bar and hit return.


    Does anyone know what code i need to update/refresh these formula links?


    Thanks James

  • Rename Sheets-Worksheets Codename

    Re: Rename Codename


    Quote from norie

    James


    Sort of off-topic, but why not just copy the template sheet instead of creating a new sheet and then copying from the template?


    Hi,


    Yes, That is a good suggestion. Thanks, I will make that change too. :smile:

  • Rename Sheets-Worksheets Codename

    Re: Rename Codename


    Hi andy,

    Sorry, i'm not sure I understand what u mean exactly. WF_Template the codename of what is just a normal sheet with a few numbers and borders on it. It is nothing special. Its just there so I can copy it to the new sheets each time one is created.

  • Rename Sheets-Worksheets Codename

    Re: Rename Codename


    Hi Andy

    YearBox is just a list box on a userform containing years 2006,2007,2008,2009,2011 and 2012.

    WF_Template is just a sheet that contains a layout that i will be copying across to the newly created worksheet.

    So the full macro looks like:

  • Rename Sheets-Worksheets Codename

    Re: Rename Codename


    Hi

    Well the reason i want to do this is that I originally wrote a big spreadsheet full of macros that referenced sheets throughout my code using codenames incase someone using the file decided to rename the sheet. I had the sheets hidden and made them visible when required so the codenames were effectively hardcoded in.

    Now the 'powers that be' have decided they dont like having the sheets hidden and want to create these sheets using a macro as and when they are required.

    So personally, I dont want to have to go through my macros to change from using the codename and in terms of the program, I like using codenames as it stops people breaking the spreadsheet when they decide to rename sheets to names that they prefer.

    Is this going to be possible?

    ADDITIONALLY: Also, just tried Andy's code. It works kindof. It creates the sheet and changes the name and codename but then immediately errors with a 424 run time error saying 'object required'.

  • Rename Sheets-Worksheets Codename

    Hi


    I'm tring to create a newsheet and rename the codename but it doesnt seem to be working. Here is my code:



    Thanks for your help


    James

  • Rename Word Object File Icon

    Hi


    I've added an word object to my excel sheet as an icon.


    The only problem is that it shows the word icon then under it says 'Microsoft Word Document'. This might be an obvious and silly question. But how do I Rename the 'Microsoft Word Document' to a title of my choice?


    Thanks James