using VB to link to a cell

rholdren

Board Regular
Joined
Aug 25, 2016
Messages
97
Office Version
  1. 365
  2. 2019
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

rholdren

Board Regular
Joined
Aug 25, 2016
Messages
97
Office Version
  1. 365
  2. 2019
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
how about...

Code:
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
 
Last edited:

rholdren

Board Regular
Joined
Aug 25, 2016
Messages
97
Office Version
  1. 365
  2. 2019
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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"

Code:
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
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Just spotted missing apostrophe in previous post
Range("Z1").Formula = "='X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!O26"
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another more direct method, but the syntax is more complcated

Code:
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
Code:
arg = "'=X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!R26C15"
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
:eek: Please disregard post#7
-unnecessary "=" crept in when overwriting my test path with your path

Code:
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
Code:
arg = "'X:\Audit Tracking\Reps\2018 Reps\[VanA.xlsx]VanA'!R26C15"
 
Last edited:

rholdren

Board Regular
Joined
Aug 25, 2016
Messages
97
Office Version
  1. 365
  2. 2019
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 :)
 

Forum statistics

Threads
1,148,334
Messages
5,746,162
Members
423,997
Latest member
moofish87

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top