Checkboxes in Excel - ActiveX-based vs Form-based 2

[MattGreer]

I recreated a PDF form in Excel (Office 365) to make it easier for me to enter data, duplicate rows, etc. Part of the form uses checkboxes, so I added checkboxes accordingly using the Developer tab and picking from Form or ActiveX controls. I chose ActiveX controls. Unfortunately, after using this form that I recreated, there's a few hundred of these checkboxes and the spreadsheet has become nearly unusable due to the resources required on my part. I'm not sure why I chose ActiveX controls to be honest, but after messing with the form controls I think it's because I'm not able to cut and paste a Form-based checkbox and have it end up in the "right place". ActiveX controls seem to cut and paste more easily. I need to be able to cut and paste to add more entries as I add information to the spreadsheet.

The checkboxes don't "do" anything. They're just a checkbox to indicate "yes" or "no" for the reader.

The original PDF was not created as a form which is why I did this. I've attached the excel template for your viewing pleasure.

So the questions:
1. Is there a way to search/replace ActiveX checkboxes vs Form checkboxes?
2. Is it possible to create a template of a form and then cut/paste it into Excel using Form-based checkboxes?

That's all the questions I have. Hope everyone is having a good week!



Thanks!!


Matt

 

[SkipVought]

Matt,

Coupla options I've used.

The checkboxes don't "do" anything. They're just a checkbox to indicate "yes" or "no" for the reader.

So why use a Checkbox of any kind? I've used a Selection_Change event, so that when you select one cell in specified column(s) it either adds or deletes a check mark.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

The other technique I've used is to have ONE Checkbox Control that is hidden and becomes visible using the Selection_Change event in the appropriate column(s) that put a check mark in/or deleted from the selected cell.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

If the checkboxes do nothing, in fact you don't need them. Instead, you can use medium thickness borders, with dark and light gray colors to mimic 3-D view. Next, the checkmark either from wingdings font or borders again - two crossed lines. Even simpler, wingdings have empty box and checked characters.

combo

 

[MattGreer]

I'll say I like the functionality of the checkbox, click on/off and if I want to change the selection it's just as easy.

Skip, that's really interesting. I'll investigate that more. I wasn't aware that you could have programming on a specific cell like that. When I go to duplicate the rows, will the code duplicate itself as well? I'm thinking maybe not but this VBA functionality is definitely new to me.

combo, I'll check out the old wingdings checkmark.

I'll freely admit that I could just put an "X" in a cell and be done with it but I guess I preferred "form" over "function" in this case which isn't always the best idea, heh. I hate using the mouse, but in this case clicking on something like a checkbox seems easier than ctrl-c / ctrl-v and then go back and possibly have to go back and manually delete the source checkbox or something. Just trying to make this as easy and brain-dead as possible for the end-user (i.e. me, possibly others but not likely).

Thanks!!


Matt

 

[MattGreer]

So... I found this code, tried to run it, but when I do I get a pop-up box from the VBE that asks me for a macro to run. What's causing this? I saved the spreadsheet as an .xlsb due to the resources required. I put the code in a module rather than a specific worksheet, if that matters?

Sub Worksheet_SelectionChange(ByVal Target As Range)
    With ActiveWindow
        .ScrollRow = Target.Row
        .ScrollColumn = Target.Column
        Debug.Print Target.Column & ", " & Target.Row
    End With
End Sub

Thanks!!


Matt

 

[SkipVought]

You get these events as follows...

1) Right-Click the Sheet Tab
2) Select View Code
This is a Sheet Module.
(Above the Code Window are two Drop-Down boxes.
In the left Drop-Down select Worksheet
After you select Worksheet the right Drop-Down will list Events.)
You can past your event code in the Sheet Module and it will run when you make a new selection on your sheet.

Be sure you remove this code from where you originally placed it!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Skip,

I never knew those areas existed! Man, wonder what I can do with 'em, heh. For a long time I've been envious of Access' ability to run code based on selecting a field, deselecting a field, etc. Didn't know you cold do that in Excel. I'm loling as there's a whole new world that just opened up to me. I realize that might sound goofy but I can't tell you how man times I wish I could have done something based on clicking on a cell.

The conundrum I have now is how do I only allow the code to run in certain columns?

For now, this is what I have. I'd appreciate your expert eye on how it could be simplified/improved/etc. I suppose if I created a Named Range for the columns I could use that instead but, to be honest, I'm terrible with Ranges.

combo, thanks for the Wingdings suggestion. Works perfectly!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColumn As Integer
    With ActiveCell
        iColumn = Target.Column
        If iColumn = 3 Or iColumn = 5 Or iColumn = 9 Then
            .VerticalAlignment = xlCenter
            .HorizontalAlignment = xlCenter
            .Font.Name = "Wingdings"
            If .Value = "þ" Then
                .Value = ""
            Else
                .Value = "þ"
            End If
        Else
            Exit Sub
        End If
    End With
End Sub

Thanks!!


Matt

 

[SkipVought]

FAQ707-1945
In an Event procedure Target is used rather than ActiveCell.
You must NOT assume that the Selection (Target) is only one cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[s]Dim iColumn As Integer[/s]
    If Target.Count > 1 Then Exit Sub
    With [s]ActiveCell[/s]Target
        If Not Intersect(.Cells, Union(.Parent.Columns(3), .Parent.Columns(5))) Is Nothing Then
        
            .VerticalAlignment = xlCenter
            .HorizontalAlignment = xlCenter
            .Font.Name = "Wingdings"
            If .Value = "þ" Then
                .Value = ""
            Else
                .Value = "þ"
            End If
        Else
            Exit Sub
        End If
    End With
End Sub

BTW, in the event that a multiple cell Selection for Target is allowable, then you'll need to loop thru each cell in Target...

Dim t As Range
For Each t In Target
    With t
       'Your Target logic
    End With
Next

BTW2, in the Project Explorer, you'll see each Sheet Object and the ThisWorkbook Object. As you Select any of these Objects, in the Code Window you'll see any code for that object.

BTW3, a Sheet Object can be a Worksheet Object, Chart Object or Macro Sheet Object. A Sheet is not necessarily a Worksheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Can't say thank you enough Skip, much appreciated! Works perfectly.

One more question, if I may impose? If I click in the cell, I get the checkbox. But if I click in the same cell without moving the active cell or focus somewhere else, I can't remove the checkbox. So I have to click out of the cell, and then back in the cell.

Is it possible to add/remove a checkbox without having to click out of the cell?

Thank you for your help, again!

Thanks!!


Matt

 

[SkipVought]

We could add a step in the procedure at the end, that would Select some other cell, such as an adjacent cell...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    With Target
        If Not Intersect(.Cells, Union(.Parent.Columns(3), .Parent.Columns(5))) Is Nothing Then
        
            .VerticalAlignment = xlCenter
            .HorizontalAlignment = xlCenter
            .Font.Name = "Wingdings"
            If .Value = "þ" Then
                .Value = ""
            Else
                .Value = "þ"
            End If
        Else
            Exit Sub
        End If
        [b]Application.EnableEvents = False
        .Offset(0,1).Select
        Application.EnableEvents = True[/b]
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Nifty! Thanks again!

Thanks!!


Matt