help with macro for checkboxes

[Dmhs]

I would like to create automatically a checkbox per row but also if
the checkbox is selected I want to hide columns J to K, I got this
code but I don't know how to change it to put the condition about
hiding the columns.
could anybody help me...

this is the code :

Sub add_checkbox()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width,
c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With

c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6 'change
for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 6
'change for other color when ticked
.Font.ColorIndex = 2 'cell background color =
White
'I think here should be the hiding option but i
don't know how to write it
End With


Next
myRange.Select

End Sub


regards,

DIANA

 

[FSt1]

I would like to create automatically a checkbox per row but also if
the checkbox is selected I want to hide columns J to K, I got this
code but I don't know how to change it to put the condition about
hiding the columns.
could anybody help me...

this is the code :

Sub add_checkbox()
    On Error Resume Next
    Dim c As Range, myRange As Range
    Set myRange = Selection
    For Each c In myRange.Cells
        ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width,
c.Height).Select
            With Selection
                .LinkedCell = c.Address
                .Characters.Text = ""
                .Name = c.Address
            End With

            c.Select
                With Selection
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlExpression,
Formula1:="=" & c.Address & "=TRUE"
                    .FormatConditions(1).Font.ColorIndex = 6 'change
for other color when ticked
                    .FormatConditions(1).Interior.ColorIndex = 6
'change for other color when ticked
                    .Font.ColorIndex = 2 'cell background color =
White
                     'I think here should be the hiding option but i
don't know how to write it
                End With

        Next
         myRange.Select

End Sub

regards,

DIANA

hi
personally i don't think you can add that code to the check box in the
way you want. to hide and unhide columns, you would have to use the
check box click event to trigger the hide/unhide code to run. the code
would have to reside in each check box which would be independent of
the code that adds the check boxes. I suppose you could program the vb
editor to add the code to each check box but i don't know how to do
that. i do know it can be a real pain depending on what you are doing.
you might check this site out for more info...

http://cpearson.com/excel/vbe.aspx

here is code i use for one of our engineering files. but i only have 1
check box.
it not only hides/unhides a column, it also changes the color and
captions of the check box. make adjustments as needed.
Private Sub ChBx1_Click()
If Columns("C:C").Hidden = True Then
Columns("C:C").Hidden = False
ChBx1.BackColor = RGB(0, 0, 255)
ChBx1.ForeColor = RGB(245, 245, 5)
ChBx1.Caption = "Metric"
Columns("D").Hidden = True
Else
Columns("C:C").Hidden = True
Columns("D").Hidden = False
ChBx1.BackColor = RGB(245, 30, 5)
'ChBx1.ForeColor =
ChBx1.Caption = "Standard"
End If
End Sub

regards
FSt1

 

[Don Guillett Excel MVP]

hi
personally i don't think you can add that code to the check box in the
way you want. to hide and unhide columns, you would have to use the
check box click event to trigger the hide/unhide code to run. the code
would have to reside in each check box which would be independent of
the code that adds the check boxes. I suppose you could program the vb
editor to add the code to each check box but i don't know how to do
that. i do know it can be a real pain depending on what you are doing.
you might check this site out for more info...

http://cpearson.com/excel/vbe.aspx

here is code i use for one of our engineering files. but i only have 1
check box.
it not only hides/unhides a column, it also changes the color and
captions of the check box. make adjustments as needed.
Private Sub ChBx1_Click()
 If Columns("C:C").Hidden = True Then
        Columns("C:C").Hidden = False
        ChBx1.BackColor = RGB(0, 0, 255)
        ChBx1.ForeColor = RGB(245, 245, 5)
        ChBx1.Caption = "Metric"
        Columns("D").Hidden = True
        Else
            Columns("C:C").Hidden = True
            Columns("D").Hidden = False
            ChBx1.BackColor = RGB(245, 30, 5)
            'ChBx1.ForeColor =
            ChBx1.Caption = "Standard"
 End If
End Sub

regards
FSt1- Hide quoted text -

- Show quoted text -

