VBA Link Text Box to Cell Value

[craymer]

I have two workbooks, BookFile and PayAppSOV. Both workbooks have an identical worksheet called "SOV." I would like to import the values from BookFile "SOV" into PayAppSOV "SOV." I actually have this functionality working using the code below:

[pre]

Sub ImportSOV()

' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook

' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
filter = "Text files (*.xlsm),*.xlsm"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)

Set customerWorkbook = Application.Workbooks.Open(customerFilename)

' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Sheets("SOV")
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Sheets("SOV")

targetSheet.Range("B7", "C30").Value = sourceSheet.Range("B7", "C30").Value

' Close customer workbook
customerWorkbook.Close

End Sub

[/pre]

However, in the workbook BookFile, worksheet Non-Labor, cells "C2" and "C3," I would like to link to two text boxes in PayAppSOV. I am able to hobble through VBA using Google and searching for solutions to my problems. The issue lies that I don't truly understand how to code so these minor changes throw me for a loop. Your help is appreciated!


https://www.dropbox.com/sh/0bc2ew9na20u9pu/u09HOVzIJ4


Thanks,


Chad

 

[lohithsriram]

Hi Craymer,


Check if this link is of some use for you.


http://chandoo.org/forums/topic/cell-value-to-external-workbook-textbox-issue

 

[craymer]

Lohith, thank you very much for the link. I read the thread in detail but it appears my code is written slightly different. It doesn't like "textbox1" below. I have two textboxes, how does it know which is which?


Set targetSheet = PayAppSOV.Sheets("SOV")

Set sourceSheet = BookFile.Sheets("Non-Labor")


targetSheet.textbox1.Value = sourceSheet.Range("C3").Value

 

[Smallman]

Hi Craymer


There is a problem with the Textboxes on the PayApps - SOV sheet. This will solve the problem. Go onto the Bookfile workbook Sheet SOV. Now Copy the TextBoxes (both of them) and paste them on the PayAppsov workbook sheet SOv. Now delete the two textboxes which don't seem to be performing.


This is a simple approach but it worked for me. It is not a vba solution so you will not have the flexibility to change the sheet that is being imported to your PayApps Book. However if this is your normal upload it will fix this problem.


Take care


Smallman