+ Reply to Thread
Results 1 to 8 of 8

Cannot select Scroll Bar object when sheet is protected

  1. #1
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Smile Cannot select Scroll Bar object when sheet is protected

    Hi Guys,

    I have added a scroll bar to my worksheet so that I can calculate percentages from 0% to 100%. The trouble is I want to protect my worksheet so certain cell with formulas cannot be deleted. I have selected the "Format Control" option and unticked "Locked" on the "Protection" tab. However when I lock the worksheet I cannot slide the values on the scroll bar.

    I even tried unlocking the cells behind the scroll bar, however this does not work either.

    Can anyone please explain how I can lock the sheet and still access the scroll bar.

    Thanks in advance.
    Best Regards.

    Michael
    -----------------------------------
    Windows Vista, Microsoft Office 2007

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi

    Are you using the Worksheet Forms scroll bar, or the Activex VBA scroll bar control?

    Assuming the former, have you also unticked the 'Locked' in the Format Cell Protection tab, for the cell that's linked to the scroll bar? If not untick this.

    HTH

  3. #3
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Smile

    Quote Originally Posted by Richard Buttrey
    Hi

    Are you using the Worksheet Forms scroll bar, or the Activex VBA scroll bar control?

    Assuming the former, have you also unticked the 'Locked' in the Format Cell Protection tab, for the cell that's linked to the scroll bar? If not untick this.

    HTH
    Hi I am using the scroll bar from the Forms menu toolbar. It's the toolbar that allows you to create List box, Combo box, spinner button etc.

    I you read my previous post I have unticked the "Locked" box the Protection tab, however for some reason when I try to drag the mouse over the slider I get the: "This sheet is protected blah blah..." and it won't let me access the scroll bar.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    I think you have misread/misunderstood my previous reply. I didn't mention the 'locked' box on the scroll bar protection tab, I accept that you have unlocked this. I said that you should untick the 'locked' box for the cell that is linked to the scroll bar.

    Rgds

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    As Richard says, you need to unlock both the control & the Linked Cell
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Forum Contributor
    Join Date
    03-30-2007
    Location
    London, UK
    MS-Off Ver
    Microsoft Office 2007
    Posts
    317

    Smile

    @Richard Buttrey & royUK

    I do apologise guys, I misunderstood what Richard meant. Thank you both so much it now works perfectly. In hindsight it makes perfect sense that the linked cell should be unlock as its value will need to change to reflect the values according to the sliding scroll bar.

    Thanks again to both of you for your help, I really appreciate it.

  7. #7
    Registered User
    Join Date
    01-03-2016
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    17

    Re: Cannot select Scroll Bar object when sheet is protected

    In same scenario how to unprotect the scroll bar incase of using a VBA Macro.
    If we unlock the linked cell it shows error while using scroll bar.

    Below is the vba code i am using:

    Sub Tracker()
    LeaveTracker.Columns("B:NI").Hidden = True
    LeaveTracker.Range(Columns(Range("A3").Value * 31 - 29), Columns(Range("A3").Value * 31 + 1)).Hidden = False
    End Sub

    Anyone can help

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Cannot select Scroll Bar object when sheet is protected

    Yuvaraaj,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1