trippo
03-03-2010, 08:39 AM
I have a code that places checkboxes in 60 consecutive cells of a column. As the checkboxes are placed, the first checkbox is aligned in the cell, but as the others are placed down the column, the checkboxes start to wander out of the cell such that the last checkbox is no longer in the last cell location (somewhat below).
I have tried to use the activecell .top and .left commands, but that doesn't work (even if I select the last cell location, it will place the checkbox below the row). I have tried to calculate the position in inches based on the number of rows and their heights and that doesn't work (it's almost like the row heights are not as large as indicated).
Thoughts?
'Make sure all rows and columns are the correct height and width as the check boxes are added based upon the cell height and width.
Header1 = 15.75
Header2 = 24#
HeightRow = 18#
Column1 = 35#
Column2 = 7.5
Column3 = 11#
Column4 = 12#
WidthColumn = 16#
Range("A1:A1").EntireRow.RowHeight = Header1
Range("A2:A2").EntireRow.RowHeight = Header2
Range("A3:A3").EntireRow.RowHeight = Header1
Range("A4:A4").EntireRow.RowHeight = Header1
Range("A5:A" & FinalRow).EntireRow.RowHeight = HeightRow
Columns(1).ColumnWidth = Column1
Columns(2).ColumnWidth = Column2
Columns(3).ColumnWidth = Column3
Columns(4).ColumnWidth = Column4
DataColumn = 5
Do Until DataColumn = FinalColumn
Columns(DataColumn).ColumnWidth = WidthColumn
DataColumn = DataColumn + 1
Loop
'Insert a column to the left of the selected/input column
Range(InputColumnInsert & "1", InputColumnInsert & "1").EntireColumn.Insert
'Start inserting checkboxes after the heading rows.This assumes the first checkbox row is in row number 5.
ChkBxLocation = "71.25"
For RowNum = 5 To FinalRow
'Select the first cell of the range to insert the check box.
Range(InputColumnInsert & RowNum).Select
Edit: VBA tags added to code.
I have tried to use the activecell .top and .left commands, but that doesn't work (even if I select the last cell location, it will place the checkbox below the row). I have tried to calculate the position in inches based on the number of rows and their heights and that doesn't work (it's almost like the row heights are not as large as indicated).
Thoughts?
'Make sure all rows and columns are the correct height and width as the check boxes are added based upon the cell height and width.
Header1 = 15.75
Header2 = 24#
HeightRow = 18#
Column1 = 35#
Column2 = 7.5
Column3 = 11#
Column4 = 12#
WidthColumn = 16#
Range("A1:A1").EntireRow.RowHeight = Header1
Range("A2:A2").EntireRow.RowHeight = Header2
Range("A3:A3").EntireRow.RowHeight = Header1
Range("A4:A4").EntireRow.RowHeight = Header1
Range("A5:A" & FinalRow).EntireRow.RowHeight = HeightRow
Columns(1).ColumnWidth = Column1
Columns(2).ColumnWidth = Column2
Columns(3).ColumnWidth = Column3
Columns(4).ColumnWidth = Column4
DataColumn = 5
Do Until DataColumn = FinalColumn
Columns(DataColumn).ColumnWidth = WidthColumn
DataColumn = DataColumn + 1
Loop
'Insert a column to the left of the selected/input column
Range(InputColumnInsert & "1", InputColumnInsert & "1").EntireColumn.Insert
'Start inserting checkboxes after the heading rows.This assumes the first checkbox row is in row number 5.
ChkBxLocation = "71.25"
For RowNum = 5 To FinalRow
'Select the first cell of the range to insert the check box.
Range(InputColumnInsert & RowNum).Select
Edit: VBA tags added to code.