Turn off the Ability to Select Text Box

[mc-lemons]

Hi all,

Is there any way to turn off the ability to select a Text Box? Just a regular Text Box object in a worksheet (not in a form, etc.). It would be similar to how the border of a Group Box can be turned off via the code below and cannot be selected until it is turned back on. Note that I still need the text to be visible. Also, the Text Boxes will be snapped to the size of various cells, so if those cells change in size due to row or column adjustments, I need the Text Boxes to adjust accordingly, like they normally do.

activesheet.groupboxes.visible = false

Thanks!

 

[Anthony47]

If your TextBox is an ActiveX (not a Module) then it has the "Enabled" property; thus you can enable and disable it:

Me.TextBox1.Enabled = False
'...
Me.TextBox1.Enabled = True

For the repositioning question, you have to set the Placement property; for example:
Me.TextBox1.Placement = xlMoveAndSize

For the available options:

XlPlacement Enum (Microsoft.Office.Interop.Excel)

Specifies the way an object is attached to its underlying cells.

docs.microsoft.com

These example are intended for the vba class module of the worksheet that holds the TextBox

Bye

 

[mc-lemons]

Anthony,

Thank you so much for your response! Two questions:

1. While I personally use VBA to update modify things when making revisions/updating the template, I have not designed it to actively use VBA since users often forget to enable macros. Assuming everything is initially set up correctly, would the moving/sizing of the ActiveX Textbox work if macros are not enabled?
2. When the Text Box is disabled, can the cells that it is covering be selected like normal?

Thanks again!

 

[Anthony47]

Once you set the properties of the object, they will stay there even if the user don't enable macros. Of course if macro are disabled you will not be able to switch TextBox1.Enabled = False or True

When TextBox1.Enabled = False the TextBox is still visible and probably will hide some cells; to select the hided cells you cannot use the mouse but have to use the keyboard arrow keys

Bye

 

[mc-lemons]

Ok, thanks again for the help. Is there a way to make this as solved?

 

[mc-lemons]

Just noticed the text stretches in Text Box when the column is widened

 

[Anthony47]

Is that a problem?
It will gain normal width when Enable=True

 

[mc-lemons]

Yeah, I don't really want stretched text. When a regular Text Box object changes size due to column or row adjustments, the text doesn't stretch and looks normal, just the wrap point changes. That's what I'm looking for. If somehow I could "Send it to Back", behind the cells where it couldn't be selected but the cell(s) in front could, it would work how I envisioned. Doesn't seem like it is possible though.

 

[Anthony47]

Sometimes you have to settle for what you have
Or you should accept that the TextBox moves when cell are resized but doesn't resize with them

Bye

 

[mc-lemons]

I will approach the problem from another angle. There are many ways to skin a cat!

Thanks for your efforts!