Copy cell value from a different workbook into a textbox in a userform

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hi, good afternoon, I have a userform with TextBox310 where I want the textbox to show the info from another workbook for example the work book is located in 'G:INDAY.xlsm ' and the info is in sheet1 cell S17, please can you help?
I have tried...
Code:
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#333333][FONT="Courier New"]Private Sub TextBox310_Change()[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#333333][FONT="Courier New"]Dim WB as Workbook[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#333333][FONT="Courier New"]Set WB = Workbook.CopyFileName:="G:\INDAY.xlsm"[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#333333]WB.Worksheets(Sheet1).Cells=”S17”[/COLOR][FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#333333][FONT="Courier New"]End Sub[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Re: How to copy cell vaule from a different workbook into a textbox in a userform

Hi I have also tried the following code, hope you can help please.
Code:
[COLOR=#333333][FONT=Courier New]Private Sub TextBox310_Change()[/FONT]
[/COLOR][FONT=Courier New][COLOR=#333333]Dim WB as Workbook[/COLOR][/FONT]
[COLOR=#333333][FONT=Courier New]Set WB = Workbook.Value FileName:="G:\INDAY.xlsm"[/FONT][/COLOR]
[COLOR=#333333]WB.Worksheets(Sheet1).Cells=”S17”
[/COLOR][COLOR=#333333][FONT=Courier New]End Sub[/FONT][/COLOR]
 
Last edited:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,508
Office Version
  1. 2019
Platform
  1. Windows
Re: How to copy cell vaule from a different workbook into a textbox in a userform

John Walkenbach published a nice function that may do what you want


Place code in a STANDARD module

Code:
Function GetCellValue(Filepath As String, FileName As String, _
                      SheetName As String, CellAddress As String) As Variant
'modified from John Walkenbach's routine:
'Retrieves a value from an open or closed workbook
    Dim Arg As String, PathSep As String
    PathSep = Application.PathSeparator
    If Right(Filepath, 1) <> PathSep Then Filepath = Filepath & PathSep
    
'Make sure the file exists
    If Not Dir(Filepath & FileName) = vbNullString Then
'Create the argument
        Arg = "'" & Filepath & "[" & FileName & "]" & SheetName & "'!" & _
        Range(CellAddress)(1).Address(ReferenceStyle:=xlR1C1)
'Execute an XLM macro
        GetCellValue = ExecuteExcel4Macro(Arg)
    Else
        GetCellValue = "Error"
    End If
End Function

I've changed it a little from published code but hopefully, will still work as intended.


to put value in your textbox

Code:
Me.TextBox1.Text = GetCellValue(Filepath:="G:\", FileName:="INDAY.xlsm", SheetName:="Sheet1", CellAddress:="S17")

I have included use of argument names to make it a little clearer to you in passing them to the function but you can exclude them

Code:
Me.TextBox1.Text = GetCellValue("G:\", "INDAY.xlsm", "Sheet1", "S17")


Dave
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Re: How to copy cell vaule from a different workbook into a textbox in a userform

Thank you Dave for this I shall try it in the morning. What do I do if I need to add more than one cell? For example S17, T17, U17?
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081

ADVERTISEMENT

Re: How to copy cell vaule from a different workbook into a textbox in a userform

With extra textboxes? Sorry for got to add this in previous message
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,508
Office Version
  1. 2019
Platform
  1. Windows
Re: How to copy cell vaule from a different workbook into a textbox in a userform

With extra textboxes? Sorry for got to add this in previous message

just change cell address in last argument

Rich (BB code):
Me.TextBox2.Text = GetCellValue("G:\", "INDAY.xlsm", "Sheet1", "T17")

Dave
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081

ADVERTISEMENT

Re: How to copy cell vaule from a different workbook into a textbox in a userform

Great thank you Dave
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Re: How to copy cell vaule from a different workbook into a textbox in a userform

Hi Dave I have done as described but the info has not goneinto TextBox310, please see code below that I have put in for the textbox, and Ihave put the other code into the module.
Code:
[FONT=Times New Roman][/FONT]
[COLOR=#574123]Me.TextBox310.Text = GetCellValue(Filepath:="[/COLOR] [COLOR=#574123]G:\TEAM\ES_Mtr_Tech_Ops\Customer ManagementCentre\Utilisation\Complex\Capacity test\", FileName:="[/COLOR] [COLOR=#574123]CapacitytestDATA.xlsx", SheetName:="Data",CellAddress:="V2")[/COLOR]
[FONT=Times New Roman][/CO[/FONT]DE]

[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Re: How to copy cell vaule from a different workbook into a textbox in a userform

Hope you can advise where I have gone wrong?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,508
Office Version
  1. 2019
Platform
  1. Windows
Re: How to copy cell vaule from a different workbook into a textbox in a userform

Hi Dave I have done as described but the info has not goneinto TextBox310, please see code below that I have put in for the textbox, and Ihave put the other code into the module.
Code:
[COLOR=#574123]Me.TextBox310.Text = GetCellValue(Filepath:="[/COLOR] [COLOR=#574123]G:\TEAM\ES_Mtr_Tech_Ops\Customer ManagementCentre\Utilisation\Complex\Capacity test\", FileName:="[/COLOR] [COLOR=#574123]CapacitytestDATA.xlsx", SheetName:="Data",CellAddress:="V2")[/COLOR]
[FONT=Times New Roman][/CO[/FONT]DE]

[/COLOR][/SIZE][/FONT]

[/QUOTE]

Code looks ok - where in your project is the textbox calling the code?

I am out most of day so reply's will be slow.

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,129,365
Messages
5,635,845
Members
416,885
Latest member
hs11

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