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

[Patriot2879]

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...

[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]

 

[Patriot2879]

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.

[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]

 

[dmt32]

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

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

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

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

Dave

 

[Patriot2879]

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]

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]

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

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

Dave

 

[Patriot2879]

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

Great thank you Dave

 

[Patriot2879]

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.

[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]

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]

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.

[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