Custom Design of an Excel Textbox VBA

Question

Well I was wondering whether we can somehow create custom looking text boxes that act as an input box and is linked to VBA.

As far as I am aware the standard procedure would entail adding an ActiveX Textbox Control and then using the TextBox1_Change event to add the code as to what needs to happen when the user enters something in to.

Sadly the look of the default textbox isn't matching the way I want by spreadsheet to look. So is there any way to change how it looks or have something replace it while serving the same purpose?

One thing I could think of and have tried is inserting a shape (blue):

Shape http://im52.gulfup.com/qD2F0B.png

I can get the text that is in the shape using VBA by:

InputText = Shapes("Rounded Rectangle 1").TextFrame.Characters.Text

But I don't suppose there is a way to detect a change of shape text event?

Suggestions / Workarounds are welcome!

Thanks

PatricK · Accepted Answer · 2014-10-14 00:09:37Z

3

There are limitations on what you can change on an ActiveX TextBox, such as Font/Color/Border/SpecialEffects, but the basic rectangle shape cannot be changed.

However you can make the TextBox transparent by BackStyle property and group it to a shape (bring the TB forward) and still use the TextBox1_Change method for changes.

If you need to access the value in the TextBox somewhere else, a quick way is to use TextBox1.LinkedCell and below to set the value to a cell, or a Named Range.

Private Sub TextBox1_Change()
    ' Same Sheet as TextBox1
    ActiveSheet.Range(TextBox1.LinkedCell).Value = TextBox1.Value
    ' Or Below for Named Range
    ThisWorkbook.Names(TextBox1.LinkedCell).RefersToRange.Value = TextBox1.Value
End Sub

ActiveX TextBox Properties TxtBox on Top of Shape

answered Oct 14, 2014 at 0:09

PatricK

6,4231 gold badge23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Custom Design of an Excel Textbox VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related