Help! What kind of checkboxes to use that will sort with rest of data

[Cheryl]

Good day:

Could someone please tell me how to configure my checkboxes to move with a
data sort. I need to link the checkbox results (true, false) using linked
cell and have it shows todays date in a different cell. The date will not
change if the file is opened on another day. unless unchecked and checked
again. I try to sort my information but the checkboxes are not sorting with
the data. I am not sure whether to use the Control Toolbox checkbox or form
checkbox. I have tried both.. and neither seems to move when I sort... I can
make it show todays date and all. main problem is that when I sort the
data.. the information/checkboxes do not follow..

Thanks!

 

[Debra Dalgleish]

You can use combo boxes from the Control toolbox or from the
Forms toolbar.
Format the combo boxes to move with the cells (Format>Control)
The combo boxes should be fully contained within the cell on
which they're sitting.
When you sort the data, select all the columns, including the
column that contains the combo boxes.
Then, choose Data>Sort, and select your sort options.
The combo boxes should move with their rows.

 

[Cheryl]

Debra:

Thanks for the information...

So, combo boxes instead of checkboxes? My boss just wants to click on
something like a checkbox and have date automatically appear in next cell
when he did it...Checkboxes really are no good in a 'table' of information
then i take it... ? Just for forms and yes/no..(true/false).

Cheryl

 

[Debra Dalgleish]

Oops! Sorry -- just substitute 'check box' where I wrote 'combo box',
and the results should be the same.

 

[Dave Peterson]

And one more thing that bit me in the, well, never mind where it bit me!

Tools|options|Edit tab
make sure "Cut, copy, and sort objects with cells" is checked.

(I think I had a thread with the most number of replies to myself!)

 

[Dave Hawley]

Hi Cheryl

To have the date inserted into a cell upon checking a checkbox, add a
Checkbox from the "Forms" toolbar to the sheet. Now with the CheckBox
selected, click in the "Name Box", left of the Formula Bar. In here
type: DateAdd then push Enter.

Now push Alt+F11 and go to Insert>Module and in here paste

Sub AddDate()
If ActiveSheet.Shapes("DateAdd"). _
ControlFormat.Value = xlOn Then
Range("A1") = Date
End If
End Sub

Click the top right X to get back to Excel and Save. Now right click on
the CheckBox and click "Assign Macro" then click "AddDate" and click Ok.
Save again and test it out.

Range("A1") can of course be any cell.



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Cheryl]

Thank you all for the help.. it works now..

 

[Cheryl]

Dave:

I check off my Checkbox before sorting.. and it automatically shows the date
right beside it in the next cell .. When I sort... the checkboxes
move..(like they should.. and so does the date.. However, if I check off
other boxes when it is sorted.. the date appears in the original spot (where
it would have in the original before sorting.. It is as though I declared it
absolute but I did not.. How do I do it so no matter where it sorted to ..
the code knows to put the date in the cell beside it...

I used...

If ActiveSheet.Shapes("DateAdd"). _
ControlFormat.Value = xlOn Then
Range("j2") = Date
End If
If ActiveSheet.Shapes("DateAdd"). _
ControlFormat.Value = xlOff Then
Range("j2") = ""
End If

Thanks in advance..

 

[Dave Peterson]

If you position the checkbox nicely with in the cell, you can determine which
cell is its topleftcell.

Once you know this, you can use .offset() to go in any direction you want. I
chose to go to the left .offset(0,1)

I assigned this to my checkbox from the forms toolbar:

Option Explicit
Sub FormsChkBoxClick()

Dim myCBX As CheckBox

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

With myCBX.TopLeftCell.Offset(0, 1)
If myCBX.Value = xlOn Then
.NumberFormat = "mm/dd/yyyy"
.Value = Date
Else
.ClearContents
End If
End With

End Sub

I also clear the contents if the checkbox is not checked. Not sure if you
wanted that.

And you can assign this to as many forms checkboxes as you want.

 

[Dave Peterson]

er...

Once you know this, you can use .offset() to go in any direction you want. I
chose to go to the left .offset(0,1)

Change Left to Right in that sentence above!

CQC may be lurking looking for a reason to point and giggle.

 

[Debra Dalgleish]

Unfortunately, people who can't tell the difference between a combo box
and a check box, aren't allowed to mock others (in the same thread).

er...

Once you know this, you can use .offset() to go in any direction you want. I
chose to go to the left .offset(0,1)

Change Left to Right in that sentence above!

CQC may be lurking looking for a reason to point and giggle.

 

[Dave Peterson]

Woohoo for me!

Unfortunately, people who can't tell the difference between a combo box
and a check box, aren't allowed to mock others (in the same thread).

 

[Cheryl]

Thanks once again.. all

Will give that a try ... next.. You all are a great help...

Cheryl

 

[Cheryl]

To all:

Thanks for your help everyone.. It works the way I want now... You were
lifesavers...

Cheryl