Way to select / clear Excel Checkbox without using mouse to click on it ? (Long)
Angela
worksheet setup to be used as a contract that has 80 cells to fill in
and also 16 checkboxes in it. Most cells are locked (under Format
Cells - Protection) so I can tab through the contract to fill in
information only in the unlocked cells (such as name, address, phone,
etc). When I get to a cell that has a checkbox in it on the form (I
have left the cells that have checkboxes unlocked so that the cursor
would stop on that cell as I am tabbing along), I would like to be
able to select or clear the checkbox with a keyboard shortcut instead
of having to use the mouse to click on the checkbox. The checkboxes
were setup from the Forms toolbar, but I can change them to ActiveX
checkboxes from the Control toolbar if I have to (would prefer not to
have to change them!).
My question: Is it possible to select or clear checkboxes in Excel
without having to use the mouse to click on them? I hope this is
enough information to explain my problem. NOTE: I am not at all very
knowledgeable in Excel or VBA, so if your answer is technical, please
try to give it to me in step-by-step plain english if possible.
I have read on some message boards that I should be able to tab over
to the checkbox & hit the spacebar to change the checkbox. When I
tried that, it only put a space in the cell that the checkbox was
sitting in.
One idea I had while searching Excel & Visual Basic help that may be a
solution if this is possible ... is to change the value of the
checkbox so that instead of selected=TRUE and cleared=FALSE, change
each checkbox so that selected=Y and cleared=N, but I cannot figure
out how to do this or if it is possible. I got the idea under the
help heading CheckBox Control "Use a CheckBox to give the user a
choice between two values such as Yes/No, True/False, or On/Off. When
the user selects a CheckBox, it displays a special mark (such as an X)
and its current setting is Yes, True, or On; if the user does not
select the CheckBox, it is empty and its setting is No, False, or Off.
Depending on the value of the TripleState property, a CheckBox can
also have a null value."
I know that I can assign a cell link to a checkbox, and I also know
that I can keep a cell value hidden so that it doesn't print but only
shows up in the "formula bar" at the top. Then if I assigned the
checkbox cell link to be the cell that the checkbox is sitting in,
when I am tabbing along and it stops there, I could just type in "Y"
in that cell to select that checkbox.
I am sorry this is so long, but I wanted to explain my problem as
clearly as possible since I don't know the correct technical terms to
use. Thanks for your help!!!
Angela
Anders S
worksheet in the tabbing sequence of the unprotected cells. I haven't been
able to do that. Also, the checkboxes are not attached to the cell(s) they
happen to be placed above.
If you want an input form with textboxes and checkboxes that you can tab
(and shift-tab) between, as well as turn the checkboxes on and off with the
spacebar, you should try a UserForm.
It's quite easy to design a UserForm, and when closing it, and it's no big
deal to take care of the data.
Open the VBA editor (Alt + F11), select your workbook in the Project Browser
and do Insert - UserForm. Add a few controls from the Toolbox and run the
UserForm with F5.
Play around, have fun, and post again if you need help with the coding.
Anders
"Angela" <sno_b...@yahoo.com> skrev i meddelandet
news:8b1bde4b.03022...@posting.google.com...
Angela
lot in last 3 days! I did play with the UserForm for a
while, but could not figure it out. It even took me about
15mins to figure out how to delete it back out! LOL :(
Someone else emailed me a solution for changing the
checkbox value to Y or N instead of TRUE or FALSE. They
told me TRUE=1 and FALSE=0, so here is what I did ... (the
following is just in case any more *dummies* like me are
searching for a keyboard shortcut to checkboxes without
using the mouse to click on it - I'm sure you experts do
NOT need this explanation! But if there is a better way
than below, please post to me!!!)
HOW TO PUT CHECKBOXES ON YOUR WORKSHEET
1. make sure Forms toolbar is showing - click on View
menu, choose Toolbars, check Forms
2. click on icon that looks like little box with
checkmark in it, then click wherever on your sheet you
want a checkbox. the checkbox will be highlighted & you
can delete the words "CheckBox1" by clicking in the text.
I put my actual text in cells on the worksheet ... not in
the checkbox highlighted area
3. then drag the handlebars on the side to make
highlighted area smaller or larger (optional). You can
also move checkbox around by using arrow keys to "nudge"
it or with the mouse when you get 4 sided arrow, you can
drag the checkbox around on your sheet
3. right click on checkbox to highlight it or get the
dropdown menu. use copy & paste to paste checkboxes
wherever you need them.
HOW TO PROTECT YOUR WORKSHEET FROM CHANGES
4. protection default is already set to locked, so any
cells you want users to be able to type in need to be not
locked
5. to unlock protection, move your cursor to each cell
(or highlighted groups of cells) you want unlocked, click
on Format menu, choose cells, choose Protection tab,
uncheck "Locked", click OK
6. shortcut to do this for each cell you want unlocked:
before hitting any other keys other than mouse, arrows,
tab, or enter, move your cursor to each cell you want
unlocked & hit F4 (or you can just repeat step 5 for each
cell)
7. the checkboxes are not actually attached to the cells
they are sitting above, but if you want to be able to
check / clear without using the mouse, you must also
unlock the cells directly under the checkboxes
SETUP CHECKBOX TO CHECK OR CLEAR USING KEYSTROKES
8. for each checkbox you will have to right click, choose
Format Control on the menu that pops up, click on Control
tab, in Cell Link box type in the letter & number of the
cell directly under that checkbox (for example: C3), and
click OK
9. now click your cursor somewhere on the sheet & use
your arrows to move to that cell (C3). type in "1", hit
enter, and cell will check. type in "0", hit enter,and
cell will uncheck. (you could also key in the word "TRUE"
or "FALSE" to check/clear the checkbox, or click on the
checkbox with the mouse)
HIDE THE TYPING OF 1 OR 0 IN THE CELL
10. now it looks funny for 1 or 0 (or true/false) to be
there & you probably do NOT want it there so ...
11. move your cursor back to that cell (C3), click on
Format menu, choose cells, choose Number tab, under
Category choose Custom, in the Type box type in ;;; (three
semicolons), and click OK. Now the contents of the cell
are hidden (the words not the checkbox) and they only show
up in the "formula bar" at the top. this must be done for
each checkbox on the wroksheet or ...
12. shortcut to do this for each checkbox: before hitting
any other keys other than arrows, tab, or enter, move your
cursor to each cell that has a checkbox sitting over it
that you assigned a cell link to in step 7 & hit F4 (or
you can just repeat step 11 for each checkbox)
WANT TO TEST IT ? ENABLE PROTECT SHEET
13. click on Tools menu, choose Protection, choose
Protect Sheet, you do NOT have to assign a password if you
don't want one, click OK (NOTE: sheet is NOT protected
from changes until this is done. once it is protected,
you will get error message whenever you try tomake a
change where it is locked. to start making changes again,
you will have to unprotect sheet by clicking Tools menu-
Protection-Unprotect Sheet. you will have to protect
sheet again before you close Excel and save the changes
you have made)
14. test it by hitting the tab key along through your
worksheet, typing in 1 or 0 when you come to checkboxes
LAST STEP: INFORM OTHER USERS HOW TO CHECKBOXES (optional)
15. tab your cursor to the FIRST cell closest to top of
worksheet that contains a checkbox
16. click on Data menu, choose validation, choose Input
Message tab, make sure "show input message when cell is
selected" is checked (you may have to unprotect sheet as
in step 13)
17. in Title box type "How to Check Boxes", in Input
message box type "All Checkboxes can be checked by using
the mouse to click on them, or by typing in "1" to check
the box or "0" to uncheck the box", click OK
18. now when users tab to the first checkbox, they will
get instructions on how to checkboxes
19. That's It !!!
Thanks to everyone who has helped this newbie with this
problem or have helped me learn other things along the
way!!!
Angela
Dave Peterson
Format the cell/range so that it's using a wingdings font.
Then Format|Cells|Number tab|Custom
Give it a format like:
ş;;o;
I got the first funny symbol by holding the alt-key while I typed 0254 on the
numeric keyboard. the "o" is just lower case letter o. (oh!)
Format works like:
positive;negative;zero;text
So for positive numbers, I get a symbol that looks like a checkbox
I hide all negative numbers
I show an empty box for zeros
and I hide all text.
Then you should be able to refer to them by:
=if(a1>0,"then box is checked","box is not checked")
But if you clear contents of the cell, the box goes away.
--
Dave Peterson
ec3...@msn.com
Tom Ogilvy
I didn't see anything in your message which used Y or N? Did you mean 1 or
0 rather than Y or N?
Regards,
Tom Ogilvy
Angela <sno_b...@yahoo.com> wrote in message
news:024801c2daea$a3fe0010$a101...@phx.gbl...
Angela
My main objective was that when I tabbed to a cell that had a checkbox
in it, I wanted to be able to select or clear the checkbox with a
keyboard shortcut instead of having to use the mouse to click on the
somehow so that typing in "Y" would mean checked. The suggestion
someone emailed me was that I should just use 1 or 0 (still only one
keystroke) which is already setup in checkboxes to mean true or false.
Then I setup a validation message to tell users to type in 1 or 0 for
checkboxes.
Sorry if I was unclear in my explanation. Thank you (and everyone who
has helped me) for responding to help me figure this problem out. I
ended up with 4 other people along the way of my search that also
wanted to know how to do this if/when I found an answer!
Angela
"Tom Ogilvy" <twog...@msn.com> wrote in message news:<uVkVpk12...@TK2MSFTNGP09.phx.gbl>...
Angela
working on a laptop that only has numbers across the top - no number
keypad. When I try to put in a character using alt-####, my computer
just beeps at me for each number I try to key. If I want a special
character, I have to open the character map & copy it to put it in.
On every other computer I have ever worked on (desktops with
keyboards), I never had this problem.
Now to your answer ... are you saying that I would not use a checkbox
at all, but just do this instead? And I guess setup a validation
error message so users would not be allowed to enter anything except 1
or 0? It would force them to key an answer, because the mouse would
not be an option. It would just LOOK like a checkbox was there, but
it would actually be a wingding. Is there a way to NOT let the check
wingding go away? So that it can ONLY be check wingding or empty box
wingding, but not an empty cell (cleared cell)? I wouldn't want users
to be able to clear the cell so that there was no box at all in front
of the answers they have to choose from when checking.
> =if(a1>0,"then box is checked","box is not checked")
By this are you saying that the formulas I currently have that refer
to the checkbox cell link values, change them to refer to the cell
with the wingdings,or do you mean something totally different by "then
box is checked"? So for example,
if I had =IF(OR(A1=1,A1=TRUE),15,0)
then change it to =IF(A1>0,15,0) ?
This is an interesting way around my problem. AND I learned something
new about custom format being positive;negative;zero;text. I knew
that ;;; cleared the cell, but I never knew why!!! Thanks for your
help!
Angela
Dave Peterson <ec3...@msn.com> wrote in message news:<3E58EAB8...@msn.com>...
Dave Peterson
Don't most laptops have a function key (like Fn or a special key) that allows
you to use the keyboard (like UIO,JKL,NM...) to emulate the keypad?
There are events that excel monitors. One of them is when a worksheet changes.
If your user is typing in the cell, you can intercept anything they do and make
it your own.
Right click on the worksheet tab and select view code. then paste this in:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myColumn As Range
Dim myRange As Range
Set myColumn = Range("a:a")
Set myRange = Nothing
On Error Resume Next
Set myRange = Intersect(Target, myColumn)
On Error GoTo 0
If myRange Is Nothing Then Exit Sub
For Each myCell In myRange.Cells
Application.EnableEvents = False
If IsNumeric(myCell.Value) _
And myCell.Value <= 0 Then
myCell.Value = 0
Else
myCell.Value = 1
End If
Next myCell
errHandler:
Application.EnableEvents = True
End Sub
Have column A formatted like in the previous message.
Now when the user types anything into column A, the macro will check it. If
it's numeric and <=0, then it'll overwrite it to 0. Anything else (number or
text) gets a 1. When they use the delete key to clearcontents, excel's VBA
still sees that as numeric zero.
Even though myColumn is named like a column, you can set it any range you want
(range("a9:z99") would work.)
<<snipped>>
--
Dave Peterson
ec3...@msn.com