I have a worksheet with data that I build and sort with macros to present the
data in different desirable fashion. Each line of the data has a check box
to allow a flag to be set by the user. Although the check box moves with the
data, the linked cell stays constant. As such, I need to delete all of the
check boxes, sort the data, and then recreate the check boxes. This works
but is a little time consuming. Is there a way to have the linked cell move
with the check box? For what it is worth, the cell is directly under the
check box.

Thanks

How about an alternative?

Just use a cell in the same row, but format it to show a checkmark when there's
something in it?

I find this technique pretty easy...

Select the range
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.

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")

And since this is just data, it'll sort with the other cells.

ZipCurs wrote:

I have a worksheet with data that I build and sort with macros to present the
data in different desirable fashion. Each line of the data has a check box
to allow a flag to be set by the user. Although the check box moves with the
data, the linked cell stays constant. As such, I need to delete all of the
check boxes, sort the data, and then recreate the check boxes. This works
but is a little time consuming. Is there a way to have the linked cell move
with the check box? For what it is worth, the cell is directly under the
check box.

Thanks

--

Dave Peterson

Dave,

Thanks for your response. Unfortunately, it is not quite what I was looking
for. I actually want the user to be able to use the check box to change a
setting for that line of data.

I ended up using a sledgehammer approach and linked the check boxes to a
part of the worksheet that was not being sorted. Not pretty, but it works.
Please let me know if you have a more elegant solution.

"Dave Peterson" wrote:

How about an alternative?

Just use a cell in the same row, but format it to show a checkmark when there's
something in it?

I find this technique pretty easy...

Select the range
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.

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")

And since this is just data, it'll sort with the other cells.

ZipCurs wrote:

I have a worksheet with data that I build and sort with macros to present the
data in different desirable fashion. Each line of the data has a check box
to allow a flag to be set by the user. Although the check box moves with the
data, the linked cell stays constant. As such, I need to delete all of the
check boxes, sort the data, and then recreate the check boxes. This works
but is a little time consuming. Is there a way to have the linked cell move
with the check box? For what it is worth, the cell is directly under the
check box.

Thanks

--

Dave Peterson

How about dropping the linked cells altogether.

And if you used checkboxes from the Forms toolbar, you could assign the same
macro to each checkbox that plops true/false into the cell that is below the
checkbox.

Option Explicit
Sub CBX_Click()
Dim CBX As CheckBox
Set CBX = ActiveSheet.CheckBoxes(Application.Caller)
CBX.TopLeftCell.Value = CBool(CBX.Value = xlOn)
'CBX.TopLeftCell.NumberFormat = ";;;"
End Sub



ZipCurs wrote:

Dave,

Thanks for your response. Unfortunately, it is not quite what I was looking
for. I actually want the user to be able to use the check box to change a
setting for that line of data.

I ended up using a sledgehammer approach and linked the check boxes to a
part of the worksheet that was not being sorted. Not pretty, but it works.
Please let me know if you have a more elegant solution.

"Dave Peterson" wrote:

How about an alternative?

Just use a cell in the same row, but format it to show a checkmark when there's
something in it?

I find this technique pretty easy...

Select the range
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.

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")

And since this is just data, it'll sort with the other cells.

ZipCurs wrote:

I have a worksheet with data that I build and sort with macros to present the
data in different desirable fashion. Each line of the data has a check box
to allow a flag to be set by the user. Although the check box moves with the
data, the linked cell stays constant. As such, I need to delete all of the
check boxes, sort the data, and then recreate the check boxes. This works
but is a little time consuming. Is there a way to have the linked cell move
with the check box? For what it is worth, the cell is directly under the
check box.

Thanks

--

Dave Peterson


--

Dave Peterson