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

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
958
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:

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
958
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
5,280
Office Version
2013
Platform
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
958
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
958
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
5,280
Office Version
2013
Platform
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
958
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
958
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
958
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
5,280
Office Version
2013
Platform
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
 

Forum statistics

Threads
1,082,333
Messages
5,364,675
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top