Automatically Update TextBox on Other Sheet

[lm8]

Hi there,

So I feel like this has to be a simple solution that I'm just missing. I've sifted through a lot of forums and Google results and still haven't found it.

I'm using Microsoft Excel 2010. I have 2 sheets, Sheet 1 "Editable" (the editable copy of the form) & Sheet 2 "Printable" (the print friendly copy of the form). When text is typed into a cell on "Editable", the text should automatically populate to a textbox on "Printable".
I wasn't sure where to put the code, so I put it under the Worksheet_Change sub on "Printable". It will automatically update, but only with changes to "Printable". I would like it so it automatically updates whenever text is entered into "Editable".

Here is the VBA code for the "Printable" sheet:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myArray As Variant
Dim myStr As String
Dim x As Long


Dim myArray2 As Variant
Dim myStr2 As String
Dim y As Long


txtTime.Text = Format("MM/dd/yyyy hh:mm:ss AM/PM")


'for comments


myArray = Worksheets("Editable").Range("J15:J62").Value


For x = LBound(myArray, 1) To UBound(myArray, 1)


myStr = myStr & myArray(x, 1) & vbCrLf


Next x


'TextBox267 = myStr


txtComments = myStr


'for time stamps


myArray2 = Worksheets("Editable").Range("K15: K62").Value


For y = LBound(myArray2, 1) To UBound(myArray2, 1)


myStr2 = myStr2 & myArray2(y, 1) & vbCrLf


Next y


txtTime = myStr2


End Sub


Is there a better subroutine to place this in or something else that I'm missing?

Thanks in advance for any assistance! :)

[p45cal]

Could you attach a file? With realistic data in it.

[lm8]

The entire .xlsm file? I don't think I can or should attach that as there is some private and identifying information in there. Or are you referring to something else? Thanks for the response though!

Edit: I can remove the information, but I'm not confident in my ability to *fully* remove it and don't want to risk that. I hope you understand :)

[p45cal]

I understand about sensitive data.
Hmmm. This could be difficult.
I'd have expected the code to be in the Editable worksheet's code-module, but it will need one or two changes for it to work there.

I wonder whether you would allow just me to see it - again I'd understand if you don't feel comfortable about that - but if you are comfortable, Private Message me here for my private email address to send it to me on.
Otherwise, if you're happy only for me to see it transiently, we could set up a TeamViewer session (Google it); again Private Message me here if that's the case.

[lm8]

Yeah, I agree. It definitely makes more sense in the Editable code module. All of my references and attempts to fit it in that code module weren't working so I left it in Printable where it at least works to some extent.

Would you be willing to direct me to some of the changes required to work in the Editable worksheet code-module? I know that references to textboxes, etc on that worksheet need to be changed, but I'm not sure what else or in what way.

[p45cal]

One of the reasons I'd like to see your workbook is that lines such as:
txtTime.Text = Format("MM/dd/yyyy hh:mm:ss AM/PM")
probably get overwritten later with the likes of:
txtTime = myStr2
but I'm not certain.

Anyway, references like that probably need to be prefixed if the code is elsewhere:
Worksheets("Printable").txtTime.Text = Format("MM/dd/yyyy hh:mm:ss AM/PM")
Worksheets("Printable").txtTime = myStr2
Worksheets("Printable").txtComments = myStr

Regarding lines such as:
myArray = Worksheets("Editable").Range("J15:J62").Value
since this code will reside in the Editable sheet's code-module, you no longer need (but it doesn't matter if they're there) references to that sheet, so such lines can be reduced to:
myArray = Range("J15:J62").Value

[lm8]

It worked! Thank you so much! It must have just been a matter of me not referencing correctly. Thank you again for your time and knowledge. :D


Edit : I don't think anyone is probably going to need the same weird help I requested, but just in case.
As p45cal suggested, I moved all of the code to the Worksheet Change routine in theEditable code-module and just changed the references based on the recommendations.