Glaswegian
03-16-2005, 04:17 AM
Hello
I?m having a problem with Textboxes.
One of the sheets in the workbook I?m creating will have around a dozen textboxes. Users will input to some or all of these boxes. Once input is complete, the sheet will be printed ? therefore all text in the textboxes must be visible.
My problem is this ? how can I automatically resize the textboxes to ensure that
all text is visible
textboxes do not overlap, i.e. each textbox will retain it?s position relative to it?s neighbours
I tried something like this code, but it is only a partial (and not very good) solution
Private Sub CommandButton1_Click()
Dim tbHeight As Integer
Dim txBox As OLEObject
Dim rngCell As Range
Set txBox = Sheets("Sheet1").OLEObjects("TextBox1")
Set rngCell = txBox.BottomRightCell
tbHeight = txBox.Height
txBox.Height = txBox.Height + 10
rngCell.Select
rngCell.EntireRow.Insert
End Sub
As you can see I?ve resorted to using a button to run the code. I?ve tried using LineCount but I keep getting a message that I need to set the Focus first. I then try and set the focus but then get another message that ?Object does not support this property or method?. I also tried the Change Event for the textbox but, again, had problems with the focus. (this could be XL 97 related of course)
Textboxes are from the Control Toolbox. I had a play with Forms textboxes and using Automatic Sizing, but had problems with overlapping.
I have no preference for either type of textbox.
This is probably something I should be able to work out, but I think a fresh eye is needed.
Thanks for any assistance.
Regards
I?m having a problem with Textboxes.
One of the sheets in the workbook I?m creating will have around a dozen textboxes. Users will input to some or all of these boxes. Once input is complete, the sheet will be printed ? therefore all text in the textboxes must be visible.
My problem is this ? how can I automatically resize the textboxes to ensure that
all text is visible
textboxes do not overlap, i.e. each textbox will retain it?s position relative to it?s neighbours
I tried something like this code, but it is only a partial (and not very good) solution
Private Sub CommandButton1_Click()
Dim tbHeight As Integer
Dim txBox As OLEObject
Dim rngCell As Range
Set txBox = Sheets("Sheet1").OLEObjects("TextBox1")
Set rngCell = txBox.BottomRightCell
tbHeight = txBox.Height
txBox.Height = txBox.Height + 10
rngCell.Select
rngCell.EntireRow.Insert
End Sub
As you can see I?ve resorted to using a button to run the code. I?ve tried using LineCount but I keep getting a message that I need to set the Focus first. I then try and set the focus but then get another message that ?Object does not support this property or method?. I also tried the Change Event for the textbox but, again, had problems with the focus. (this could be XL 97 related of course)
Textboxes are from the Control Toolbox. I had a play with Forms textboxes and using Automatic Sizing, but had problems with overlapping.
I have no preference for either type of textbox.
This is probably something I should be able to work out, but I think a fresh eye is needed.
Thanks for any assistance.
Regards