Managing Textboxes in a Worksheet
 

I have inserted textboxes in merged cell ranges as a way around Excel's cell
character count restrictions. There are ten worksheets with multiple
textboxes. Three questions:

1.) Is there a way to automatically size this textbox to the merged cell
range size?
2.) How could I keep the user from resizing or moving the textbox? They
should only be allowed to type text in it.
3.) Can/should this be done with code?

Without resolving issues 1 and 2, the worksheets will get pretty messy, as
there are about six of these textboxes in each worksheet.

Managing Textboxes in a Worksheet
 

Automatically size the textbox - no
You can of course do it with code.

you can protect the sheet to keep the user from making changes.

--
Regards,
Tom Ogilvy


"Phil H" wrote:

I have inserted textboxes in merged cell ranges as a way around Excel's cell
character count restrictions. There are ten worksheets with multiple
textboxes. Three questions:

1.) Is there a way to automatically size this textbox to the merged cell
range size?
2.) How could I keep the user from resizing or moving the textbox? They
should only be allowed to type text in it.
3.) Can/should this be done with code?

Without resolving issues 1 and 2, the worksheets will get pretty messy, as
there are about six of these textboxes in each worksheet.

Managing Textboxes in a Worksheet
 

Tom, Thanks for your answer.

What I planned was a text box with a scroll bar that users could type text
into. But I also know how this can get very sloppy because of user
carelessness. Could you suggest code to size the textbox? I do plan to
protect the worksheet - will the user still be able to make textbox entries?

Thanks, Phil

"Tom Ogilvy" wrote:

Automatically size the textbox - no
You can of course do it with code.

you can protect the sheet to keep the user from making changes.

--
Regards,
Tom Ogilvy


"Phil H" wrote:

I have inserted textboxes in merged cell ranges as a way around Excel's cell
character count restrictions. There are ten worksheets with multiple
textboxes. Three questions:

1.) Is there a way to automatically size this textbox to the merged cell
range size?
2.) How could I keep the user from resizing or moving the textbox? They
should only be allowed to type text in it.
3.) Can/should this be done with code?

Without resolving issues 1 and 2, the worksheets will get pretty messy, as
there are about six of these textboxes in each worksheet.

Managing Textboxes in a Worksheet
 

Assuming a textbox from the control toolbox toolbar

yes, if the sheet is protected, the user can still type in the textbox

Sub AAtester2()
Set sh = ActiveSheet
With sh.Range("B9").MergeArea
sh.TextBox1.Left = .Left
sh.TextBox1.Top = .Top
sh.TextBox1.Width = .Width
sh.TextBox1.Height = .Height
End With
End Sub

--
Regards,
Tom Ogilvy

"Phil H" wrote:

Tom, Thanks for your answer.

What I planned was a text box with a scroll bar that users could type text
into. But I also know how this can get very sloppy because of user
carelessness. Could you suggest code to size the textbox? I do plan to
protect the worksheet - will the user still be able to make textbox entries?

Thanks, Phil

"Tom Ogilvy" wrote:

Automatically size the textbox - no
You can of course do it with code.

you can protect the sheet to keep the user from making changes.

--
Regards,
Tom Ogilvy


"Phil H" wrote:

I have inserted textboxes in merged cell ranges as a way around Excel's cell
character count restrictions. There are ten worksheets with multiple
textboxes. Three questions:

1.) Is there a way to automatically size this textbox to the merged cell
range size?
2.) How could I keep the user from resizing or moving the textbox? They
should only be allowed to type text in it.
3.) Can/should this be done with code?

Without resolving issues 1 and 2, the worksheets will get pretty messy, as
there are about six of these textboxes in each worksheet.

Managing Textboxes in a Worksheet
 

This is what I needed - thanks for the help, Tom.

"Tom Ogilvy" wrote:

Assuming a textbox from the control toolbox toolbar

yes, if the sheet is protected, the user can still type in the textbox

Sub AAtester2()
Set sh = ActiveSheet
With sh.Range("B9").MergeArea
sh.TextBox1.Left = .Left
sh.TextBox1.Top = .Top
sh.TextBox1.Width = .Width
sh.TextBox1.Height = .Height
End With
End Sub

--
Regards,
Tom Ogilvy

"Phil H" wrote:

Tom, Thanks for your answer.

What I planned was a text box with a scroll bar that users could type text
into. But I also know how this can get very sloppy because of user
carelessness. Could you suggest code to size the textbox? I do plan to
protect the worksheet - will the user still be able to make textbox entries?

Thanks, Phil

"Tom Ogilvy" wrote:

Automatically size the textbox - no
You can of course do it with code.

you can protect the sheet to keep the user from making changes.

--
Regards,
Tom Ogilvy


"Phil H" wrote:

I have inserted textboxes in merged cell ranges as a way around Excel's cell
character count restrictions. There are ten worksheets with multiple
textboxes. Three questions:

1.) Is there a way to automatically size this textbox to the merged cell
range size?
2.) How could I keep the user from resizing or moving the textbox? They
should only be allowed to type text in it.
3.) Can/should this be done with code?

Without resolving issues 1 and 2, the worksheets will get pretty messy, as
there are about six of these textboxes in each worksheet.