Excel userform textboxes not updating, after change in MultiPage tab

[Un-Do Re-Do]

I have this userform which takes selected cells and puts them in userform textboxes. Below the textboxes, there are command buttons which insert a character into the (1) the textbox, and (2) the relevant cell. The buttons are separated into MultiPage tabs, so far there are numbers and letters.
* The change event where a different MultiPage tab is selected is causing problems. The update is made correctly to the cell but not the textbox (when additional characters are added).

The way the code is structured is that when there is a change in MultiPage tab, the form setup code "FormID" is called up again because this fixes the overall height of the form ... as different tabs have a different height.


Snapshot.png

FORM CODE

Please Login or Register  to view this content.

CLASS MODULE clsDelegate

Please Login or Register  to view this content.

CLASS MODULE clsEvents

Please Login or Register  to view this content.

[Keebellah]

You're probably missing the code TextBox change; there is no other event to update the contents.
I'll see what I can find

[Keebellah]

First thing that comes to mind, is that you do nothing with the textbox and worksheet interaction.
I just opened the file to see what it does, but you must interact with textbox AND the cell.
Any change in the textbox must update the cell and vv, so if you press Backspace the character is deleted and then the textbox contents is pasted in the cell.
Leading will have to be the textbox value
Back to the drawingboard for the coding for you

[rorya]

Why do you call all that code again if all you want to do is change the height?

[Un-Do Re-Do]

Any change in the textbox must update the cell and vv, so if you press Backspace the character is deleted and then the textbox contents is pasted in the cell.
Leading will have to be the textbox value

@Keebellah, there is a reason for this. It's because there is a variation in fonts, to my understanding this information can't be transferred between textbox to cell. If the textbox leads, I lose the fonts.
I have heard about InkEdit Control but I get the feeling this was not the way to go.

[Keebellah]

If it's the font that you want to maintain then you will have to do that on the worksheet, a textbox does NOT interact with the worksheet font properties, it's plain text.
You will have to account for that in the code when writing the textbox to the worksheet's cell

[Keebellah]

The code seems like an overkill to me, but then I haven't looked at what you are really trying to achieve, I agree with @rorya

[Un-Do Re-Do]

I was just thinking that my logic in post #5 may not be correct. Not surprising.

Why do you call all that code again if all you want to do is change the height?

@rorya, I don't know how to do it otherwise.

[Keebellah]

I can't really make heads or tails how you did this.
I spent half an hour trying to figure it out but lost it along the way.
I'm not going to analyse your code to try to understand/decypher it, I'm missing parts, have no idea what the rest of your ideas are. and all the TBA buttons either; assumptions always sent me up the wrong path.
Hope others can really help you.

[Un-Do Re-Do]

That's OK. It may not be logical. I really use trial and error mostly.

[Keebellah]

That's my method too.
Just a question.
What is you're trying to achieve?
Allow a user to edit each of the cells in B using the userform and only the buttons?

[Un-Do Re-Do]

What is you're trying to achieve?

I'm trying to add a character using the command buttons, at the cursor location, in the textbox. This works for me, except that when I change MultiPage tabs, the inserted characters are not shown in the textbox (it is written into the cell though, which it should be). If the tab is again changed, it does seem to refresh the textbox but I need this refreshed as the command button is pressed.

[Keebellah]

The reason is that when youchange the multipage you do not have the focus set to that textbox, and I don't know how you address is since you're using cls modules that invoke something but don't tell me the textbox's name.
After you 'switch' multipage you have to either click that textbox first or check the code where you can reset the focus to that textbox again, then the text will show up in the textbox.

You als don't have a backspace to clear a character from the textbox as well as from the cell.

[Un-Do Re-Do]

Yes I know but how to do in Visual Basic.

With the delete character, I have something but not included here. I'm using "Cell.Characters(.SelStart, 1).Insert vbNullString".

[Keebellah]

I don't where that textbox comes from so I don't know either.
I've checke the form but cannot detect it, you wrote (or copied) the code so you should know what the textboxs name (reference) is.

[Un-Do Re-Do]

The text box name is "Text_Box_" & digit, as per code in the original post.

[rorya]

Put the code that changes the heights into a separate routine and call it from both locations.

[Un-Do Re-Do]

Put the code that changes the heights into a separate routine and call it from both locations.

No, this didn't resolve it

[rorya]

Hard to comment further as I don't know what you actually did, but you should do that anyway. Your current approach is inefficient and doesn't really make sense.

[Un-Do Re-Do]

Please see attached.

[Andy Pope]

Try this version.

I added code to FormID routine so it only create controls and event objects once.
On further calls it only updates multipage and userform size.

[rorya]

FWIW, you were still calling FormID from the multipage change event so it's not surprising it didn't fix it.

