Using a checkbox that selects certain cells [solved]

[gte]

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?

[Winon]

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.

Option Explicit

Private Sub CheckBox1_Click()

If CheckBox1 = True Then
Range("D46:AA46").Select
Else: Range("B46").Select
End If

End Sub

For ease of reference, I have attached a sample Workbook for you.

Regards.

[gte]

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

[Winon]

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.

[gte]

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

[Winon]

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.

[gte]

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