Results 1 to 15 of 15
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Disallow Range Names (Excel 2000 >)

    Is it possible to disallow the user to name a range? In other words, can the name range feature of Excel be "switched off"?
    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disallow Range Names (Excel 2000 >)

    You can protect a worksheet. This will disable most items in the Insert | Name submenu for that sheet.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disallow Range Names (Excel 2000 >)

    But it does not stop you from doing it by starting from another worksheet.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disallow Range Names (Excel 2000 >)

    Oops. Protect all sheets, then? Or is there no good way?

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disallow Range Names (Excel 2000 >)

    Protecting all sheets could be used as a last resort. But I will wait for an answer on your final statement >>Or is there no good way? too!

    Could code disallow naming a range?!
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disallow Range Names (Excel 2000 >)

    But why do you want to prevent the user from naming a range?

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disallow Range Names (Excel 2000 >)

    I am just playing with the idea. I am creating a file for some collegues and I am aware that one or two collegues regularly use named ranges. I was thinking of disabling this to prevent them from trying to create their own range names in the file.
    talk about being nasty - eh!!

    I am still interested in if this could be done somehow, all be it that is is not critical!

    I can recall in a VBA manual that you can use methods in VBA to disable events from ocurring by using boolean switches. I was thinking of if a range name is created to use VBA to disable or delete the range name and warn the user with a message!
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disallow Range Names (Excel 2000 >)

    Frankly, I don't see the point. But protecting all worksheets in the workbook should at least make it more difficult, if not impossible, to create named ranges.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disallow Range Names (Excel 2000 >)

    Frankly, I don't see the point. (Ah-hum >> My name is Rudi, not Frank)

    Its OK. I'm just playing with the idea and was interested if there was an alternative. Its good to cast your bread on the waters and see what comes back! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    The problem with sheet protection in Excel 2000 will give the users very little options of changing the formatting and structure of the sheet though!
    Regards,
    Rudi

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disallow Range Names (Excel 2000 >)

    So I should have put it rudely, instead of frankly? <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disallow Range Names (Excel 2000 >)

    Well, you could temporarily hijack their Insert name menu...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disallow Range Names (Excel 2000 >)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> - very witty!
    Regards,
    Rudi

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disallow Range Names (Excel 2000 >)

    Now thats a suggestion. I recall posts in the lounge dealing with disabling menu commands with code. However, even if I disable the Insert | Name | Define command, they will still be able to create names in the Name Box on the formula bar!
    Regards,
    Rudi

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disallow Range Names (Excel 2000 >)

    Preventing a user from creating named ranges without a very good reason would be VERY Rude.
    Legare Coleman

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disallow Range Names (Excel 2000 >)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> - VERY well put Legare. I assure you that my name does not relate to my character <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. Frankly (haha) I am a really kind loveable person... just like JezzaBear (without the saw!)!
    Regards,
    Rudi

Posting Permissions

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