[Un-Do Re-Do]

@Andy Pope. Thanks very much again. This works as intended.

[Un-Do Re-Do]

Thanks to others as well. The code may be jumbled up but I had a couple of other functionalities (not shown) which seemed to work with the way it was.

[Keebellah]

Just a minor modification, instead of selecting the cell an pressing click, select the cell and right mouse button and position the userform accordingly too

[Un-Do Re-Do]

Ouch.
The solution at post #21 creates controls and objects once. I had an extra part, not shown which was for a change in selected cells.
It now won't work for this.
Sorry about that.

Please Login or Register  to view this content.

[Un-Do Re-Do]

Is there a way of capturing the name of the last visited text box?

[Keebellah]

That is what I was asking to be able to set the focus. with the code you're using?
Maybe a global Variable to do that

[Un-Do Re-Do]

Maybe a global Variable to do that

Yes I know. But how do you do it.

[Keebellah]

Read my question in https://www.excelforum.com/excel-pro...ml#post4935678
Let me know

[Andy Pope]

This revision allow for selection of different ranges whilst the form is still loaded.

It automatically resets the activecontrol to nothing when the selection changes.

If you wanted to keep the activecontrol reference alive you would need to store the cell address as
well and then you could compare address of activecontrol to all the newly referenced controls and set activecontrol if there was a match.
Otherwise set reference to nothing and await user to select one of the textboxes.

[Un-Do Re-Do]

Are you really 'attached' to the way that userform has been coded?

I would be surprised if it works in another way, given that there is a frame with textboxes and a MultiPage tab, and the requirement to update on the fly.

Does it only apply to column B in the said worksheet?

No there is nothing in the code which refers to Column B.

[Un-Do Re-Do]

Following from another post.

This is my latest attempt to tell the code which is the active text box when there is a selection change in the MultiPage tab. The issue was that the change is not shown in the textbox when characters are added. Still not working though.

Note that solution at post #31 would not update with a change in cell selection.

MODIFIED CLASS MODULE clsEvents

Please Login or Register  to view this content.

CHANGES/ADDITIONS TO FORM CODE

Please Login or Register  to view this content.

[Keebellah]

Is there a special reason you want the characters entered by pressing a button?
What about a comma or a space no 'buttons' for that

[Un-Do Re-Do]

Is there a special reason you want the characters entered by pressing a button?

I can't remember but there was a good reason, it may have something to do with rich text strings, ie, keeping fonts and sizes, etc.

What about a comma or a space no 'buttons' for that

You could use the ChrW function.

Please Login or Register  to view this content.

[Keebellah]

I had this file ready for but pasted it in the wrong post

[Un-Do Re-Do]

Sorry but I'm not sure what your intention is.
Can you please let someone try to solve my problem.

[AliGW]

Keebellah is trying to help solve your problem - have you looked at the sample file in post #36? If it's not what you want, just explain in what way it doesn't fit the bill.

[Un-Do Re-Do]

Hi AliGW and Keebellah. I have no intention of being difficult. I have had a look at the file and whilst it may be helpful to others, it is digressing from what I'm trying to do. I am appreciative of the response but I'm a bit under the pump ... so sorry to you Keebellah for the comment.

[AliGW]

Thanks for clarifying. Let's hope someone else understands what you require and can help. Please remember, though, that all help here is given voluntarily and for free, so we can't guarantee that it will be solved (if at all) within the timeframe you require. It may be that you need to redefine your issue at this point (don't start a new thread - give extra pertinent information here). The main reason for issues not being resolved is a lack of clarity on the part of the person asking for help, who of course fully understands what they want!

[Un-Do Re-Do]

OK thank you, I'll bear this in mind.

[AliGW]

So are you going to redefine the issue? I really feel, having read through this thread and the changes in requirements you have made along the way, that you need to do so if you want anyone to engage with this today.

[Un-Do Re-Do]

I actually did start a new thread along similar bu not the same lines, so sorry if that is against forum rules. This was about 3 hours ago. I'll have a go at redefining the issue, give me 15 minutes to get everything together.

[Keebellah]

My last posted file does exactly what you want in a TextBox, I remove the Frame.
But...
Have fun with it, I'm unsubscribing from this post.

[AliGW]

Duplicate threads are not allowed. Your second thread has now been closed. Please post the updated information here.

[Un-Do Re-Do]

OK, so I'm not looking too good here.
In trying to re-clarify the question, it turns out that the solution was given in post #31 by @Andy Pope. For some reason I had missed out on it.
So apologies to you again Keeballah. Thanks for your time though. I hope I can make it up to you.
Thanks to @Andy Pope for the solution, and AliGW for pushing it along.

[AliGW]

That's good to know - thanks for letting us know.