+ Reply to Thread
Results 1 to 2 of 2

Add a scrollbar to a list with checkboxes.

  1. #1
    Registered User
    Join Date
    06-12-2014
    Posts
    1

    Exclamation Add a scrollbar to a list with checkboxes.

    I have a list of 30 rows and 11 columns (B6:L34)
    In the first column, every cell has a drop down list referring back to the same data... and in 3 of the columns there are checkboxes (form controls). I want to make this list scroll so that only 10 of the rows are visible at a time instead of all 30 in order to shorten the overall table.

    I know that there are ways to add scrollbars through form controls, but I can't get it to work with all of the data already entered?
    Any ideas?!

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Add a scrollbar to a list with checkboxes.

    I think the code below will do what you want, but it's going to take some work with your controls to set it up. The complication is that if you right-click your form controls and select Format Control, then Properties, you will see that "Move and size with cells" is disabled (at least it is for me), so we need to include that in the code, which I have done.

    The first thing you need to do make sure the controls are all set to "Move but don't size with cells". I think that's the default, but check a few to be sure. If they're not, you can multi-select them and do them all at once.

    Next, your going to need to rename all your controls so the names match the row they sit on. That way the code can find them. This will be the biggest pain because you'll have to do them one at a time. First, paste the following into the Immediate pane of the VBA window:

    Please Login or Register  to view this content.
    Next, right-click the first drop-down and observe the name in the name field (where the cell name appears when you select a cell). In the Immediate pane, replace the N with the number so it matches the name, and change the R so it matches the row that the control is sitting on (for example, if Drop Down 1 is in row 6, rename it to DropDown6). With the cursor still on that line in the Immediate pane, press Enter. If you unselect and reselect that control, it should now have the new name. Repeat this for all the drop-downs.

    Do the same thing with the checkboxes, except that since you have multiple checkboxes on each row, include the column number also so they will each have a unique name (Check Box 2 becomes CheckBox2F, Check Box 3 becomes CheckBox2G, etc.)

    Once that's all done, it wouldn't hurt to save the workbook . Next, add a scrollbar control, select Properties, and change the Max value to 20. Close Properties, right-click the control and select Format Control, Properties, and "Don't move and size with cells".

    Finally, in the code window, Project Explorer, double-click the sheet containing the controls, and paste the following code into it. I've set it up to handle two checkboxes (F & G) in each row. You can adjust as needed. It also has constants for the specifications you gave, which can also be adjusted. Just remember that if you adjust the VisibleRws or TotalRws values, you may also need to adjust the Max value in the scrollbar control to match. If it blows up when you scroll, go into debug and check the row and control names it's trying to work with to make sure they match actual control names.

    Good luck!

    Please Login or Register  to view this content.
    Last edited by natefarm; 06-13-2014 at 03:44 PM.
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Change ScrollBar Max value when a second ScrollBar vale is changed?
    By Jon Henry in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2013, 06:47 PM
  2. [SOLVED] Dropdown List w/ Checkboxes
    By Stonesteel15 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-28-2012, 10:33 AM
  3. Scrollbar in a dropdown list
    By jmoffett in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-24-2008, 11:36 AM
  4. how do I link a list of possible value to a scrollbar?
    By philrogers in forum Excel General
    Replies: 0
    Last Post: 07-12-2005, 10:05 AM
  5. [SOLVED] scrollbar reslut makes another scrollbar to show
    By Patrik in forum Excel General
    Replies: 0
    Last Post: 04-18-2005, 11:06 AM

Tags for this Thread

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