associate "pick box"

[Richard R]

I need a bit of VBA code I believe or an inventive way to make "pick boxes"
associative, just like a cell would be if you dragged accross a field. what
is happening to me is that the new pick boxes all refer back to the original
true false field, leaving my data selection random.

 

[Gord Dibben]

What is a "pick box"?


Gord Dibben MS Excel MVP

 

[Richard R]

If you wish to present a user with a restricted choice of options and don't
want to risk an item being miss-typed in a cell, drop down lists are an ideal
solution. These lists display all of the available choices to the user so
that they can click on their preference. Excel allows you to place two
different types of drop down list on your worksheet - either a validation
list or a form object.

 

[Dave Peterson]

The code used would depend on what you're using.

If you're using Data|Validation, a combobox from the Control toolbox toolbar or
a dropdown from the Forms toolbar, then the code would be different.

If you used Data|Validation, you could use a worksheet event (depending on the
version of excel that you have to support) to clear the contents of the cells
that are "down the chain" from that cell.

This looks at changes in A11.

If the change is made in A1, then B11 is cleared.
If the change is made in B1, then C11 is cleared.
If the change is made in C1, then D1 is cleared.

Since this is a worksheet event, the code is placed in the worksheet module that
should have this behavior.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub 'single cell at a time
End If

On Error GoTo ErrHandler:
With Target
If Not (Intersect(.Cells, Me.Range("A1")) Is Nothing) Then
'change in A1, clear B11 (say)
Application.EnableEvents = False
Me.Range("b11").ClearContents
ElseIf Not (Intersect(.Cells, Me.Range("b1")) Is Nothing) Then
'change in b1, clear c11 (say)
Application.EnableEvents = False
Me.Range("c11").ClearContents
ElseIf Not (Intersect(.Cells, Me.Range("c1")) Is Nothing) Then
'change in c1, clear D1 (say)
Application.EnableEvents = False
Me.Range("D1").ClearContents
Else
'do nothing
End If
End With

ErrHandler:
On Error Resume Next
Application.EnableEvents = True

End Sub

 

[Richard R]

The code used would depend on what you're using.

If you're using Data|Validation, a combobox from the Control toolbox toolbar or
a dropdown from the Forms toolbar, then the code would be different.

If you used Data|Validation, you could use a worksheet event (depending on the
version of excel that you have to support) to clear the contents of the cells
that are "down the chain" from that cell.

This looks at changes in A11.

If the change is made in A1, then B11 is cleared.
If the change is made in B1, then C11 is cleared.
If the change is made in C1, then D1 is cleared.

Since this is a worksheet event, the code is placed in the worksheet module that
should have this behavior.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub 'single cell at a time
End If

On Error GoTo ErrHandler:
With Target
If Not (Intersect(.Cells, Me.Range("A1")) Is Nothing) Then
'change in A1, clear B11 (say)
Application.EnableEvents = False
Me.Range("b11").ClearContents
ElseIf Not (Intersect(.Cells, Me.Range("b1")) Is Nothing) Then
'change in b1, clear c11 (say)
Application.EnableEvents = False
Me.Range("c11").ClearContents
ElseIf Not (Intersect(.Cells, Me.Range("c1")) Is Nothing) Then
'change in c1, clear D1 (say)
Application.EnableEvents = False
Me.Range("D1").ClearContents
Else
'do nothing
End If
End With

ErrHandler:
On Error Resume Next
Application.EnableEvents = True

End Sub


.
Dave, thanks, we seem to be going down the right path.

I am not so much trying to “clear†check boxes as much as creating them. I
want to create a form based check box that fits a cell. This box should then
be formatted to the cell to the right of it. (respond true false). And I
need to make a bunch of these little boxes. Several hundred. There will be
multiple checked boxes for a given column, though this app you described is
something I may need for another part of the spreadsheet.

 

[Richard R]

Dave, thanks, we seem to be going down the right path.

I am not so much trying to “clear†check boxes as much as creating them. I
want to create a form based check box that fits a cell. This box should then
be formatted to the cell to the right of it. (respond true false). And I
need to make a bunch of these little boxes. Several hundred. There will be
multiple checked boxes for a given column, though this app you described is
something I may need for another part of the spreadsheet. (sorry, I put
reply in wrong place, here it is again.)

 

[Dave Peterson]

I'm confused.

Do you want comboboxes/dropdowns/Data|Validation or do you want checkboxes (from
the Forms toolbar or from the control toolbox toolbar).

You may find that just using a font and a numberformat sufficient.

I like this technique:

(saved from a previous post)

Select the range that would have held the checkboxes.
Format|cells|number tab|custom category
In the "type:" box, put this:
alt-0252;alt-0252;alt-0252;alt-0252

But hit and hold the alt key while you're typing the 0252 from the numeric
keypad.

It should look something like this when you're done.
ü;ü;ü;ü
(umlaut over the lower case u separated by semicolons)

And format that range of cells as Wingdings (make it as large as you want)

Now, no matter what you type (spacebar, x, anyoldtextatall), you'll see a check
mark.

Hit the delete key on the keyboard to clear the cell.

If you have to use that "checkmark" in later formulas:
=if(a1="","no checkmark","Yes checkmark")
or
=counta(a1:a10)
to get the number of "checked" cells in A1:A10

Or you can filter by blanks and non-blanks.




==========
But if you want checkboxes from the Forms toolbar, you could use something like
this to add them to the sheet (also saved from a previous post):

Option Explicit
Sub CellCheckbox()
Dim myCell As Range
Dim myRng As Range
Dim CBX As CheckBox

With ActiveSheet
.CheckBoxes.Delete 'nice for testing!
Set myRng = .Range("A1:A10") 'change to the range you want
End With

For Each myCell In myRng.Cells
With myCell
Set CBX = .Parent.CheckBoxes.Add _
(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)
CBX.Name = "CBX_" & .Address(0, 0)
CBX.Caption = "" 'or what you want
CBX.Value = xlOff

'I like to use the same cell as the linked cell
'with a number format of ;;;
'I can't see the true/false in the worksheet, but it
'still appears in the formula bar
CBX.LinkedCell = .Address(external:=True)
.NumberFormat = ";;;"
End With
Next myCell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

=============

For ease of use (like sorting, totaling, inserting/deleting rows), I don't think
you can beat just using the font and numberformat.

 

[Richard R]

Dave : thanks...got it working like a charm...now I just need to know how to
do the same kind of thing with a combo box. There is always something.
Thanks again for the help!

 

[Dave Peterson]

If you used comboboxes from the control toolbox toolbar, you could have the
_change event associated with each combobox clear the subsequent comboboxes.