Get textbox to record user selected area

[nemmi69]

I am trying to get a textbox to record an area selected by a use (the same way that it is done in graphs) but I am at a loss how it is done. Any ideas?

[HaHoBe]

Hi, nemmi69,

Textbox in a worksheet or on a UserForm?

Please Login or Register  to view this content.

Ciao,
Holger

[nemmi69]

The textbox is on a worksheet. The graph textbox does not use an inputbox you just click it the highlight the area concerned and that shows in the textbox.

[HaHoBe]

Hi, nemmi69,

I think what you are talking about is not a TextBox but the RefEdit-Control which features the showing of the address and the possiblility to either enter directly or via chossing the range with the mouse. Maybe check that control out.

Ciao,
Holger

[nemmi69]

Yes that's what I am trying to emulate

[HaHoBe]

Hi, nemmi69,

sorry but to my knowledge you would need 2 controls for that: the textbox to display the address and a dropdpon which lets you enter the address manualy or select via Mouse / Keyboard. Maybe you could have a look at Alternative to Excel’s Flaky RefEdit Control.

Ciao,
Holger

[nemmi69]

My problem seeems that all the examples have user forms and i just have the textbox on the worksheet.

[nemmi69]

Well I came up with this for a textbox on a worksheet. At the moment it only works for the current workbook though on any worksheet.

So :-
1] How do you get it to work for other workbooks?
2] Does anyone see anything wrong with this or other problems?

In ThisWorkbook

Please Login or Register  to view this content.

In Sheet1 where the textbox resides

Please Login or Register  to view this content.

In Module

Please Login or Register  to view this content.

[HaHoBe]

Hi, nemmi69,

you work exclusively with the active workbook and the active sheet in your code. If you have set a range the Parent of this will give you the worksheet name, Parent.Parent will deliver the name of the workbook (untested):

Please Login or Register  to view this content.

Ciao,
Holger

[nemmi69]

Well I was looking for this to be able to take a range from a different workbook.