Cell Value to External Workbook Textbox Issue

[dacuna.87]

Hi,


Greetings to all, I just start learning vba macros some weeks ago and it is pretty awesome I love it. Today I'm facing with the following issue hope you can help me out!


I'm trying to make a macro that send values from an specific cell to an external Textbox located in a different workbook. I tried a lot of methods but I simply cant get to the right formula or a way to call the Textbox control into my main workbook.


Right now I have the below code that just copy a cell value to other cell in a different workbook:


------ CODE ---------------------------------------------------

Sub cmdOK_Click()


Dim OriginalWorkBook As Workbook

Dim Source As Workbook


Const MyDir As String = "C:Test"

Set Source = Workbooks.Open(MyDir & "B.xlsm")


Source.Sheets("Sheet1").Activate


ActiveSheet.Range("C4") = Range("C4")


End Sub


------ END CODE ---------------------------------------------


Please find below a link with the excel spreadsheets that I'm using, copy these folders on "C:" in order for this macro to work.


http://www.mediafire.com/?9fp9at6amd9vlec


Let me know your thoughs! And thanks a lot in advance!

 

[Luke M]

This is your problem line

[pre]

ActiveSheet.Range("C4") = Range("C4")
The latter Range doesn't have a parent object stating which Sheet to use. In this case, VB assumes that you want to use the active sheet. Thus, it's really doing

[pre][code]ActiveSheet.Range("C4") = ActiveSheet.Range("C4")

[/pre]
and not doing anything. To avoid confusion, best practice would be to call out all parents, like this:

Source.Sheets("Sheet1&#34.Range("C4&#34 = ThisWorkbook.Sheets("My Sheet&#34.Range("C4&#34[/code][/pre]
Note how both sides have the workbook and worksheet parent included. ThisWorkbook always refers to the workbook which the code is contained in.

 

[dacuna.87]

Hi Luke,


Thanks for your prompt response!


Yes I just saw that hehe, but It looks like I copy it wrong on my first post, but I think that on the file that I uploaded on mediafire, the code is OK like yours.


Could you please take a look at the files that i uploaded on mediafire and give me some support on how to do the same but from one cell to an external TextBox please, I do not find a way to reference or call an external textbox and I'm getting crazy finding the way!


Thanks for your help Luke!

 

[Luke M]

I'm afraid I can't access shared workbooks from my location, but I'll do my best w/o it. Shapes are funny things...for some reason, it won't let you change the properties directly, you often have to select them first. Try this.

[pre]

Sub cmdOK_Click()

Dim OriginalWorkBook As Workbook
Dim Source As Workbook

Const MyDir As String = "C:Test"
Set Source = Workbooks.Open(MyDir & "B.xlsm")

'Select the sheet
Source.Sheets("Sheet1").Activate
'select shape
ActiveSheet.Shapes("MyBox").Select
'set value
Selection.Text = ThisWorkbook.Sheets("My Sheet").Range("C4")

End Sub

[/pre]

 

[SirJB7]

Hi, dacuna.87, Luke M!


Sorry for the interruption. I downloaded A & B workbooks, added macro to second button of A (to cmdOK_click) and changed slightly Luke M's code to this so as to try it with my folder's name:

-----

[pre]

Sub cmdOK2_Click()

Dim OriginalWorkBook As Workbook
Dim Source As Workbook

Const MyDir As String = "C:Test"
'Set Source = Workbooks.Open(MyDir & "B.xlsm")
Set Source = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & "B.xlsm")

'Select the sheet
Source.Sheets("Sheet1").Activate
'select textbox and set value
ActiveSheet.TextBox1.Text = ThisWorkbook.Sheets("Sheet1").Range("C4")

End Sub

[/pre]
-----


And it works fine.


Here's the link:

http://dl.dropbox.com/u/60558749/Cell%20Value%20To%20An%20External%20Textbox%20Issue%20-%20A%20%28for%20dacuna.87%20from%20Luke%20M%20at%20chandoo.org%29.xlsm


Hope it helped you. Credit to Luke M.


Regards!

 

[Luke M]

Thanks for downloading and adapting the code, SirJB7. It is annoying not being able to actually see the workbooks that people are posting. =(

 

[SirJB7]

@Luke M

Hi!

Don't even mention it, man...

Regards!

PS: BTW I forgot to turn on your CASFFML trick

 

[dacuna.87]

Thanks a lot guys! Exactly what I was looking for! Have a great day! :D

 

[SirJB7]

Hi, dacuna.87!

Glad you solved it. All credit to Luke M, I just arrived at dessert time.

Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!

 

[Luke M]

As SirJB7 said, glad we could help.