Transfering Text from and Activex Textbox in one sheet to another Activex Textbox in another sheet

[Mcfg007]

Hello

I am looking to transfer text from an Activex Textbox in one sheet to another Activex Textbox in another sheet?

Can anyone help?

Many thanks

 

[Rick Rothstein]

It appears that the code removes the error 1004 code and works fine, however any data brought from another textbox no longer loads on the intended textbox. If I removed the codes and unprotect the sheets, it starts working fine again. So the ask is, how to get the code in without affecting the automatically loading of the boxes.

Just guessing here... perhaps unprotect the sheet(s) in the ThisWorkbook.Open event and protect it again in the ThisWorkbook.BeforeClose event instead of in the control's event. Another possibility is to put the unprotect in the sheet's Activate event and the unprotect in the sheet's Deactivate event... in thinking about it, this latter idea may be the better one. I am not entirely sure of the interactions that are taking place from your code, but the key is to unprotect the sheet (or sheets) before anything is done to it (or them). You may have to experiment until you find the correct location for what you are trying to do.

 

[Mcfg007]

Just guessing here... perhaps unprotect the sheet(s) in the ThisWorkbook.Open event and protect it again in the ThisWorkbook.BeforeClose event instead of in the control's event. Another possibility is to put the unprotect in the sheet's Activate event and the unprotect in the sheet's Deactivate event... in thinking about it, this latter idea may be the better one. I am not entirely sure of the interactions that are taking place from your code, but the key is to unprotect the sheet (or sheets) before anything is done to it (or them). You may have to experiment until you find the correct location for what you are trying to do.

The error comes on Topleftcell line, so how do you suggest?

Private Sub TextBox1_Change()
With TextBox1
.AutoSize = True
.MultiLine = True
.WordWrap = True
.TopLeftCell.RowHeight = TextBox1.Height
.Width = 474.6 'Set as required
End With
End Sub

 

[Mcfg007]

Right I got all the other to work apart from the sheet where I have two Active X boxes retrieving data from another sheet. They do protect fine and return the date, but the sheet where the data is entered, when I select the text box to write and start writing it starts jumping and you can not see what you write, then when finish it stops and the text is there and transferred to the next box. just not sure why is creating this flickering of the box.

 

[Rick Rothstein]

Right I got all the other to work apart from the sheet where I have two Active X boxes retrieving data from another sheet. They do protect fine and return the date, but the sheet where the data is entered, when I select the text box to write and start writing it starts jumping and you can not see what you write, then when finish it stops and the text is there and transferred to the next box. just not sure why is creating this flickering of the box.

I am guessing your code is in the TextBox's Change event, correct? If so, did you set Application.EnableEvents = False at the start of procedure and then set it to True at the end of the procedure?

 

[Mcfg007]

I am guessing your code is in the TextBox's Change event, correct? If so, did you set Application.EnableEvents = False at the start of procedure and then set it to True at the end of the procedure?

Hi Rick

on the sheet (Case Deatails) I have the imputing textboxes I have the following code:

Private Sub CommandButton1_Click()
End Sub
Private Sub TextBox1_Change()
Application.EnableEvents = False
Sheets("CI Report").TextBox1.Value = TextBox1.Value
Application.EnableEvents = True
End Sub
Private Sub TextBox2_Change()
Application.EnableEvents = False
Sheets("CI Report").TextBox2.Value = TextBox2.Value
Application.EnableEvents = True
End Sub
Private Sub TextBox4_Change()
End Sub
Private Sub TextBox3_Change()
Application.EnableEvents = False
Sheets("CI Report").TextBox3.Value = TextBox3.Value
Application.EnableEvents = True
End Sub


On the sheet (CI Report) that I am getting the text into the Texboxes I have the following code,

Option Explicit
Private Sub TextBox1_Change()
Sheets("CI Report").Unprotect "Mcfgomes007"
With TextBox1
.AutoSize = True
.MultiLine = True
.WordWrap = True
.TopLeftCell.RowHeight = TextBox1.Height
.Width = 400 'Set as required
Sheets("CI Report").Protect "Mcfgomes007"
End With
End Sub
Private Sub TextBox2_Change()
Sheets("CI Report").Unprotect "Mcfgomes007"
With TextBox2
.AutoSize = True
.MultiLine = True
.WordWrap = True
.TopLeftCell.RowHeight = TextBox2.Height
.Width = 400 'Set as required
Sheets("CI Report").Protect "Mcfgomes007"
End With
End Sub


So where should be the Application.EnableEvents = False ?