using VB to link to a cell

[rholdren]

While searching I found this link
https://www.mrexcel.com/forum/excel...workbooks.html?highlight=useing+vba+link+cell

But trying to using I am still getting error messages. I tried to just change the path used in this link but with no success

What I am trying to do is have a cell display a value based off of number linked in another workbook.
Example: TextBox (named Names1 will have name entered of a person who receives scores based on multiple performances) On the spreadsheet where the averages are figured (in cell N26) I want the user form to display this average with each score this person receives.

The Current path is "x:\Tracking\Indiviuals\2018 Individuals\[SchJ.xlsx]SchJ'!N26"

So if Names1.Value = Joe Schmoe TexBox2.Value should show the average of each score posted. The spreadsheet already shows the average I just need to link N26 to TexBox2 so it displays on the form. That way as the average changes on the spreadsheet so will the value inside the Textbox2. I can do this on the spreadsheet itself but Joe Schmoe doesn't see the spread sheet they only see the result of the test. Any thoughts? Your help is greatly appreciated.

 

[rholdren]

here is what I actually have (not working of course)

Private Sub TextBox1_Change()
If CSRep.Text = "Alyssa Vangundy" Then
Textbox1.Text = X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!O26
End Sub

 

[Yongle]

how about...

Private Sub TextBox1_Change()
        If CSRep.Text = "Alyssa Vangundy" Then Textbox1.Text = [SIZE=4][COLOR=#ff0000][B]"[/B][/COLOR][/SIZE]X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!O26[SIZE=4][COLOR=#ff0000][B]"
[/B][/COLOR][/SIZE]End Sub

 

[rholdren]

it didn't give me an error but it lists the everything past the = sign as text and doesn't show the value the cell is linked to. It should show something like 98.7

 

[Yongle]

This method is not pretty but it works
Z1 is any empty cell, used as a temporary vehicle to evaluate "=X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!O26"

Private Sub TextBox1_Change()
        If CSRep.Text = "Alyssa Vangundy" Then
            Range("[COLOR=#b22222]Z1[/COLOR]").Formula = "=X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!O26"
            TextBox1.Text = Range("[COLOR=#ff0000]Z1[/COLOR]").Value
            Range("[COLOR=#b22222]Z1[/COLOR]").ClearContents
        End If
End Sub

 

[Yongle]

Just spotted missing apostrophe in previous post
Range("Z1").Formula = "='X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!O26"

 

[Yongle]

Another more direct method, but the syntax is more complcated

Private Sub TextBox1_Change()
  Dim arg As String
  If CSRep.Text = "Alyssa Vangundy" Then
    arg = "'=X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!" & Range("O26").Address(True, True, xlR1C1)        
    TextBox1.Text Application.ExecuteExcel4Macro(arg)            
  End If
End Sub

or use this

arg = "'=X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!R26C15"

 

[Yongle]

Please disregard post#7
-unnecessary "=" crept in when overwriting my test path with your path

Private Sub TextBox1_Change()
  Dim arg As String
  If CSRep.Text = "Alyssa Vangundy" Then
    arg = "'X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!" & Range("O26").Address(True, True, xlR1C1)        
    TextBox1.Text Application.ExecuteExcel4Macro(arg)            
  End If
End Sub

Or going directly for R1C1 notation

arg = "'X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!R26C15"

 

[rholdren]

You all are amazing thank so much for your help. With each visit here I learn more and more and someday hope to be able to contribute and help others. Thanks again