+ Reply to Thread
Results 1 to 7 of 7

Using a checkbox that selects certain cells [solved]

  1. #1
    Registered User
    Join Date
    05-25-2016
    Location
    MD
    MS-Off Ver
    2013
    Posts
    5

    Using a checkbox that selects certain cells [solved]

    Hi,

    How can I have a checkbox in (for example) B46 select cells D46 through AA46? and of course unselect them when I uncheck the same checkbox?
    Last edited by gte; 05-25-2016 at 04:42 PM.

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Using a checkbox that selects certain cells

    Hello gte,

    Welcome to the Forum.

    You don't state if you are referring to an ActiveX Control CheckBox, or a Forms Control CheckBox.

    If you know how to create a CheckBox of your preference, and to setup its properties, then do so on a Sheet of your choice. Then right click on the Sheet Tab and select "View Code". This will take you to the VBA environment. Towards the top, to the left of the screen, under VBAProject, click on Microsoft Excel Objects, and select the sheet where you want to enter your Code.

    Once that Sheet Code opens, paste the code below into that page. Please note that this assumes an ActiveX Control CheckBox1. If you have more any such CheckBoxes, then please change it to suit, i.e. CheckBox2 or CheckBox3, and so on.

    Please Login or Register  to view this content.
    For ease of reference, I have attached a sample Workbook for you.

    Regards.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Registered User
    Join Date
    05-25-2016
    Location
    MD
    MS-Off Ver
    2013
    Posts
    5

    Re: Using a checkbox that selects certain cells

    Hi,

    That worked great, thank you.

    How would I set up a button that will only copy the rows that are highlighted and have a check next to them? Do I need a case statement?

    Attached is the updated file you created to modify
    Attached Files Attached Files
    Last edited by gte; 05-25-2016 at 02:16 PM.

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Using a checkbox that selects certain cells

    Hi gte,

    Thank you for the feedback.

    How would I set up a button that will only copy the rows that are highlighted and have a check next to them?
    Honestly, I am a bit confused here.

    Copy the rows that are highlighted to where and have what check next to them?

    Please could you clarify?

    Regards.

  5. #5
    Registered User
    Join Date
    05-25-2016
    Location
    MD
    MS-Off Ver
    2013
    Posts
    5

    Re: Using a checkbox that selects certain cells

    I wrote code to get it to work, I'm fresh to VBA but once I can find the syntax I am golden


    Dim cellsSelected As String
    Dim cellsSelectedEdited As String

    Private Sub btnCopy_Click()


    cellsSelected = ""
    cellsSelectedEdited = ""

    If CheckBox1 = True Then
    cellsSelected = cellsSelected + "D46:AA46, "
    End If

    If CheckBox2 = True Then
    cellsSelected = cellsSelected + "D47:AA47, "
    End If

    If CheckBox3 = True Then
    cellsSelected = cellsSelected + "D48:AA48, "
    End If

    If CheckBox4 = True Then
    cellsSelected = cellsSelected + "D49:AA49, "
    End If

    If CheckBox5 = True Then
    cellsSelected = cellsSelected + "D50:AA50, "
    End If

    If (EndsWith(cellsSelected, ",")) = True Then
    cellsSelectedEdited = Left(cellsSelected, Len(cellsSelected) - 2)
    Else
    cellsSelectedEdited = cellsSelected
    End If

    ActiveSheet.Range(cellsSelectedEdited).Select


    End Sub

  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Using a checkbox that selects certain cells [solved]

    Hello gte,

    Good for you! I don't have Office2013 and your If (EndsWith(cellsSelected, ",")) = True is alien to my System.

    I wish I could see what it was what you actually wanted. In the meantime I have been playing around with a few assumptions as shown in the attached sample Workbook.(For what it is worth)

    Regards.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-25-2016
    Location
    MD
    MS-Off Ver
    2013
    Posts
    5

    Re: Using a checkbox that selects certain cells [solved]

    I'm sorry, I forgot to copy and paste a function I have, it is not a difference between 2010 and 2013, VB unfortunately does not have very good string parsing and has no function for starts with or ends with like .net. This allows me to trim empty spaces or commas off of the end of the string so the ActiveSheet.Range will still work, if there is an extra comma at the end it breaks it.



    Public Function EndsWith(str As String, ending As String) As Boolean
    Dim endingLen As Integer
    endingLen = Len(ending)
    EndsWith = (Right(Trim(UCase(str)), endingLen) = UCase(ending))
    End Function

+ 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. [SOLVED] Place X in correct column according to checkbox selects on form
    By brainzlp in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-19-2015, 10:20 PM
  2. Checkbox on a Userform that selects all entries of a Listbox
    By dutchcourage in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-17-2015, 08:09 AM
  3. Need macro to fetch value when a user selects a checkbox
    By santnair0599 in forum Excel General
    Replies: 2
    Last Post: 05-30-2015, 01:04 PM
  4. [SOLVED] Is there a way to add a button that selects the next checkbox in a row?
    By R_Bish in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-04-2014, 05:42 PM
  5. One checkbox that selects multiple checkboxes
    By pozmoney in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-25-2013, 05:57 PM
  6. Code selects entire rows upto one range and same code selects only columns for other.
    By sriharigk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2012, 05:19 AM
  7. selects all cells within that range
    By jsrinivasu in forum Excel General
    Replies: 5
    Last Post: 09-24-2007, 08:06 AM

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