Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Search for Checkbox Value in Specific Column

0

I am building a Shelf live extention tracker for work, with the idea that each row is a different product, in columns L,M & N have check boxes, with macros attached to the ones in M & N, which will move the row of that check box to another sheet when checked. so far i have the below working code.

Dim cbrow As Long, chk As CheckBox, d As Range

For Each chk In ActiveSheet.CheckBoxes
If chk.Value = xlOn Then
cbrow = Range(chk.TopLeftCell.Address).Row

    Sheets("Active SLE").Select ' New line
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown

Call NoFill

 Sheets("SLE Requests").Select ' Copy & Paste
 ActiveSheet.Range("A" & cbrow & ":K" & cbrow).Select
 Selection.Copy
     Sheets("Active SLE").Select
     Range("A2").Select
     ActiveSheet.Paste
End If
Next

But this however i only want it to move the line if the check box in Column M is checked I tryed the below, but it didnt work.

For Each chk In ActiveSheet.Range("M:M").CheckBoxes
If chk.Value = xlOn Then
excel
posted by: SamMenu (rep: 2) on Thu Mar 14, 2024 at 8:34 am
Answer
Discuss

Answers

0
Selected Answer

Hi Sam and welcome to the Forum.

Try (within your procedure, changes in bold):

Dim cbrow As Long, chk As Shape, d As Range

For Each chk In ActiveSheet.Shapes
       ' check if the checkbox is both checked and in column M
        If chk.OLEFormat.Object.Value = 1  And Mid(chk.TopLeftCell.Address, 2, 1) = "M" Then
               cbrow = Range(chk.TopLeftCell.Address).Row
              '<< rest of your code >>

(For this to work, be sure that the checkbox is indeed in column M- in Developer/ Design Mode, right click on each and be sure that the left edge of the bounding box is in M. Id the desired column has 2 characters e.g. "AB", then the final arguments of the Mid changes to 2, so:

       ' check if the checkbox is both checked and in column AB
        If chk.OLEFormat.Object.Value = 1  And Mid(chk.TopLeftCell.Address, 2, 2) = "AB" Then

)

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.

Note: In future, in general it really helps us to help you if we could see your file. You can add it with your question (or if you forget) later edit your original question) and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing data (and any macros). Then we should be able to give specific help.

posted by: John_Ru (rep: 6142) on Thu Mar 14, 2024 at 9:52 am
Discuss

Discussion

Thanks John, it worked a treat, had to change some of the later code, but its put an end to me hitting my head on the desk. 
SamMenu (rep: 2) Mar 14, '24 at 11:59 am
Glad that helped and thanks for selecting my Answer, Sam. Sorry but I didn't check any further code once I'd fixed the "is it checked in column F?" test
John_Ru (rep: 6142) Mar 14, '24 at 12:08 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login