Selection change event on text inside a textbox

[jlt-uk]

Hello,
This is the first question I post. I have been using the Excel Forum for quite a while now and it has been extremely useful so far for finding answers in older threads so thank you everybody However this time I can’t find anything so I though I’ll ask the question.

As part of a program I’m writing in VBA (for Excel 2010), I have a textbox in a user form used as an interface to write a formula in cells in Excel.
The resulting value of a formula from a cell is loaded up into the textbox. It would be shown in the textbox like “See 1.2 and 1.3” where the formula in the cell is

Please Login or Register  to view this content.

This is just used as an example but the principle is there.
It is worth noting that I’m writing this for very inexperienced Excel users but I need them to be able to edit the string part of the formula without breaking the formula.

However where I’m struggling is to pick up a selection change event inside a text box already selected. I need to be able to check if the textbox.SelStart is within an address value or within the string in the textbox.
The event Enter won’t work if the user is already editing the textbox (i.e. typing stuff) and then clicking or using the keyboard arrow to move the cursor somewhere else inside the textbox. I don’t think the event Change is the solution either as it would mean that the user would have already typed something and as a result the formula may already be broken.

I have had a good look around and I didn’t find an event for a selection change inside a textbox. Does it exists and/or is there a way that would have the same result?

Many thanks in advance.

[Pauleyb]

I don't quite follow the specifics, but it seems you want to capture what people are typing into the list box in real time. I can't think of an obvious method off the top of my head, and it sounds like you have done some research that has led to dead ends. So, it is time to think of other possibilities.

First, capturing the typing events does not seem to be possible. Maybe you can add a 'Test' button that the user will press to ensure their input is valid? Of course, the test routine would also run when the user hits 'OK' so that you can keep the form open with a warning message that the input contains errors.

Second, maybe you could use the Application.OnTime method to run a function which reads the textbox every second and checks for accuracy? This may be annoying as the person is typing and the partial input has errors.

[JosephP]

I reckon you'll need to trap the keyup-testing if the key is an arrow key-and mouseup events and check the selstart then

[Pauleyb]

@JosephP - would those work inside a form box? I wasn't sure and it seems the OP looked at some of the other events.

[jlt-uk]

Thank you Josie and Pauley for your answers. This is very helpful I thought I hit a wall on that one but it seems that it could work.

The mouseup event works by using the code below from this website:

http://www.vbaexpress.com/forum/show...MouseUp-Events

Please Login or Register  to view this content.

It seems that it is possible to use the KeyUp event but I can’t get that one to work yet. Here is the code I found:

Please Login or Register  to view this content.

I found it on this website.
http://vbcity.com/forums/t/12201.aspx

However I get the following error message:
Compile error:
Procedure declaration does not match description of event or procedure having the same name.

Is this to do with the declaration of KeyCode and Shift?

Thank you for your help

JL

[Pauleyb]

Hmm, well, it usually means you are not matching the expected parameters, but it appears you are. Only thing I see suspicious is that the top code says TextBox1 and the bottom one says Textbox1. Probably the capital B.

[romperstomper]

The procedure declaration should actually be:

Please Login or Register  to view this content.

as userforms are not the same as the VB6 forms in that link.

[jlt-uk]

Perfect! It works now with Rory’s procedure declaration

Thank you so much all!

J-L