Need the 'simplest' way to determine if a textbox is in focus

  • Code
    If {tBox1 is in Focus} Then
        myCell = "A" & CStr(curRow)
    End If
    If {tBox2 is in Focus} Then
        myCell = "B" & CStr(curRow)
    End If

    If the cursor is moved to tBox2 (via mouse-click, Tab or Enter key), the relevant column is to be selected. (I've experimented with ActiveControl and ActiveControl.Name but they only return "False" as a value). Thanks for your help.

    Edited 2 times, last by Splat ().

  • ActiveControl.Name would only return "False" if you have a control named "False" and it's active. ;)

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Thanks JD, worked like a charm!

    |Why do you want to select cells?

    It's a single cell; .Select and .Activate {seem} to do the same thing here. Cheers!

    ActiveControl.Name would only return "False" if you have a control named "False" and it's active. ;)

    You're correct! ActiveControl.Name was actually returning the names of the navigation buttons. Thanks.

  • You are missing the point. It is not usually necessary to activate or select a cell.

    What are you doing after the TextBox is picked..

    Instead of inputting data directly into the spreadsheet, the form acts as the UI. Selecting an input-box on the form should select the corresponding data location on the sheet, so that the entry made into the box is saved to the appropriate cell in the sheet.

    So, wouldn't you agree then that I am on point? ;)

  • Well, all I can say is that it works just as I expect it to. The destination changes in real time--same row, different column (C, D, E, ... L)--just by clicking the textbox.

  • So I decided to post this demo for the benefit of those who might be watching this thread. Here are two screen grabs: noSelect has the cell Range .Select line disabled and yesSelect has it enabled. The result speaks for itself. How is that inefficient? (rhetorical).


  • Hi Splat,

    Roy's point is that there is no scenario in which coding to select the cell will be more efficient then just writing to or taking information from the cell directly. Selecting ranges tends to be used by early coders before they learn how to navigate things properly, I believe Roy is trying to determine the overall intent of your coding so he can point you in the right direction but if you are happy that the system does what you are after then stick with it for now, you will probably correct it in the future.

    NB: you can easily load a full excel file here and get a working example returned to you where appropriate, i generally would not download and open a zip file.

    Glad my earlier post helped!



  • Why are you bothering asking questions if you know best.

    Yes your code works but selecting is not necessary. As you have been told twice - selecting is usually unnecessary. If you bothered answering questions about your use of the code then you would probably end up with a better knowledge of VBA.

    Without selecting you can easily enter the value in the cell

    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cells(1, 1).Value = Me.TextBox1.Value
    End Sub

    If it's always the same cell then you can use the TextBox's ControlSource Property.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!