[VBA] Make cell link in 3rd column next to the checkbox

[picmaster]

Im now try to create multiple checkbox with automatically linked cell with them. I found this formula. and I have no knowledge about VBA at all.

[COLOR=#2A2A2A][FONT=Segoe UI]Option Explicit
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]Sub addCBX()
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]Dim myCBX As CheckBox
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]Dim myCell As Range
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]    With ActiveSheet
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]       .CheckBoxes.Delete 'nice for testing
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]       For Each myCell In ActiveSheet.Range("A2:A10").Cells
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]          With myCell
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]           Set myCBX = .Parent.CheckBoxes.Add _
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]                               (Top:=.Top, Width:=.Width, _
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]                               Left:=.Left, Height:=.Height)
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]              With myCBX
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]                   .LinkedCell = myCell.Address(external:=True)
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]                   .Caption = "" 'or whatever you want
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]               '.Name = "CBX_" & myCell.Address(0, 0)
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]               End With
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]             .NumberFormat = ";;;"
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]           End With
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]       Next myCell
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]   End With
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]End Sub[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]
[/FONT][/COLOR][COLOR=#2A2A2A][FONT=Segoe UI]
[/FONT][/COLOR]

However, this will return result TRUE or FALSE within the same cell that contain checkbox. How can I modify it to show the result in the 3rd column next to it (Right hand side)

My checkboxes are in the column N and I want the result to be show in column Q

Thank you in advance

 

[Momentman]

Does this help?

Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range
    With ActiveSheet
       .CheckBoxes.Delete 'nice for testing
       For Each myCell In ActiveSheet.Range("N2:N10").Cells
          With myCell
           Set myCBX = .Parent.CheckBoxes.Add _
                               (Top:=.Top, Width:=.Width, _
                               Left:=.Left, Height:=.Height)
              With myCBX
                   .LinkedCell = myCell.Offset(0, 3).Address(external:=True)
                   .Caption = "" 'or whatever you want
               '.Name = "CBX_" & myCell.Address(0, 0)
               End With
             .NumberFormat = ";;;"
           End With
       Next myCell
   End With
End Sub

Note the texts with red font, you can change N2 to n10 to fit your actual range

 

[Snakehips]

picmaster,

Try....

Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range
    With ActiveSheet
       .CheckBoxes.Delete 'nice for testing
       For Each myCell In ActiveSheet.Range("N2:N10").Cells
          With myCell
           Set myCBX = .Parent.CheckBoxes.Add _
                               (Top:=.Top, Width:=.Width, _
                               Left:=.Left, Height:=.Height)
              With myCBX
                   .LinkedCell = myCell.Offset(0, 3).Address(external:=True) 'refs Q
                   .Caption = "" 'or whatever you want
               '.Name = "CBX_" & myCell.Address(0, 0)
               End With
             .NumberFormat = ";;;"
           End With
       Next myCell
   End With
End Sub

Hope that helps.

 

[picmaster]

picmaster,

Try....

Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range
    With ActiveSheet
       .CheckBoxes.Delete 'nice for testing
       For Each myCell In ActiveSheet.Range("N2:N10").Cells
          With myCell
           Set myCBX = .Parent.CheckBoxes.Add _
                               (Top:=.Top, Width:=.Width, _
                               Left:=.Left, Height:=.Height)
              With myCBX
                   .LinkedCell = myCell.Offset(0, 3).Address(external:=True) 'refs Q
                   .Caption = "" 'or whatever you want
               '.Name = "CBX_" & myCell.Address(0, 0)
               End With
             .NumberFormat = ";;;"
           End With
       Next myCell
   End With
End Sub

Hope that helps.

Where should I put these code in VBA? I try to put it in my "sheet1" and run it but it said "Compile error. A module is not a valid type."

I am totally new for VBA.

 

[Momentman]

1. Press Alt+F11 (this opens the visual basic editor)
2. In the window that appears(Visual basic editor), in the left pane, right click on your project(it should have the name of your workbook), select Insert>Module
3. Paste the code in there

 

[picmaster]

1. Press Alt+F11 (this opens the visual basic editor)
2. In the window that appears(Visual basic editor), in the left pane, right click on your project(it should have the name of your workbook), select Insert>Module
3. Paste the code in there

I still got the same error. this is the picture of message that i got

View image: DDD

 

[Momentman]

I still got the same error. this is the picture of message that i got

View image: DDD

I just tried the same code in a regular module and it worked

I see you have inserted a class module, what do you have in there? Maybe a conflict between what you have in the class module and this code is causing that error

I don't see a reason why this code should go into a class module but I suspect that it might work in there

 

[picmaster]

I class module I have

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'check cells for desired format to trigger the calendarfrm.show routine
'otherwise exit the sub
Dim DateFormats, DF
DateFormats = Array("m/d/yy;@", "dd/mm/yyyy")
For Each DF In DateFormats
If DF = Target.NumberFormat Then
If CalendarFrm.HelpLabel.Caption <> "" Then
CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
Else: CalendarFrm.Height = 191
CalendarFrm.Show
End If
End If
Next
End Sub

I use this for make Calendar pop up when I select the cell (choose date)

 

[Snakehips]

picmaster,


You have conflict in that you have a module named 'Checkbox'.
If you do not have Option Explicit declared then you could get away with leaving the module as Checkbox and removing the line 'Dim MyCBX As Checkbox'

However, I would suggest that you rename the module, then all should be fine.

 

[picmaster]

picmaster,


You have conflict in that you have a module named 'Checkbox'.
If you do not have Option Explicit declared then you could get away with leaving the module as Checkbox and removing the line 'Dim MyCBX As Checkbox'

However, I would suggest that you rename the module, then all should be fine.

Wow it just work perfectly!

Thank you so much !