link a VBA checkbox to a Cell to change value

[brianv]

I want to able to link a checkbox to a cell so that the cell value changes depending on the checkbox. Only 1 direction.

Now i know i can do this easy using the form control checkbox, but i cant change the physical size of the checkbox or the text style, and that check box is stupid small.

I know i can do this with the Insert | Control | Checkbox as well, and i can get that to work just great, but the only issue (minor I will agree) is that my cell formatting and the widths of the columns, make it so that the check boxes are not 'spaced' evenly, so it looks like **** (again i know, minor issue). But i may add columns to overcome this if needed.

But what i would really like, is a VBA form to open on a button, (i can do that), with the 6, 7 or what ever number of checkboxes that i need to control a cell value. The cell value is then used to filter a table of data, in this case 42 rows of wire types, and the check boxes will hide/unhide row or wire that is needed or not needed for electrical installation projects.

Its a quick way for designers to turn things on/off without scrolling though and adding 1 or 0 to those columns then filter.

So what i dont know is the VBA code so that checkbox 1 is linked to cells B5, B8, B9 or whatever cells to change its value to 1 or 0, 1 is using that cable on that row, 0 is not using it so its then hidden.

And finally, can i filter the rows and turn off (remove) the filter box in the header (not shown) and it still stay filtered?

Thank you!

 

[Micron]

What about this:
- you use textboxes instead; that way they can be as large as you want with any available font characteristics.

When you load the form set all to a checkbox font of your liking - e.g. ChrW(&H2713) Other colours/styles are available as well.
- user clicks textbox to remove that character (set value to ""). Unchecked means it will not be visible in a filtered list.
- tb can be linked to a cell so you can know what row that is. However, the linked cell will show a check mark, so make that a helper column with width = 0
- code could then hide that row on the sheet. I can't tell if it should do anything else, such as set a quantity to 0 or whatever.

I've found that you can't put your controls on a sheet as hiding a row disables the textbox. I have not done this so it's all speculation on my part but I think it would be doable.

 

[Micron]

Forgot to mention that the character number I posted is a check mark - I think it's a Wingdings font. That's how the check mark would get into the linked cell(s).

 

[brianv]

Im not sure I am following you...

Are you referring to FormControl text box, int he developer tab?
Or when i create a userfrom in VB, use a textbox in that form?

 

[Micron]

I'm referring to an ActiveX control (textbox) that normally goes on a userform because that is what I thought you referred to when you mentioned a form. Does that help?

 

[brianv]

Ok, so I went ahead and created a userform with a number of checkboxes, simply because i ended up with too many options to make all the selections look clean when inserted directly on the worksheet.

The Userform, "WireForm" opens on simple button:

I have each checkbox updating cells B5:B32 with either a "" empty cell (unchecked) or "1" (checked). Actually the checkboxs ControlSource is linked to P6:P23, then a IF statement in B6:B23 controls the 1 or 0.
Some of the checkbox control multiple cells, for ex: Checkbox12=P8, P9=P8, P12=P8... so if P8 is TRUE so is P9 & P12.

I don't want to use filters, because well, i have people at the office who just don't know how to use them, so i want the rows to hide as the checkbox is checked or unchecked.
I did find code to hide a row based on the value in each row, as the checkbox was selected, which was perfect, but i deleted it and cant find it any more, had something like:

Checkbox11.value=TRUE then
Hide row 5
else
Unhide row 5
end if

But i just cant find it anymore. The only catch i had was on checkbox11, it turn off cell B5 and turned on cell B6 and i could tell it to do both rows, its only worked on 1 row.

Alternate,
How do I call a Private Sub to run?
I do have code setup to hide rows based on a doubleclick on cell C3.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Const COL_VISIBLE = 10
    Const COL_HIDDEN = 0
    Dim lngCol As Long, _
        lngRow As Long, _
        i As Long
 
    'ignore clicks outside the target range
    If Intersect(Target, Range("C3")) Is Nothing Then Exit Sub
    lngCol = Target.Column
    lngRow = Range("C65536").End(xlUp).Row
 
    'show and hide columns and rows
    If Cells(1, lngCol).Value = "Show" Then
        For i = 2 To 2
            Columns(i).ColumnWidth = COL_VISIBLE
            ActiveSheet.AutoFilterMode = False
            Cells(1, lngCol).ClearContents
        Next i
    Else
        For i = 2 To 3
            If i = lngCol Then
                Columns(i).ColumnWidth = COL_VISIBLE
                Cells(1, lngCol).Value = "Show"
            Else
                Columns(i).ColumnWidth = COL_HIDDEN
            End If
            'With Range(Cells(5, lngCol), Cells(lngRow, lngCol))
            With Range("B3:B" & lngRow)
                .AutoFilter
                .AutoFilter Field:=1, Criteria1:="<>"
            End With
        Next i
    End If
End Sub

Any help would be great!

Thanks

 

[NoSparks]

i want the rows to hide as the checkbox is checked or unchecked.

Perhaps like this for every checkbox

Private Sub CheckBox1_Click()
    Hide_UnHide_Rows
End Sub

and the sub getting called

Sub Hide_UnHide_Rows()
Dim cel As Range, rng As Range

Set rng = Range("B5", Cells(Rows.Count, "B").End(xlUp))
Application.ScreenUpdating = False
rng.EntireRow.Hidden = False
For Each cel In rng
    If cel = "" Then cel.EntireRow.Hidden = True
Next cel
Application.ScreenUpdating = True
End Sub