Dynamically update standard textbox (not UF) with cell data

[jlarcher01]

Hi All! I've been scouring the web trying to figure this out and I'm sure it's relatively simple but for the life of me I can't figure it out which is probably due to my lack of understanding of the coding hierarchy and scope needed. I've tired numerous different things and I wouldn't be able to provide any meaningful code that has gotten me anywhere although I'm happy to provide it if needed. Thanks in advance for the help...

-I have a spreadsheet full of data on sheet 1
-I have an image on sheet 2.
-I've inserted a number of ActiveX textboxes on this image to visually show the data on sheet 1.
-All I wish to do is update each text box with respective data from the same column every time but different row.
-For example, TextBox1 will be tied to column 1, TextBox2 will be tied to column 2, etc up to 22
-Whenever the user clicks/selects/moves to a different row (no matter where they click) each TextBox is updated with the new
data respectively at the now active row but same column number locations.

So if Row5 is the active row, TextBox1 will update data from Row5, Column,1. TextBox2 will show Row5, Column 2, etc.
If Row9 is now made active, TextBox1 will show data from Row9, Column1. TextBox2 will show data from Row9, Column9, etc.
If there is no data at that specific location then nothing shows.

Thanks again for the help.

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

This script will perform in all the activex textboxes you have on your sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ans As String
ans = ActiveCell.Row
Dim cc As Long
cc = 0
Dim obj As OLEObject
    For Each obj In ActiveSheet.OLEObjects
        cc = cc + 1
        If TypeOf obj.Object Is MSForms.TextBox Then
                obj.Object.Text = Cells(ans, cc).Value
        End If
    Next
End Sub

OK

 

[jlarcher01]

Thanks for the reply. I commented out all my other code just to be safe, pasted in your code and compiled. TextBoxes are not showing anything. ??????

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

This script will perform in all the activex textboxes you have on your sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ans As String
ans = ActiveCell.Row
Dim cc As Long
cc = 0
Dim obj As OLEObject
    For Each obj In ActiveSheet.OLEObjects
        cc = cc + 1
        If TypeOf obj.Object Is MSForms.TextBox Then
                obj.Object.Text = Cells(ans, cc).Value
        End If
    Next
End Sub

OK

 

[My Aswer Is This]

What does this mean:

pasted in your code and compiled.

What does compiled mean?

And all your Text Boxes are Activex Text Boxes


And you must have some values in the cells you select

If you move into Range "A1" the value in textbox1 will be the value in that range
If you move into Range "A2" the value in textbox1 will be the value in that range

Be assured I test all my scripts.

 

[My Aswer Is This]

You need to create a new Worksheet.
Insert 3 new Activex Textboxes

Now go into Column "A" and insert some text in rows 1 to 5
And enter some text into column "B" rows 1 to 5
And enter some text into column "C" rows 1 to 5

Now install the code I gave you into the sheet the way I mentioned before.

Now when you move into Range "A1" the value in Range "A1" should be seen in Textbox1
Now when you move into Range "A2" the value in Range "A1" should be seen in Textbox1
Now when you move into Range "A2" the value in Range "A1" should be seen in Textbox1

And the values in range "B1" should be seen in Textbox2
And the value in Range "C1" should be seen in Textbox3

Your Textboxs must be numbered properly

The default is always 1 the 2 then 3

If this works then you need to go back to your original sheet and maybe rename them.

If you previously deleted some they may be out of order.

 

[jlarcher01]

What does this mean:

pasted in your code and compiled.

What does compiled mean?

And all your Text Boxes are Activex Text Boxes


And you must have some values in the cells you select

If you move into Range "A1" the value in textbox1 will be the value in that range
If you move into Range "A2" the value in textbox1 will be the value in that range

Be assured I test all my scripts.

Compiled; Debug tab-> Compile VBAProject. I pasted your code in the Sheet2 (Code) window.

Yes, there is a large amount of data in sheet1.

Yes, they are all ActiveX; Developer tab -> Insert, Text Box (ActiveX controls)

Does this make sense?

 

[jlarcher01]

You need to create a new Worksheet.
Insert 3 new Activex Textboxes

Now go into Column "A" and insert some text in rows 1 to 5
And enter some text into column "B" rows 1 to 5
And enter some text into column "C" rows 1 to 5

Now install the code I gave you into the sheet the way I mentioned before.

Now when you move into Range "A1" the value in Range "A1" should be seen in Textbox1
Now when you move into Range "A2" the value in Range "A1" should be seen in Textbox1
Now when you move into Range "A2" the value in Range "A1" should be seen in Textbox1

And the values in range "B1" should be seen in Textbox2
And the value in Range "C1" should be seen in Textbox3

Your Textboxs must be numbered properly

The default is always 1 the 2 then 3

If this works then you need to go back to your original sheet and maybe rename them.

If you previously deleted some they may be out of order.

I created a new sheet and did as you instructed and nothing worked.

Just to clarify again, the textboxes are on sheet2 and the data is on sheet1.

 

[jlarcher01]

You need to create a new Worksheet.
Insert 3 new Activex Textboxes

Now go into Column "A" and insert some text in rows 1 to 5
And enter some text into column "B" rows 1 to 5
And enter some text into column "C" rows 1 to 5

Now install the code I gave you into the sheet the way I mentioned before.

Now when you move into Range "A1" the value in Range "A1" should be seen in Textbox1
Now when you move into Range "A2" the value in Range "A1" should be seen in Textbox1
Now when you move into Range "A2" the value in Range "A1" should be seen in Textbox1

And the values in range "B1" should be seen in Textbox2
And the value in Range "C1" should be seen in Textbox3

Your Textboxs must be numbered properly

The default is always 1 the 2 then 3

If this works then you need to go back to your original sheet and maybe rename them.

If you previously deleted some they may be out of order.

I pasted the data into the first few cells of sheet 2 and it works. So now I just need to make it work from sheet1 and then automatically show sheet2 after a new row is selected/made active.

 

[My Aswer Is This]

I missed that part.

The data is in sheet (1) and Textboxes in sheet(2)

I will have to rethink how to do this.
And every time you select a new row you want to activate sheet (2)
That looks like something odd but I will try and tell the script to do that.

 

[My Aswer Is This]

Try this:

This script should be put into Sheet(1) and Textboxes will be in Sheet(2)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ans As String
ans = ActiveCell.Row
Dim cc As Long
cc = 0
Dim obj As OLEObject
    For Each obj In Sheets(2).OLEObjects
        cc = cc + 1
        If TypeOf obj.Object Is MSForms.TextBox Then
                obj.Object.Text = Cells(ans, cc).Value
        End If
    Next
    Sheets(2).Activate
End Sub