How do I tell Excel, my blinking cursor is in Textbox4 of Userform3...?

[chazrab]

Purpose is to be able to assign a variable X to what I would term for now, the Active Textbox of any userform in an application.

I can't seem to get beyond the following:

Dim x as DataObject
Dim ActiveShape  as Shape

If I can tell Excel what textbox my blinking cursor is in, then I can assign it to variable x.
Once that can be done, then Excel will know what textbox 1'm in.

Thanks for anyone's help.

cr

 

[RoryA]

How would this be used?

 

[chazrab]

Hi Rory - thx for replying. Here's the code block which does exactly as indicated with as few lines as I could make it. Works great
to this point except for the issue with assigning a variable to the active or current highlighted selection in any textbox of any userform in the application:
I tried to explain each step with comments as clear as possible: Image below is Textbox3. Selection is what's copied as below

Private Sub cmdSENDKEYSC_Click()
Dim X As String, y As Variant, rs As Worksheet
X = Me.ListBox1.Value  'just tells Excel the row value to copy from in Listbox1
'y = 'Textbox highlighted selection on a multitextbox userform.  This highlighted selection will be copied to range B1 of row x in Sheet VSEDITS
MsgBox X 'chking to make sure coped row is correct

Sheets("VSEDITS").Range("A1").Value = X 'puts the value of x in row A1 of Sheet VSEDITS
Sheets("VSEDITS").Range("B1").Value = TextBox3.SelText 'here's where the challenge comes in.  I had to identify the copied selection in the code as Textbox3.  'I want to be able to substitute the value of y for any textbox.  It may be Textbox3, next time it may be Textbox4.  Hence, a variable to tell Excel which textbox cursor is in.
'copy A1 and B1 values from VSEDITS to Listbox1 Rowsource sheet, which is just named NEWRESULT

Set rs = Worksheets("NEWRESULT") 'applying the typical FIND method to locate the value of LIstbox1:
    With rs.Range("B:B")
       Set c = .Find(What:=X, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
             If Not c Is Nothing Then
                   c.Offset(, 3) = Sheets("VSEDITS").Range("B1:B1").Value 'locates the correct column to paste the changed data from VSEDITS in
             Else
            End If
   End With
 'now add changes code to copy back to Sheet2.  This is the main data sheet consisting of 31,103 rows of data.  Final step and will be copied, pasted and saved there later.
End Sub

I tried not to give you too much information, yet wanted to be as complete as I could make it.
Just want to tell Excel with a variable y which text selection of which textbox of which userform is being copid

Thx again for all your help, as in the past.

cr

 

[chazrab]

How would this be used?

Hi Rory - any ideas on how to do this ?
Thx
cr

 

[RoryA]

I still don't understand how this would be used. Where are you calling it from?

 

[chazrab]

The code above is in the click event of a button named cmdSENDKEYS on the userform in the image of the userform above which has 5 textboxes: names = Textbox1, Textbox2 , Textbox3, Textbox4 and Textbox5. I'm calling it (running the code) from that button on the userform itself: Private Sub cmdSENDKEYSC_Click() 1st line of code. I just want to tell Excel with a variable which textbox is entered or clicked or the highlighted text is in.

Something like, but does not work:

   If Me.TextBox3.SetFocus Then
       MsgBox "You're in Textbox3"
       Sheets("VSEDITS").Range("B1").Value = TextBox3.SelText
 Else
 If Me.TextBox2.SetFocus Then
        MsgBox "You're in Textbox2"
       Sheets("VSEDITS").Range("B1").Value = TextBox2.SelText
Else
End If
End If
etc...to include every textbox.  The  intentionally incorrect statement I used is only for an example
as a way of telling Excel which textbox the highlighted or selected text is in.

Thanks for all your help.   I hope I've made this clearer.  The above code is run from a button on the userform if I understand "Where are you calling it from" correctly.
cr

 

[RoryA]

If it's on the userform in question, then you can use the activecontrol property of the form.

 

[chazrab]

Hi Rory, thanks for helping. Using Activecontrol may be a better solution than what I've been working on. Below:
I kept working on this and I think I've found "A" solution, not necessarily the ideal one.
1. I first added a new Textbox11 on the suserform
2. Then I added this code in each respective Enter event of each textbox on the userform:

Private Sub TextBox3,2,1,4_Enter()
Me.TextBox3,2,1,4.SetFocus
Me.TextBox3,2,1,4.SelStart = 0
Me.TextBox11.Value = "Textbox3","Textbox2","Textbox1", "Textbox4"
End Sub

'tells Excel to assign to the value of Textbox11 on the userform the name of which Textbox is entered each time. Repeated for all textboxes on the userform with correct Textbox names

3. Lastly, I added if statements in the button code on the userform, which will select the correct textbox:

Private Sub cmdSENDKEYSC_Click()
Dim X As String, rs As Worksheet
X = Me.ListBox1.Value
Sheets("VSEDITS").Range("A1").Value = X
If Me.TextBox11.Value = "Textbox3" Then
   Sheets("VSEDITS").Range("B1").Value = TextBox3.SelText
Else
If Me.TextBox11.Value = "Textbox2" Then
   Sheets("VSEDITS").Range("C1").Value = TextBox2.SelText
Else
If Me.TextBox11.Value = "Textbox1" Then
   Sheets("VSEDITS").Range("D1").Value = TextBox1.SelText
Else
If Me.TextBox11.Value = "Textbox4" Then
   Sheets("VSEDITS").Range("E1").Value = TextBox4.SelText
Else
End If
End If
End If
End If
[code] ' in button code
'Sheets("VSEDITS").Range("B1").Value = TextBox3.SelText
Set rs = Worksheets("NEWRESULT")
With rs.Range("B:B")
Set c = .Find(What:=X, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
    If Me.TextBox11.Value = "Textbox3" Then
            c.Offset(, 3) = Sheets("VSEDITS").Range("B1:B1").Value 'locates the correct column to paste the changed data from VSEDITS in
    Else
    If Me.TextBox11.Value = "Textbox2" Then
           c.Offset(, 2) = Sheets("VSEDITS").Range("C1:C1").Value
   Else
   If Me.TextBox11.Value = "Textbox1" Then
          c.Offset(, 1) = Sheets("VSEDITS").Range("D1:D1").Value
   Else
   If Me.TextBox11.Value = "Textbox4" Then
          c.Offset(, 4) = Sheets("VSEDITS").Range("E1:E1").Value
Else
End If
End If
End If
End If
End If
End With
End Sub

Tested once, seems to work correctly - that is, lets Excel know where to paste any userform highlighted text to in the underlying sheet.

Thanks again for all your help. Should I mark as solution ?