View column A value based on the active cell

[JohnBi]

Hello to everyone,
how may I see in a TextBox the value of a cell in colum A based on the active cell?
For example:
- Active cell = H25 --> Textbox = A25 value
- Active cell = H20 --> Textbox = A20 value
- Active cell = C125 --> Textbox = A125 value
- Active cell = F87 --> Textbox = A87 value

Thank you for your help.
Regards
John

 

[Joe4]

You would have a line of code something like:

TextBox1.Value = Cells(ActiveCell.Row, "A").Value

 

[JohnBi]

You would have a line of code something like:

TextBox1.Value = Cells(ActiveCell.Row, "A").Value

Thank you Joe4,
this is what I am looking for.
However please, if I am allowed to ask another question, how is possible to have the value updated every time I change the row?

Thank you again.
Regards
John

 

[Joe4]

You can use "Worksheet_Change" event procedure code if you want it to update automatically upon some manual update being made somewhere on your sheet.
Under what conditions/criteria do you want this code to fire?
Specifically, what range of cells do you want to watch for a change in order to fire this code?

 

[JohnBi]

I have a lot of survey on paper to "translate" 1:1 to excel (1 survey = 1 file)
Since each question has been numbered and the excel template has the question's number in column A.
I would like to hide excel column A (to avoid confusion) and return to the userform that I am using to fill the file the question's reference number.
This in the hope to easy the data entry massive job (1600 survey by 8 pages each!)
The range is C11:H133.
Thank you for your help, much appreciated.
John

 

[Joe4]

I have a lot of survey on paper to "translate" 1:1 to excel (1 survey = 1 file)
Since each question has been numbered and the excel template has the question's number in column A.
I would like to hide excel column A (to avoid confusion) and return to the userform that I am using to fill the file the question's reference number.
This in the hope to easy the data entry massive job (1600 survey by 8 pages each!)

That seems like an entirely different question that the one you posted here, and therefore should probably be posted to its own new thread.

The range is C11:H133.

That is what I was looking for. To get that line of code I gave you to automatically run when an update happens in that range, do the following:
1. Go to the sheet you want to apply this to.
2. Right-click on the sheet tab name at the bottom of the screen
3. Select "View Code"
4. Paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)
'   See if update happened in range C11:H133
    If Not Intersect(Target, Range("C11:H133")) Is Nothing Then
        TextBox1.Value = Cells(ActiveCell.Row, "A").Value
    End If
End Sub

Because you have placed this code in the specific Sheet module, and it is a "Worksheet_Change" event procedure, it will automatically run whenever a manual update is made to range C11:H133.

 

[JohnBi]

That seems like an entirely different question that the one you posted here, and therefore should probably be posted to its own new thread.


That is what I was looking for. To get that line of code I gave you to automatically run when an update happens in that range, do the following:
1. Go to the sheet you want to apply this to.
2. Right-click on the sheet tab name at the bottom of the screen
3. Select "View Code"
4. Paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)
'   See if update happened in range C11:H133
    If Not Intersect(Target, Range("C11:H133")) Is Nothing Then
        TextBox1.Value = Cells(ActiveCell.Row, "A").Value
    End If
End Sub

Because you have placed this code in the specific Sheet module, and it is a "Worksheet_Change" event procedure, it will automatically run whenever a manual update is made to range C11:H133.

Hi,
I get runtime error 424 when I made a change in the range.

Regards
John

 

[Joe4]

Did you update "TextBox1" in the code to match the actual name of your text box?

 

[JohnBi]

Did you update "TextBox1" in the code to match the actual name of your text box?

Yes, it is TextBox5 of the userform. The userform is also popup.
See picture for a bettere reference.

Regards and always thanks
Jonh

 

[Joe4]

OK, I thought you just had a textbox on worksheet directly.
If it is textbox on a UserForm, then you just should put the one line of code I originally gave you to whatever code is bringing up the UserForm.