I'm not quite sure what you want because you can only hide entire
columns, not columns in a row. This code will color the cell yellow
and hide columns J:K if anything is inputed into the cell. Empty the
cell for no color and unhide.
No checkbox overhead needed. Right click sheet tab>view code>insert
this

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("b2:b22")) Is Nothing _
Or Target.Count > 1 Then Exit Sub
With Target
If Len(Application.Trim(Target)) < 1 Then
.Interior.ColorIndex = 0
Columns("j:k").Hidden = False
Else
.Interior.ColorIndex = 6
Columns("j:k").Hidden = True
End If
End With
End Sub

 

[Dmhs]

I'm not quite sure what you want because you can only hide entire
columns, not columns in a row. This code will color the cell yellow
and hide columns J:K if anything is inputed into the cell. Empty the
cell for no color and unhide.
No checkbox overhead needed. Right click sheet tab>view code>insert
this

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("b2:b22")) Is Nothing _
Or Target.Count > 1 Then Exit Sub
With Target
If Len(Application.Trim(Target)) < 1 Then
  .Interior.ColorIndex = 0
Columns("j:k").Hidden = False
Else
  .Interior.ColorIndex = 6
Columns("j:k").Hidden = True
End If
End With
End Sub- Hide quoted text -

- Show quoted text -

thanks for your answer, well I will explain it better, I know how to
hide a column if I select the checkbox needed and show the column if
the checkbox is not selected, the thing is I don't know how to a apply
that code to all the checkboxes in my sheet, that's why I used first
the automatic creation of checkboxes but now I am stuck because I
don't know how to insert the condition of hiding or not. (I want to
hide the whole columns K:M).

best,
Diana

 

[Dmhs]

hi
personally i don't think you can add that code to the check box in the
way you want. to hide and unhide columns, you would have to use the
check box click event to trigger the hide/unhide code to run. the code
would have to reside in each check box which would be independent of
the code that adds the check boxes. I suppose you could program the vb
editor to add the code to each check box but i don't know how to do
that. i do know it can be a real pain depending on what you are doing.
you might check this site out for more info...

http://cpearson.com/excel/vbe.aspx

here is code i use for one of our engineering files. but i only have 1
check box.
it not only hides/unhides a column, it also changes the color and
captions of the check box. make adjustments as needed.
Private Sub ChBx1_Click()
 If Columns("C:C").Hidden = True Then
        Columns("C:C").Hidden = False
        ChBx1.BackColor = RGB(0, 0, 255)
        ChBx1.ForeColor = RGB(245, 245, 5)
        ChBx1.Caption = "Metric"
        Columns("D").Hidden = True
        Else
            Columns("C:C").Hidden = True
            Columns("D").Hidden = False
            ChBx1.BackColor = RGB(245, 30, 5)
            'ChBx1.ForeColor =
            ChBx1.Caption = "Standard"
 End If
End Sub

regards
FSt1- Hide quoted text -

- Show quoted text -

Thanks for your help, and yes that's my problem i don't want to go to
each checkbox and assign it the macro that hides columns, maybe there
is a way so I can write a code for all the checkboxes, or something
like that.

Let me know if you find a way,

Best,
Diana

 

[dk]

Thanks for your help, and yes that's my problem i don't want to go to
each checkbox and assign it the  macro that hides columns, maybe there
is a way so I can write a code for all the checkboxes, or something
like that.

Let me know if you find a way,

Best,
Diana- Hide quoted text -

- Show quoted text -

You wanted to add check boxes as well as code for the added check
boxes right?

You have to use VB IDE. Requires digital signature setup too.

 

[Dave Peterson]

This may get you closer.

Option Explicit
Sub add_checkbox()
Dim c As Range
Dim myRange As Range
Dim CBX As CheckBox
Set myRange = Selection
For Each c In myRange.Cells
Set CBX = ActiveSheet.CheckBoxes.Add _
(c.Left, c.Top, c.Width, c.Height)
With CBX
.LinkedCell = c.Address
.Caption = ""
'don't use names that look like addresses!
.Name = "CBX_" & c.Address(0, 0)
.OnAction = "'" & ThisWorkbook.Name & "'!testme"
End With
Next c

End Sub
Sub testme()
Dim CBX As CheckBox
Set CBX = ActiveSheet.CheckBoxes(Application.Caller)

If CBX.Value = xlOn Then
MsgBox "Checked!" & vbLf _
& CBX.Name & vbLf _
& CBX.TopLeftCell.Address
Else
MsgBox "not checked"
End If
End Sub