How to get the cursor position on user form in EXCEL VBA?

[softlover]

I made a user form in EXCEL VBA. and I want to input number to some TextBox by "soft keyboard". But before input the number I should get the position where the cursor is.
Anyone know the codes to locate the cursor?

 

[PHV]

Have a look at the Selstart, SelLength and SelText properties of the TextBox object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

Anyone know the codes to locate the cursor? "

Could you explain that? You can get numbers about the cursor IN a control using the SelStart etc. properties PHV mentions. However, I am not sure that is what you are asking about.

So let's be clear, when you say cursor, do actually mean focus? If you DO really mean cursor, then if the cursor is in TextBox1 (say), then its position can be returned using the properties PHV mentions.

BTW: what do you mean by "soft keyboard"?

Gerry

 

[softlover]

What I means input number by "soft keyboard" is that I made ten numeric CommandButton which defined caption from "0" to "9" and when press any numeric CommandButton then input the caption as number to the TextBox which cursor located.

The Selstart, SelLength and SelText properties of the TextBox object seems unusable.

 

[PHV]

You may use those properties in the Exit event procedure of the TextBox and store them in global variables you can deal with in the Click event procedures of the buttons.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveInIowa]

You could also consider using SendKeys for each of the button click events:

Private Sub btn0_Click()
    SendKeys "0"
End Sub

You'll need to set each button's TakeFocusOnClick property to False.

 

[fumei]

The Selstart, SelLength and SelText properties of the TextBox object seems unusable. "

Huh? Unusable? No, I am quite sure they are usable, as they are real properties of a textbox control. However, they may not be usable in the way you would like.

"What I means input number by "soft keyboard" is that I made ten numeric CommandButton which defined caption from "0" to "9" and when press any numeric CommandButton then input the caption as number to the TextBox which cursor located."

Let me see if I understand this. You have ten commandbuttons. Each commandbutton has a caption ("0", "1", "2"...etc.). When any given button is clicked, its Caption ("0", "1", "2"...etc.) is put in as text in whatever textbox has the cursor.

Is that what you are saying?

If this is what you are meaning, then I have to say that if you click a button then, at that point, THAT is where the "cursor" is...not in a textbox. Which is why I was asking about focus.

I take it there a number of textboxes (or at least more than one).

In any case, again, when you click a button THAT is where the focus (or cursor if you like) is, not in a textbox. So if you click the "0" button, you are going to have to tell it (via the _Click event) where to put the "0".

Gerry

 

[softlover]

Sorry for my poor expression. Thanks to Fumei that his explaination is exact in below.

"Let me see if I understand this. You have ten commandbuttons. Each commandbutton has a caption ("0", "1", "2"...etc.). When any given button is clicked, its Caption ("0", "1", "2"...etc.) is put in as text in whatever textbox has the cursor."

I want to explain when click the commandbutton the pre-selected textbox will lost the focus and the selected textbox can't be remembered by the system after focus move to a commandbutton. When I move the cursor to any textbox with "TAB" key(For fast input I need left hand to control the cursor and right hand to click the relatived command to input number to the textbox selected with "TAB" key) in the keyboard I need system variant remember the cursor position or the object.name of the cursor located. Then after click any "0"-"9" button the number can set to the remembered textbox.

The problem is that the textbox haven't TextBox_On_Focus or TextBox_On_CursorMove event to trigger the codes to store the textbox name or position etc.

 

[PHV]

Did you read my post stamped 9 Mar 09 20:58 ?

 

[softlover]

Thanks to all. The Exit event solve the problem.

 

[DaveInIowa]

I'm curious; what were the drawbacks with the SendKeys approach? It seems a lot easier than saving/restoring text boxes and their selection properties.

 

[HarleyQuinn]

The reason I wouldn't use sendkeys is that (unlike the other method) it doesn't send the text specifically to the textbox, rather just simulates pressing a key.

If you're not in the textbox and you press a number button I'd want the value to go into the textbox, not wherever the cursor is at the time.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[DaveInIowa]

The poster's original question referred to "some TextBox", not "a TextBox", leading me to believe there were multiple text boxes, any of which could be the target of the button presses. They also mentioned they should get the position where the cursor is, not was, which would indicate a text box selection is active at the time.

If you're not in the textbox and you press a number button I'd want the value to go into the textbox, not wherever the cursor is at the time.

How would you determine what text box to insert the number into if it's not active?

 

[HarleyQuinn]

Dave,

Agreed . I'd overlooked the multiple textbox implication in the question and assumed single textbox (for some reason I had a calculator style form in my head )

I still wouldn't use sendkeys in this scenario though, I wouldn't generally use sendkeys at all (this is personal preference though) as I don't like that you can't guarantee where the input is going to end up. With the other solution in which the textbox to which the input should go is kept in memory (along with the various Sel... properties) you can be sure as to where the input will be received.

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[fumei]

Just so I understand.

You are using the _Exit event of the textbox to store information (the textbox name) into a variable.

When a commandbutton is clicked, it checks the variable name, and THAT name is used to identfy the textbox to put the commandbutton caption ("0", "1", "2"...etc) text.

Could you post this code?

Gerry

 

[DaveInIowa]

Here's my take on using the TextBox properties. I felt capturing the "active" text box on the Enter event made more sense than the Exit event.

Private myTextBox As Variant

Private Sub btn1_Click()
    SendStringToTextBox ("1")
End Sub

Private Sub btn2_Click()
    SendStringToTextBox ("2")
End Sub

' Same for buttons 3 through 0

Private Sub TextBox1_Enter()
    Set myTextBox = TextBox1
End Sub

Private Sub TextBox2_Enter()
    Set myTextBox = TextBox2
End Sub

Private Sub SendStringToTextBox(Value As String)
    Dim txtLeft As String
    Dim txtRight As String
    
    If (myTextBox Is Nothing) Then Exit Sub
    
    With myTextBox
        txtLeft = VBA.Left$(.Text, .selStart)
        txtRight = VBA.Right$(.Text, Len(.Text) - .selLength - .selStart)
        .Text = txtLeft & Value & txtRight
        .selStart = Len(txtLeft) + 1
    End With
    
End Sub

Strangely, myTextBox needs to be declared as a Variant; this will not work if it is declared as a TextBox.

I agree that SendKeys can be problematic but that's because you're usually activating another application before sending the keystrokes, hoping they get to the intended destination without something stepping in between. That shouldn't be a problem here since the keystrokes would be sent internal to the same form they're called from. I still feel that's a cleaner solution.

 

[fumei]

Private myTextBox As Control

will work.

Gerry

 

[fumei]

Also, if you fully qualify the declaration, you can in fact use TextBox.

Private myTextBox As MSForms.TextBox

but then...

Private myTextBox As TextBox

does work for me. What version are you using??

Gerry

 

[DaveInIowa]

Gerry -- Thanks for the info. I am using Office 2003 SP3.

As you mentioned, As Control & As MSForms.TextBox work for me also; however As TextBox produces a Type mismatch error.

As TextBox must be the equivalent of As Excel.TextBox.

See the bottom post from Combo in thread thread707-1446884.

Just in case someone is not able to find it, Show Hidden Members is available by right-click in the Object Browser.

 

[fumei]

Oh crap, yes I forgot all about the fact this is working from Excel VBA...not Word.

<slaps self>

Gerry