Excel Enter text in a textbox

[gustaf111]

Hello,

I have this problem: I would like the user to enter text in a textbox, to be able to use tab.

The Textbox under the ribbon meny insert->Textbox got these features, but is it possible to access that text from vba ?

The textbox under the ribbon meny developer->insert->Textbox, can you configure it so the user can use tab ?

Any ideas ?
Gustaf

 

[gustaf111]

How to count the length of a string that contains tabs ? Len only counts char not the "space" due to tabs

 

[gustaf111]

Or is it possible to use tab in a merged row ?

 

[combo]

In the VB editor set the textbox TabKeyBehavior property to true.

You can use Len function or TextLength property to get the length in characters. How can one measure the fixed tab space in case of variable length potential characters?

What 'merged rows' do you mean?

combo

 

[gustaf111]

Thanks combo!

I have two option ... I need to make the tab working, if I can not get the tab working within a merged cells. I have merged Cell A1 to AB1 and want to use tab within that ... is that possible ?

My other solution is to use a textbox, but I need to keep track so the text is filled within the textbox ... so that is the reason I want to count the number of char + tabs within the textbox ..

 

[combo]

AFAIK, excel does not accept tabs in the cell. If you paste a text with tabs (from notepad for instance), they are converted to spaces (1 tab = 1 space).
You can limit a number of characters in the textbox via its MaxLength property.

combo

 

[SkipVought]

What is the purpose of the TAB in a merged cell? Please give an example that would illustrate the effect.

You may be able to simulate this using the CAMERA feature.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gustaf111]

Thanks again combo!

Hello Skip
Then user would like to use tab instead of spaces I think they will add "colums" and data to it so then is tab more convenient I guess.

I will go for a textbox and use tab there. Does anybody know how many spaces one tab represtent ? I cheked and on my instalation one tab is 8 char ... is that different or same for all ?

 

[combo]

In my case for Tahoma: 4-5 for 'W', around 24 for 'i', for Courier New - 7.
It's much better to use a series of textboxes or directly excel cells instead.

combo

 

[gustaf111]

Combo ... shit I notice that also I had a merged cells from the beginning

In a textbox is it possible to prevent vertical scroll so you can scroll out text so not all text shows all the time ?

or is it possible to fake a tab in cells that are merged ? For some key combination to include spaces ?

 

[combo]

Don't merge cells. Use white borders in the middle instead.
For a textbox - you can adjust the height and display only the scrollbars you need.
Anyway, make the interface simple and not confusing. If you have a structured data, design a data entry form or let users directly enter data in the predefined area of spreadsheet, next format it. Tell users that it's not a word processor and has some limitations, otherwise use word.

combo

 

[gustaf111]

Yes you are right, the box is intended to be a box there you could input "fri" text etc

Thanks again!

 

[SkipVought]

Yes, forget MERGE, it is a SCOURGE!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gustaf111]

Is it possible to check if the scrollbars are active for a textbox ?

 

[combo]

Check properties of your textbox in the VBE properties window. Properties of all activex controls are available for a sheet selected in object browser when you switch to design mode.
Search for TextBox class in object browser and check properties in the help file (F1). You will find more or less what you can do with the control.

combo