Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference Tab name to two textboxes | Excel Discussion (Misc queries) | |||
Looping through textboxes | Excel Discussion (Misc queries) | |||
UserForm TextBoxes | Excel Discussion (Misc queries) | |||
tab between several textboxes | Excel Worksheet Functions | |||
Referencing Textboxes? | Excel Discussion (Misc queries) |