I want to create a check list with a count of entries at bottom.

[Bupkus]

I have a table of 25 columns and a variable number of rows. Each row
represents a person and each column represents an activity. If person has
requested an activity then user mouse clicks in cell to mark person to that
activity. Bottom row has totals for each activity.
I would like a checkbox matrix where cells with checked box are counted in
the Totals row.
I'm over my head here and time doesn't allow for me any more research.
Thanks.

 

[Bupkus]

Actually I don't really need checkboxes. I just thought of that as one way to
mark an entry with a single mouse click. If a mouse click entered an "X" that
would be great too. I tried using the CountA function for totalling a count
but if a user accidentally enters a space char that would be counted too and
I don't know how to restrict entries to "x" only.
Easiest for user, naturally, would be a mouse click that enters an "x" char.

 

[Susan]

just put an "x" in each activity column/row.
at the bottom of each column (or at the end of each row, if you'd
rather) have a counta formula:

=counta(a2:a25)

that will give you the # of x's in each row.
OH WAIT, that won't work. because it will also count the wording
you've got there for either name or activity........

try this instead:

=countif(a2:d2,"x") this is for rows
=countif(a2:a25,"x") this is for columns

now it will only count the x's.
hope it helps (or at least gets you started!)

susan

 

[Susan]

oh. for 4:01pm on xmas eve, you're asking for quite a task!
you'll need either a forms checkbox on each row/column junction, OR
some sort of a macro that will insert an "x" when the mouse is
clicked. i can't think of how you'd do #2 quickly, and #1 isn't that
quick, either............

but for #1, (do you have to have this for today?!?!?!), on your
toolbar use View, Toolbars, Control Toolbox.
that will open a little toolbar & you'll see a square box with a
checkmark in it. if you hover over it it'll say "checkbox".

if you click on it, you can draw a checkbox where you need it on the
worksheet. unfortunately, you're going to have to do that for every
single row/column junction where you'd like somebody to be able to
place a check. i'm assuming there's quite a few places.........

anyway, when you get all done with THAT, there's another little
rectangular button on that toolbar that's called "Command Button" when
you hover over it. click on that & draw yourself a button somewhere
on your spreadsheet. you'll have to have that command button run a
little macro that will total up the number of checked checkboxes.

i can write the macro for you but i can't do it tonight. some guru-
type-person could probably whip it up in 5 minutes, but it'd take me
about a half an hour to get it working properly & test it. i can work
on it tomorrow & have it for you on wednesday......... about the time
you get all those little checkboxes placed properly.

susan

 

[Bupkus]

That's great, thanks.
However, how can I restrict the user from accidently entering a space char
or any other char while I'm asking. I would like a Verification warning
atleast.
BTW, no row totals in right most column is needed.
Thanks again.

 

[Bupkus]

Correction: Validation against anything except "x" is what I meant.

 

[Susan]

yes.... up in the toolbar - Data - Validation

use "allow custom" & set the box at the bottom called "formula" to be

="x"

so it has to be that. then there's a tab where you can write a
warning message telling people that only an "x" can be entered there.

then you can copy that cell to all the other cells at the row/column
junction where you would want an "x".

i'll whip up that macro just in case you decide you want it on
wednesday (i don't have internet @ home, sorry). if you don't, no
problem, i like to write macros.
susan

 

[Gord Dibben]

To allow only an "x" to be entered use Data>Validation>Allow>Custom

=cellref="x"

Select a range of cells first so that validation will be on all the cells.

If you want worksheet event code that will enter an "x" upon a double-click on a
cell try this.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column < 26 Then
n = Target.Row
If Target.Value = "" Then
Target.Value = "x"
End If
End If
Cancel = True
enditall:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.


Gord Dibben MS Excel MVP

 

[Teethless mama]

select your data range (eg. A1:A10)

Data > Validation > Allow: select Custom > Formula: =A1="x" > OK out

 

[Gord Dibben]

In order to have formulas in columns we have to change the range.

I am assuming row 20 as last cell to be checkmarked. Adjust to suit.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "A1:Y20"
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Target.Value = "x"
End If
Cancel = True
enditall:
Application.EnableEvents = True
End Sub

Select A1:Y20 and add the Data Validation to those cells.

In A21 enter =COUNTIF(A1:A20,"x")

Drag/copy across to Y21


Gord

 

[Bupkus]

Before this post I tried "custom" =NOT("X") and it taught me to try =("x").
It worked until I tried to paste special "validation". Then all other cells
failed to work.

 

[Bupkus]

mama, you hit it on the nail head.
Thanks

select your data range (eg. A1:A10)

Data > Validation > Allow: select Custom > Formula: =A1="x" > OK out

 

[Susan]

btw, if you want, here's that counting checkboxes code. sounds like
you don't need it, but i'm throwing it in anyway just in case (& for
future searchers who might run across this post).

======================
Option Explicit

Private Sub CommandButton1_Click()

Dim wks As Worksheet
Dim oleobj As OLEObject
Dim mynumber As Integer

Set wks = ActiveSheet
mynumber = 0

For Each oleobj In wks.OLEObject
If TypeOf oleobj.Object Is msforms.CheckBox Then
If oleobj.Object.Value = True Then
mynumber = mynumber + 1
End If
End If
Next oleobj

MsgBox "the number of checked boxes is " & mynumber

End Sub
=========================
susan