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

Problem vertically aligning multiple rows with tickboxes added using VB.
 

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

On Wed, 26 Jul 2006 07:58:02 +0100, Westie
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 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

Problem vertically aligning multiple rows with tickboxes addedusing VB.
 

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

Problem vertically aligning multiple rows with tickboxes added using VB.
 

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

Problem vertically aligning multiple rows with tickboxes addedusing VB.
 

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