Userform textbox used as refedit

Hi guys,

I hope what I am trying to achieve is possible, if not please suggest another way.

I have a userform that is called from a button in excel. The userform has a couple of text boxes in which the user can either fill values, refer to any cell on the sheet (might never be the same one twice), and type excel or custom formulae, or a combination of these.

I tried using the refedit, but for some reason I cannot type a formula, and then select a cell, as you would find in the "Insert function" block, in fact, the "Insert Function" tab is what I would like to replicate:

label: textbox = value
label: textbox = value
label: textbox = value
-----------------------------------------
= value

The only diference is that I would like to be able to store each text box individually on a hidden sheet.

If what I have asked is unclear, please ask and I'll try to clarify more.

Thanks in advance.

Jacques Grobler,

Goeie more,

Could you upload a small sample WorkBook with Userform etc. also showing on a seperate sheet the desired outcome?

Maybe it will clear things up for us.

'n Goeie more aan jou ook Winon

I have picked up a couple of problems while trying to replicate what I'd like to achieve:

1> When I enter a formula into a refedit box, excel hangs
2> When I enter a value into the refedit box, and want to get the value, as it is not a range value, it errors - this I can sort out, least of my problems

I have attached a workbook with 2 forms in, note that only one form is called form the excel workbook, the other needs to be run from VBE directly (not connected to anything at this stage).

I hope this gives a better idea of what I'd like to achieve, if not, please let me know.myBook_btns Control.xlsm

Somebody... Anybody... Nobody... Body...

Hi Jacques,

Gestonk!

Sorry, but I cannot give you a solution as yet. As far as the RefEdit goes, I don't think it will ever allow a user to enter any number or formula in there. I will have a look at it again later tonight.

In the meantime, I think you should replace those two boxes with TextBoxes.

Sterkte!

Ook goeiemorgen !

Check whether the values won't exceed the limits of the vartype !

Please Login or Register  to view this content.

I added this code to your Userform1 and started this Userform1 from the VBEditor (avoiding all other code).

Click the start button.
change a value in textbox3 (that contains 20 when opening): e.g into 200
Look at the userform's caption.

Here is what I'd like to achieve with a textbox, however, I have probable not played enough with the concept to adapt it to what I require, but in any case, here is the site:

http://peltiertech.com/WordPress/ref...l-alternative/

SNB, thanks for the reply, I'll have a look at your suggestion as well.

Just a spanner in the works, if I start in a clean cell, and I start typing P for PI(), it gives me an error because it doesn't recognise the formula as a value, but rather as a string.

I'm trying to work around this but I am struggling with this. Even if you take let's say textbox1 and you select the cell and start clearing by pressing backspace, you get an error.

The input in the textbox must be identical to the normal formula input in a cell in a worksheet.

You can use the exit-event:

Please Login or Register  to view this content.

Hi SNB,

Got something similar to what you have posted before, see attached.

Now if I can incorporate teh cell selection as indicated in the page I have linked earlier, I'll be where I want to be.

If you want the cell selection to be activated: doubleclick in the textbox.

Hi SNB,

The problem then is say I type PI()* , then I double click and it opens the "select cell", whatever it returns, wipes the existing info in the cell

Ok, I have achieved the following but then again pick up some errors that might be able to be solved with a bit of help...

> When I enter "PI()* " and then select the _ btn on the textbox (see attached book), I can select a cell without double-click, select the cell and then return to the form, but then the "input box" value is seen as text and it changes the formula into ="PI()*$H$6", which is a string.
> When I only select a cell in the "input box", say cell H6, I see =$H$6 in the box, but when I go to the form I see =R6C8. This does not give me a value when evaluating.

Anyone out there?

Just bumping the thread up again

YAY!!!!!!!!

Calm yourself, breath...

Ok, so I have achieved the ideal text box for the use as a refedit box, as well as sorted out the problems I have come accross such as R1C1 reference, strings to values, etc, etc, etc.

Attached is the ideal, however, I'd like to take this one step further now, I'd like to have this redEditTextBox as a "drag onto userform" button on my toolbox, how do I do this?

Anyone out there to help please?

Morning all,

As my previous request was not really part of how the thread was started, I'm marking this one as solved, and I will start a new thread for the new question.

See: http://www.excelforum.com/excel-prog...72#post2840972