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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,806
Office Version
  1. 2007
Platform
  1. Windows
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
 
Try this.
VBA Code:
Me.TextBox1.Value = GetTextBoxValue("E63")
VBA Code:
Option Explicit

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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try this.
VBA Code:
Me.TextBox1.Value = GetTextBoxValue("E63")
VBA Code:
Option Explicit

Public Function GetTextBoxValue(ByVal rng As String) As String
Dim xlApp As Excel.Application, wb As Workbook, sht As Worksheet
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("<span>C:\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2010 - 2011\SUMMARY 2010 - 2011.xlsx</span>
")
Set sht = wb.Sheets(1)
GetTextBoxValue = sht.Range(rng).Value
wb.Close
xlApp.Quit
End Function
Sorry, Copy Past messed up the file name. Remove <span> and </span>
 
Upvote 0
I read post #7 & now put all the value on the same sheet.
So the Textbox now needs to get value from that sheet, example cell T1 for TextBox1

I cant then do Textbox2 for the next cell etc
 
Upvote 0
I'm not sure I know how to explain it any further.
Paste this code into your Form code module
VBA Code:
Option Explicit

Public Function GetTextBoxValue(ByVal rng As String) 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(rng).Value
wb.Close
xlApp.Quit
End Function
Assign your TextBoxes like this
VBA Code:
Private Sub TextBox1_Change()
TextBox1.Value = GetTextBoxValue("E63")
End Sub

VBA Code:
Private Sub TextBox2_Change()
TextBox2.Value = GetTextBoxValue("B2") ' Or whatever cell it's coming from
End Sub

And again, this only works if all of the cells are in the same (closed) Workbook.
 
Upvote 0
Thanks but thats why i said each sheet for each year is in a different folder.
I then mentioned putting the values on the same sheet then have the code just look in the cell on that sheet.

Now done using this,Thanks.

VBA Code:
Private Sub UserForm_Initialize()
TextBox1.Value = Sheets("VALUES").Range("T1").Value
TextBox2.Value = Sheets("VALUES").Range("T2").Value
TextBox3.Value = Sheets("VALUES").Range("T3").Value
TextBox4.Value = Sheets("VALUES").Range("T4").Value
TextBox5.Value = Sheets("VALUES").Range("T5").Value
TextBox6.Value = Sheets("VALUES").Range("T6").Value
TextBox7.Value = Sheets("VALUES").Range("T7").Value
TextBox8.Value = Sheets("VALUES").Range("T8").Value
TextBox9.Value = Sheets("VALUES").Range("T9").Value
TextBox10.Value = Sheets("VALUES").Range("T10").Value
TextBox11.Value = Sheets("VALUES").Range("T11").Value
TextBox12.Value = Sheets("VALUES").Range("T12").Value
TextBox13.Value = Sheets("VALUES").Range("T13").Value
TextBox14.Value = Sheets("VALUES").Range("T14").Value
End Sub
 
Upvote 0
VBA Code:
Private Sub UserForm_Initialize()
Dim tb, i As Long
i = 1
For Each tb In Me.Controls
If TypeName(tb) = "TextBox" Then
        
        tb.Value = Sheets("VALUES").Range("T" & i).Value
        i = i + 1
    End If
Next tb
End Sub
 
Upvote 0
VBA Code:
Private Sub UserForm_Initialize()
Dim tb, i As Long
i = 1
For Each tb In Me.Controls
If TypeName(tb) = "TextBox" Then
       
        tb.Value = Sheets("VALUES").Range("T" & i).Value
        i = i + 1
    End If
Next tb
End Sub
Disregard this answer. I think I know what you're saying now. Stand by.
 
Upvote 0

Forum statistics

Threads
1,224,505
Messages
6,179,151
Members
452,891
Latest member
JUSTOUTOFMYREACH

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
Back
Top