Textboxes
 

Greetins to All,

Is there a way to insert several textboxes at once that are aligned with the
size of the cell. I would like to desiginate part of a column for text entry
and copy the text boxes down for as many as I may need. The width/height of
the column/rows may need to be changed. And it would be great if I could
insert rows and/or columns without causing problems to the text boxes. And
finally, is there a way to copy a particular textbox to a cell in another
workbook?

Thanks
--
SAL

Textboxes
 

Hi

I recently wrote this bunch of code, which will set you on track:

Sub PutCheckboxes()

' Wim Gielis
'
' 07/09/2007
' Procedure to put a chosen number of checkboxes
' Also on http://www.wimgielis.be


Dim lRowsCounter As Long
Dim iColsCounter As Integer
Dim r As Range
Dim rngCells As Range

Dim lNumberOfRows
Dim lNumberOfColumns

Application.ScreenUpdating = False

'tidy up
On Error Resume Next

'remove all checkboxen on sheet
ActiveSheet.DrawingObjects.Delete

With ActiveSheet.UsedRange

'clear the TRUE's, FALSE's and formulas
.ClearContents

'clear formulas and checkbox status
.Font.ColorIndex = 0

'reset row height
.Rows.RowHeight = 12.75

'reset alignment
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
End With

On Error GoTo 0
'end of tidy up

'ask number of checkboxes
Do
lNumberOfRows = Application.InputBox _
("How many rows with checkboxes do you want?", "Rows", 20,
Type:=1)
Loop Until lNumberOfRows 0

Do
lNumberOfColumns = Application.InputBox _
("How many columns with checkboxes do you want?", "Columns", 4,
Type:=1)
Loop Until lNumberOfColumns 0

Set rngCells = Range("A1").Resize(lNumberOfRows, lNumberOfColumns)
rngCells.Rows.RowHeight = 24

'place checkbox in the cells
For lRowsCounter = 1 To lNumberOfRows
For iColsCounter = 1 To lNumberOfColumns

'update status in statusbar
Application.StatusBar = "Processing checkbox " & (lRowsCounter -
1) * _
lNumberOfColumns + iColsCounter & " of " & lNumberOfRows *
lNumberOfColumns

Set r = Cells(lRowsCounter, iColsCounter)

ActiveSheet.CheckBoxes.Add(r.Left, r.Top, _
r.Width * 0.5, r.Height).Select
With Selection
.Name = "CB" & lRowsCounter & "-" & iColsCounter
.LinkedCell = r.Address
.Characters.Text = ""
End With
Next iColsCounter
Next lRowsCounter

With rngCells

.Range("A1").Select

'put cells with TRUE's and FALSE's in white font colour
.Font.ColorIndex = 2

With .Columns(1).Offset(, lNumberOfColumns)

'in the column to the right of the last checkbox,
'there is a, b, c, d, ...
.FormulaR1C1 = "=IF(COUNTIF(RC[-1]:RC[-" & lNumberOfColumns &
"],TRUE)," & _
"CHAR(96+MATCH(TRUE,RC[-1]:RC[-" & lNumberOfColumns &
"],0)),"""")"

'center contents
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter

End With

End With

With Application
.StatusBar = False
.ScreenUpdating = False
End With

End Sub


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"SAL" wrote:

Greetins to All,

Is there a way to insert several textboxes at once that are aligned with the
size of the cell. I would like to desiginate part of a column for text entry
and copy the text boxes down for as many as I may need. The width/height of
the column/rows may need to be changed. And it would be great if I could
insert rows and/or columns without causing problems to the text boxes. And
finally, is there a way to copy a particular textbox to a cell in another
workbook?

Thanks
--
SAL

Textboxes
 

Hi

Updated code, as well as userform on it:

see my website, then choose Excel in the left menu, then VBA-code, then
scroll down a bit and click "Multiple choice"

code is unprotected.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Wigi" wrote:

Hi

I recently wrote this bunch of code, which will set you on track:

Sub PutCheckboxes()

' Wim Gielis
'
' 07/09/2007
' Procedure to put a chosen number of checkboxes
' Also on http://www.wimgielis.be


Dim lRowsCounter As Long
Dim iColsCounter As Integer
Dim r As Range
Dim rngCells As Range

Dim lNumberOfRows
Dim lNumberOfColumns

Application.ScreenUpdating = False

'tidy up
On Error Resume Next

'remove all checkboxen on sheet
ActiveSheet.DrawingObjects.Delete

With ActiveSheet.UsedRange

'clear the TRUE's, FALSE's and formulas
.ClearContents

'clear formulas and checkbox status
.Font.ColorIndex = 0

'reset row height
.Rows.RowHeight = 12.75

'reset alignment
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
End With

On Error GoTo 0
'end of tidy up

'ask number of checkboxes
Do
lNumberOfRows = Application.InputBox _
("How many rows with checkboxes do you want?", "Rows", 20,
Type:=1)
Loop Until lNumberOfRows 0

Do
lNumberOfColumns = Application.InputBox _
("How many columns with checkboxes do you want?", "Columns", 4,
Type:=1)
Loop Until lNumberOfColumns 0

Set rngCells = Range("A1").Resize(lNumberOfRows, lNumberOfColumns)
rngCells.Rows.RowHeight = 24

'place checkbox in the cells
For lRowsCounter = 1 To lNumberOfRows
For iColsCounter = 1 To lNumberOfColumns

'update status in statusbar
Application.StatusBar = "Processing checkbox " & (lRowsCounter -
1) * _
lNumberOfColumns + iColsCounter & " of " & lNumberOfRows *
lNumberOfColumns

Set r = Cells(lRowsCounter, iColsCounter)

ActiveSheet.CheckBoxes.Add(r.Left, r.Top, _
r.Width * 0.5, r.Height).Select
With Selection
.Name = "CB" & lRowsCounter & "-" & iColsCounter
.LinkedCell = r.Address
.Characters.Text = ""
End With
Next iColsCounter
Next lRowsCounter

With rngCells

.Range("A1").Select

'put cells with TRUE's and FALSE's in white font colour
.Font.ColorIndex = 2

With .Columns(1).Offset(, lNumberOfColumns)

'in the column to the right of the last checkbox,
'there is a, b, c, d, ...
.FormulaR1C1 = "=IF(COUNTIF(RC[-1]:RC[-" & lNumberOfColumns &
"],TRUE)," & _
"CHAR(96+MATCH(TRUE,RC[-1]:RC[-" & lNumberOfColumns &
"],0)),"""")"

'center contents
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter

End With

End With

With Application
.StatusBar = False
.ScreenUpdating = False
End With

End Sub


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"SAL" wrote:

Greetins to All,

Is there a way to insert several textboxes at once that are aligned with the
size of the cell. I would like to desiginate part of a column for text entry
and copy the text boxes down for as many as I may need. The width/height of
the column/rows may need to be changed. And it would be great if I could
insert rows and/or columns without causing problems to the text boxes. And
finally, is there a way to copy a particular textbox to a cell in another
workbook?

Thanks
--
SAL