Copy userform data to another opened workbook

[YingFa]

Hello,

I have the following code to open a workbook called file1.xlsm from a userform. I would like to copy textbox1 to cell A1 in the opened file1 workbook but the code that I have is not working for that. It only opens the workbook but it does not copy textbox1 value into cell A1. Can I please have your help on this?

Private Sub CommandButton1_Click()

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
Application.ScreenUpdating = False
xlApp.Visible = True
xlApp.Workbooks.Open Filename:="Z:\QZX\Project\File1.xlsm"

Dim Workbook As Workbook
Dim sheet As Worksheet
Set Workbook = Workbooks("File1")
Set sheet = Worksheets("File1")
sheet.Cells(1, 1) = Me.TextBox1.Value

Application.ScreenUpdating = True

End Sub

Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.

Shouldn't the "me.textbox1.value" be "me.texbox1.text"?

Not necessarily.

 

[SSF1590]

Shouldn't the "me.textbox1.value" be "me.texbox1.text"?

Hi. I tried with me.textbox1.value and it is copying what is entered in textbox1 to the workbook. I think it would work with both .value and .text. Would it better text?

 

[glfiedler]

Please ignore my "value/text" comment. Further research on the web indicates ".value" would be appropriate.

 

[SSF1590]

Understood! Thank you very much glfiedler.

 

[YingFa]

You have that problem because you are opening the file in a new instance of Xl.

Hello again. I forgot to say that I need to work in the open workbook while the userform is active that is why I had the below code at the beginning. Is there anyway I can make your code work and still make changes to the opened workbook while the userform is active?

My code at the beginning:

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
Application.ScreenUpdating = False
xlApp.Visible = True
xlApp.Workbooks.Open Filename:="Z:\QZX\Project\File1.xlsm"

Thank you

 

[Fluff]

In post#9 you said

Thank you very much Fluff! The code you provided is working perfectly. I can’t say thank you enough for your help!

So I'm not sure what you mean.

 

[SSF1590]

In post#9 you said
So I'm not sure what you mean.

Hello Fluff. Yes, I said your code was working perfectly because it is. The thing is if I use your code I cannot work with the workbook that the code is opening while having the userform activated/opened.
Before I used to have the below code because I need to be able to work/write in the opened workbook while having the userform active. If I use your code, I cannot write anything in the opened workbook because the button that triggers to open that workbook is in a userform. So before closing the userform, I should be able to open the workbook and make changes to it then comeback to the userform and keep filling it out. At the same time, I need to transfer the value in textbox1 to the workbook that will be opened by clicking the button in the userform. Is it possible to use your code and still be able to work on the workbook while having the userform activated in another workbook (the main workbook)?

Private Sub CommandButton1_Click()

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
Application.ScreenUpdating = False
xlApp.Visible = True
xlApp.Workbooks.Open Filename:="Z:\QZX\Project\File1.xlsm"

Dim Workbook As Workbook
Dim sheet As Worksheet
Set Workbook = Workbooks("File1")
Set sheet = Worksheets("File1")
sheet.Cells(1, 1) = Me.TextBox1.Value

Application.ScreenUpdating = True

End Sub

 

[Fluff]

Easiest option is to change the userform to Modeless.