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 Dave this is the whole code. What do you mean calling?

Private Sub TextBox310_Change()
Me.TextBox310.Text = GetCellValue(Filepath:="G:\TEAM\ES_Mtr_Tech_Ops\Customer Management Centre\Utilisation\Complex\Capacity test\", FileName:="CapacitytestDATA.xlsx", SheetName:="Data", CellAddress:="V2")
End Sub

 

[Patriot2879]

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

And this is the code I put into Module2.

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

 

[dmt32]

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

Hi Dave this is the whole code. What do you mean calling?

Private Sub TextBox310_Change()
Me.TextBox310.Text = GetCellValue(Filepath:="G:\TEAM\ES_Mtr_Tech_Ops\Customer Management Centre\Utilisation\Complex\Capacity test\", FileName:="CapacitytestDATA.xlsx", SheetName:="Data", CellAddress:="V2")
End Sub

I suspect you are using the wrong event for what you are trying to do. Assign that code to a button on your form & see if returns value you want.

dave

 

[Patriot2879]

Hi there. Sorry not following what you mean. I just need what is in the cell in another workbook to show in the text box. Sorry do you mean you want me to add a button?

 

[dmt32]

Hi there. Sorry not following what you mean. I just need what is in the cell in another workbook to show in the text box. Sorry do you mean you want me to add a button?

The TextBox change event triggers went you type something in to it - You want a value returned to that textbox from another workbook which code provided should do, but you something needs to happen in your project to make that code to run

- without knowing what you are doing difficult to suggest where in your project the code should reside.

Perhaps helpful if you could paost section of code in your form & explain what it is you are doing in in it (e.g. entering a search value in another textbox?) to expect values to populate textboxes.

 

[Patriot2879]

Hi thanks for getting back to me. Can I add my project to dropbox please tomorrow for you to look at? What I have is a userform which works with another workbook. But one part of it i have several text boxes which I just want to show what is in a cell in another workbook. For example it will have the word 'Mide' in a cell and I want that to show in the text box in the userform

 

[Patriot2879]

Hi good morning please see below the link to my file in dropbox hop wyou can help please.
https://www.dropbox.com/s/t8sxysjludtdufk/Capacity4.42.xlsm?dl=0

 

[Patriot2879]

Hopefully you can help me please with this?

 

[Patriot2879]

Hi good morning please see below the link to my file in dropbox hop wyou can help please.
[FONT=Times New

 

[Patriot2879]

Hi all hoping you can help please? I am trying to get a cell value from another workbook and to show the value into my textbox which is in the userform.

Private Sub TextBox310_Change()
Me.TextBox310.Text = GetCellValue(Filepath:="G:\TEAM\ES_Mtr_Tech_Ops\Customer Management Centre\Utilisation\Complex\Capacity test\", FileName:="CapacitytestDATA.xlsx", SheetName:="Data", CellAddress:="V2")
End Sub[\CODE]