Problem vertically aligning multiple rows with tickboxes added usingVB.

Hi guys.

I searched for days to find the solution to adding a column of
checkboxes down a table with 500 rows in it - a checkbox at the end of
each row. The good news is that I eentually found that someone had
posted a little VB code that did it nicely. I used some additional
conditional formatting and now each individual row changes colour when
the checkbox is ticked! It's a thing of beauty to a new user!

The trouble is that when you get 100 or so rows down the table the
checkboxes gets incrementally and noticeably out of alignment with the
rows. I guess that a tiny vertical misalignment accumulates. By the
time you get to the 500th checkbox, they have run out the bottom of the
table, and gone well below it, and are completely out of vertical
alignment by about 10 rows!

Does anyone have any idea on how to align the checkboxes correctly to
each cell they are sitting above? Or to put it another way; to each row
they should relate to?

Is there some kind of tweak to the VB code that will make them "snap" to
a cell as the script runs? Is there another completely different
solution to get a checkbox-like cell at the end of every row?

Thanks for any help with this
--
Westie

Missed the post earlier with the VB code

Not sure why you are using VB code - A WinDing character set has a tick =
in =

it.

A little macro
Sub Char_Tick()
'
' Put a Tick into current cell
' Use 251 for a Cross
ActiveCell.Font.Name =3D "Wingdings"
ActiveCell.FormulaR1C1 =3D "=3DCHAR(252)"
End Sub

This then will obviously be a standard cell and won't need any special =

aligning

Steve

On Wed, 26 Jul 2006 07:58:02 +0100, Westie <[email protected]>=
=

wrote:

> Hi guys.
>
> I searched for days to find the solution to adding a column of =

> checkboxes down a table with 500 rows in it - a checkbox at the end of=
=

> each row. The good news is that I eentually found that someone had =

> posted a little VB code that did it nicely. I used some additional =

> conditional formatting and now each individual row changes colour when=
=

> the checkbox is ticked! It's a thing of beauty to a new user!
>
> The trouble is that when you get 100 or so rows down the table the =

> checkboxes gets incrementally and noticeably out of alignment with the=
=

> rows. I guess that a tiny vertical misalignment accumulates. By the =
=

> time you get to the 500th checkbox, they have run out the bottom of th=
e =

> table, and gone well below it, and are completely out of vertical =

> alignment by about 10 rows!
>
> Does anyone have any idea on how to align the checkboxes correctly to =
=

> each cell they are sitting above? Or to put it another way; to each r=
ow =

> they should relate to?
>
> Is there some kind of tweak to the VB code that will make them "snap" =
to =

> a cell as the script runs? Is there another completely different =

> solution to get a checkbox-like cell at the end of every row?
>
> Thanks for any help with this
> --
> Westie

Thanks for replying, Steve.
I didn't post the code originally. I was intending to keep my post
short, but here it is:

.................................
You're not going to believe this. I was just explaining to my wife what
the problem was as I was finishing this post off, and it's all working
and aligned just fine now when I went to show her. I don't know what
exactly happened. I reopened the closed worksheet and it worked just
fine. Maybe I did something before I closed it last time and I sent
myself on a wild goose chase?! Anyway, feel free to read the rest of it
since I typed it out! LOL! I feel stupid now.
.................................

Option Explicit
Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
..CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("H7:H500").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
..LinkedCell = myCell.Offset(0, 4).Address(external:=True)
..Caption = ""
..Name = "CBX_" & myCell.Address(0, 0)
End With
End With
Next myCell
End With
End Sub


You can see that I'm adding the tickbox from the forms toolbar - it can
be actively ticked or unticked. What do you call it? An object?
The control is linked to a target cell. That triggers my conditional
formatting colour change depending on the TRUE or FALSE result from the
checkbox. It's not the wingding font checkbox character that I'm using.

If you throw this macro into a sheet and run it, you'll see my problem.
It seems that "objects" float above the worksheet and it's tricky to get
them precisely aligned to cells - particularly when you use code to
insert 500 of them.

Unless the row heights are set to exactly whatever the vertical spacing
between the checkboxes is, they incrementally get out of alignment with
the rows. I could probably get away with this if I could match the row
height to the height between checkboxes - but I can't get it right. At
least not for 500 of the suckers. I've played around with different row
heights but they all seem to go out of alignment at some point
regardless of what I do. I need a way to keep the rows AND the
checkboxes aligned.

--
Westie


SteveW wrote:
> Missed the post earlier with the VB code
>
> Not sure why you are using VB code - A WinDing character set has a tick
> in it.
>
> A little macro
> Sub Char_Tick()
> '
> ' Put a Tick into current cell
> ' Use 251 for a Cross
> ActiveCell.Font.Name = "Wingdings"
> ActiveCell.FormulaR1C1 = "=CHAR(252)"
> End Sub
>
> This then will obviously be a standard cell and won't need any special
> aligning
>
> Steve
>

Ok, see the code - I guessed as much that you were using some object.

Only thing I can think of is when creating the object hold the ALT key =

down, this will *snap* the object to the top left of the cell.

The other thing is to make sure the object has the *Move and Size with =

cell* attribute
check properties

Steve


On Wed, 26 Jul 2006 10:52:09 +0100, Westie <[email protected]>=
=

wrote:

> Thanks for replying, Steve.
> I didn't post the code originally. I was intending to keep my post =

