Enter value in textbox userform from a closed sheet on my pc

[ipbr21054]

Hi.
I have a userform with various textboxes.
I wish to populate each of these textboxes from sheets that are closed & on my pc.

Please advise how i write this correctly so the value is shown in the textbox in question.

TextBox1 should show the value from this files path

C:\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2010 - 2011

Excel sheet is called SUMMARY 2010 - 2011.xlsl

The value is in cell E63


Once advise i can the follow / edit it for the other TextBoxes
Many Thanks

 

[Skyybot]

Try this: I assume that Cell E63 is on First Sheet in file.
Add this Function to a module

Option Explicit

Public Function GetTextBoxValue() As String
Dim xlApp As Excel.Application, wb As Workbook, sht As Worksheet
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("C:\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2010 - 2011\SUMMARY 2010 - 2011.xlsx")
Set sht = wb.Sheets(1)
GetTextBoxValue = sht.Range("E63").Value
wb.Close
xlApp.Quit
End Function

And use like following:

Me.TextBox1.Text = GetTextBoxValue

 

[ipbr21054]

There is only the one sheet.
What do i call the module or how does it know what to look for.

 

[ipbr21054]

Dont think that will work as the sheets in question will be more that 1 with different folders.

 

[Skyybot]

It's a Function. You can place it anywhere. I would put it in Form code module. Or, insert a new Module and place it there. Call with

Module1.GetTextBoxValue

 

[ipbr21054]

See post #4

 

[Skyybot]

That would be for one specific TextBox. It could be modified so that you could enter a Workbook name to retrieve the Value. Or, if the Values are in the same Workbook then you could add a Range argument.

 

[ipbr21054]

Can you advise range arguement for me to try please

 

[Skyybot]

Sure. But first explain a little more. Let's say you have multiple TextBoxes. Are the values for ALL of them stored in the same Workbook but in different Cells? Will this code ever be used to retrieve values from a different Workbook?

 

[ipbr21054]

I will get the figures & put on sheet where the code for that Textbox will then lok in a cell.
Something like this but one that works, ha ha
I will then copy the code for each & edit the T1 to the correct cell

Private Sub TextBox1_Change()
TextBox1.Value = Range("T1").TextBox1.Value
End Sub