Copy Activex textbox contents to cell in another sheet

[Mikeymike_W]

Hi all,

I'm sure this is easy enough but I just don't know how.

I have an Activex textbox on sheet1 (named CRF), I want to copy the contents to a cell on sheet 4 (named D.Scheme).
The textbox on sheet "CRF" is named "txtComments" and the cell on sheet "D.Scheme" is named "CommentsCell".

I had originally done this by linking the textbox to the cell but it made everything incredibly slow.

I was thinking of either a macro linked to a button to copy and paste or a worksheet change sub to do it automatically.

Thanks in advance for all your help,

Mike

 

[ScottR]

I was thinking of either a macro linked to a button to copy and paste or a worksheet change sub to do it automatically.

You can use the textbox's change event. Sheet code on the page including the textbox would be

Private Sub txtComments_Change()
Sheets("Sheet1").Range("CommentsCell").Value = Me.txtComments.Value
End Sub

Replacing "Sheet1" with the sheet name containing your named range

 

[Mikeymike_W]

Hi Scott,

THank you for your reply.
The code you provided does the job however it is causing my spreadsheet to work extremely slow.

Could you possibly tell me how to convert this code to use as a macro?

Many thanks,

Mike

 

[ScottR]

You could add a command button (on the same sheet as your text box) saying "submit" and use that one line of code in it's click event but I'd be more concerned about what is causing the slow down as it is unlikely to save you much, if any, time.

Do you have a lot of sheet calculation dependent on that entry or is there another cause?

 

[Mikeymike_W]

Hi Scott,

Its does have quite a few calculations, I've tidied it up as possible and it works fine but it seems to hang up when I include the textbox in the change event or if I link it to a cell.

I tried your suggestion of a command button however I need to have the button on sheet 4 rather than sheet 1, with the button in sheet 4 how would I word the code?

Many thanks,

Mike

 

[ScottR]

It would just be

Sheets('Sheet name containing comment named range'<sheet name="" containing="" comment="">).Range("CommentsCell").Value = Sheets('sheet name containing text box'<sheet name="" containing="" textbox="">).txtComments.Value

</sheet></sheet>

 

[Mikeymike_W]

Brilliant, thanks Scott