> short, but here it is:
>
> ................................
> You're not going to believe this. I was just explaining to my wife wh=
at =

> the problem was as I was finishing this post off, and it's all working=
=

> and aligned just fine now when I went to show her. I don't know what =

> exactly happened. I reopened the closed worksheet and it worked just =
=

> fine. Maybe I did something before I closed it last time and I sent =

> myself on a wild goose chase?! Anyway, feel free to read the rest of i=
t =

> since I typed it out! LOL! I feel stupid now.
> ................................
>
> Option Explicit
> Sub addCBX()
> Dim myCBX As CheckBox
> Dim myCell As Range
>
> With ActiveSheet
> .CheckBoxes.Delete 'nice for testing
> For Each myCell In ActiveSheet.Range("H7:H500").Cells
> With myCell
> Set myCBX =3D .Parent.CheckBoxes.Add _
> (Top:=3D.Top, Width:=3D.Width, _
> Left:=3D.Left, Height:=3D.Height)
> With myCBX
> .LinkedCell =3D myCell.Offset(0, 4).Address(external:=3DTrue)
> .Caption =3D ""
> .Name =3D "CBX_" & myCell.Address(0, 0)
> End With
> End With
> Next myCell
> End With
> End Sub
>
>
> You can see that I'm adding the tickbox from the forms toolbar - it ca=
n =

> be actively ticked or unticked. What do you call it? An object?
> The control is linked to a target cell. That triggers my conditional =
=

> formatting colour change depending on the TRUE or FALSE result from th=
e =

> checkbox. It's not the wingding font checkbox character that I'm usin=
g.
>
> If you throw this macro into a sheet and run it, you'll see my problem=
..
> It seems that "objects" float above the worksheet and it's tricky to g=
et =

> them precisely aligned to cells - particularly when you use code to =

> insert 500 of them.
>
> Unless the row heights are set to exactly whatever the vertical spacin=
g =

> between the checkboxes is, they incrementally get out of alignment wit=
h =

> the rows. I could probably get away with this if I could match the ro=
w =

> height to the height between checkboxes - but I can't get it right. A=
t =

> least not for 500 of the suckers. I've played around with different r=
ow =

> heights but they all seem to go out of alignment at some point =

> regardless of what I do. I need a way to keep the rows AND the =

> checkboxes aligned.
>
> --
> Westie
>
>
> SteveW wrote:
>> Missed the post earlier with the VB code
>> Not sure why you are using VB code - A WinDing character set has a =

>> tick in it.
>> A little macro
>> Sub Char_Tick()
>> '
>> ' Put a Tick into current cell
>> ' Use 251 for a Cross
>> ActiveCell.Font.Name =3D "Wingdings"
>> ActiveCell.FormulaR1C1 =3D "=3DCHAR(252)"
>> End Sub
>> This then will obviously be a standard cell and won't need any =

>> special aligning
>> Steve
>>

OK, once again, thanks for the help. Luckily this is not a majorly
urgent project so I'll keep playing around with it for while and see
what I can achieve.
--
Westie

SteveW wrote:
> Ok, see the code - I guessed as much that you were using some object.
>
> Only thing I can think of is when creating the object hold the ALT key
> down, this will *snap* the object to the top left of the cell.
>
> The other thing is to make sure the object has the *Move and Size with
> cell* attribute
> check properties
>
> Steve
>
>
> On Wed, 26 Jul 2006 10:52:09 +0100, Westie
> <[email protected]> wrote:
>
>> Thanks for replying, Steve.
>> I didn't post the code originally. I was intending to keep my post
>> short, but here it is:
>>
>> Option Explicit
>> Sub addCBX()
>> Dim myCBX As CheckBox
>> Dim myCell As Range
>>
>> With ActiveSheet
>> .CheckBoxes.Delete 'nice for testing
>> For Each myCell In ActiveSheet.Range("H7:H500").Cells
>> With myCell
>> Set myCBX = .Parent.CheckBoxes.Add _
>> (Top:=.Top, Width:=.Width, _
>> Left:=.Left, Height:=.Height)
>> With myCBX
>> .LinkedCell = myCell.Offset(0, 4).Address(external:=True)
>> .Caption = ""
>> .Name = "CBX_" & myCell.Address(0, 0)
>> End With
>> End With
>> Next myCell
>> End With
>> End Sub
>>
>>
>> You can see that I'm adding the tickbox from the forms toolbar - it
>> can be actively ticked or unticked. What do you call it? An object?
>> The control is linked to a target cell. That triggers my conditional
>> formatting colour change depending on the TRUE or FALSE result from
>> the checkbox. It's not the wingding font checkbox character that I'm
>> using.
>>
>> If you throw this macro into a sheet and run it, you'll see my problem.
>> It seems that "objects" float above the worksheet and it's tricky to
>> get them precisely aligned to cells - particularly when you use code
>> to insert 500 of them.
>>
>> Unless the row heights are set to exactly whatever the vertical
>> spacing between the checkboxes is, they incrementally get out of
>> alignment with the rows. I could probably get away with this if I
>> could match the row height to the height between checkboxes - but I
>> can't get it right. At least not for 500 of the suckers. I've
>> played around with different row heights but they all seem to go out
>> of alignment at some point regardless of what I do. I need a way to
>> keep the rows AND the checkboxes aligned.
>>
>> --
>> Westie
>