Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Protected Cells and Floating boxes (Excel 2000)

    Hi all!

    The more I learn about Excel, the more questions I have! Here is the next series.

    The attached sheet is auto filtered and a frozen pane. I need to have all of the cells in the worksheet protected. However, when I protect the worksheet, I am unable to use the autofilter drop down arrows. I have also tried to protect just individual cells, but it ends up protecting the whole worksheet any way and again, I am unable to use the drop down arrows. I need for the viewers to be able to utilize the filter drop down arrows, yet prevent them from typing anything in any cells. As is, I have the whole document read-only protected until I can figure out how to get it the way I want it.

    Secondly, the top several rows are Frozen Panes to allow the user to scroll through the info but still be able to see the Column Labels at all time. I also have a message to contact us in the upper right. My problem is that when ever you scroll down, it takes away half of the message. It makes sense as the top portion is in the frozen pane section. However, I want that message to be showing at all times, no matter where the user is on the sheet. I have tried to use a text box and bring it to the front, but it still moves when you scroll up or down. Is there a way to cause this to stay in the same place on the screen?

    Thanks again for all of you help!!

    I appreciate your patience with me!

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

    Re: Protected Cells and Floating boxes (Excel 2000)

    Protect the worksheet, but add this code to the code module behind the worksheet (rightclick its tab and choose view code):

    <pre>Private Sub Worksheet_Activate()
    Me.Protect AllowFiltering:=True
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Protected Cells and Floating boxes (Excel 2000)

    Pieterse gave some good advice for question 1.

    With regards Question 2, is it necessary to have the font so large???

    If you make the font size 10, and then unwrap the cell and change the vertical alignment to Top, it embeds in the frozen area and does not disappear off the page when a user scrolls down!
    Regards,
    Rudi

  4. #4
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Cells and Floating boxes (Excel 2000)

    Pieterse, Thank you!!!!!

    Rudi,

    I have it that size so that it catches their attention. For many they seldom work with us or with Excel.

    Is this possible?

  5. #5
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Cells and Floating boxes (Excel 2000)

    Jan,

    When I put the code in there, it still does not work. It is possible that I am doing something wrong. When I go to the code, and I double click on the current sheet, there is no code currently written. I then Copied and pasted the code you wrote into the MVB. I then click on Close and return to excel.

    Are there any changes to the code that I am supposed to make? Perhapse changes that would be obvious to you and anyone else, but to me I would not have thought of doing? Keep in mind that I am such a noobie that it's not even funny! LOL

    Thank you in advance for your help!

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

    Re: Protected Cells and Floating boxes (Excel 2000)

    Sorry to butt in...
    Perry, when you copy the code from the post, first paste it into word, then copy it again and follow Pieterse's instructions. This pastes the code into VBA effectively.
    Regards,
    Rudi

  7. #7
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Cells and Floating boxes (Excel 2000)

    I tried it the way you told me to. Still no dice <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>.

    Could some one look at the attachment from my orrigional post and tell me what I am doing wrong? I am convinced it is user (ME) error.

    Thanks!!

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

    Re: Protected Cells and Floating boxes (Excel 2000)

    To make it work, leave the sheet (select another worksheet) and go back to it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Cells and Floating boxes (Excel 2000)

    Did that;

    Now I get an error that says: "Comple Error: Named argument not found." It highligts "AllowFiltering:="

    What do I do now?

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

    Re: Protected Cells and Floating boxes (Excel 2000)

    Oh no.

    Sorry for confusing you, but the code I gave you is for Excel XP and up, this AllowFIltering keyword was introduced with XP.

    My apologies.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Cells and Floating boxes (Excel 2000)

    So do I delete that part of the code, or is the whole code not compatible? If that code is not compatable, is there some other work around?

    Thanks!!

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

    Re: Protected Cells and Floating boxes (Excel 2000)

    Just delete the AllowFiltering parameter. The rest should work.
    Legare Coleman

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

    Re: Protected Cells and Floating boxes (Excel 2000)

    No it doesn't, she needs the autofilter to work, which it does not when protecting the sheet.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    New Lounger
    Join Date
    Sep 2004
    Location
    Jacksonville, North Carolina, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protected Cells and Floating boxes (Excel 2000)

    so, to finalize. There is no hope. Correct?

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

    Re: Protected Cells and Floating boxes (Excel 2000)

    Hi again
    All hope is not lost.
    Copy this and paste into word. Copy again, switch to excel, rightclick on the sheet tab and paste!

    Private Sub Worksheet_Activate()
    ActiveSheet.EnableAutoFilter = True
    ActiveSheet.Protect password:=test, contents:=True, userInterfaceOnly:=True
    End Sub

    This will work. It tried it in excel 2000. Functions well!!
    Regards,
    Rudi

Page 1 of 2 12 LastLast

Posting Permissions

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