Two (of many) text boxes on a user form have control sources on the
spreadsheet hidden behind. These controls are =TODAY() and =USERNAME(). These
text boxes are locked.

The problem is that when the command button on the form is clicked to move
input data on the form to the spreadsheet behind, the formulae stated above
are removed leaving the date in one text box while the other is empty. How
can I adapt the vba to copy the data in TextBox1 and TextBox2 rather than
removing it?

Thanks for any ideas on this.

Jock

You can have a value or a formula in a cell. The textbox will put a value in
the cell and remove the formula.

Break the controlsource and manage the updating of the cell with code. You
can program the content of the cell as you wish.

--
regards,
Tom Ogilvy


"Jock" wrote:

Two (of many) text boxes on a user form have control sources on the
spreadsheet hidden behind. These controls are =TODAY() and =USERNAME(). These
text boxes are locked.

The problem is that when the command button on the form is clicked to move
input data on the form to the spreadsheet behind, the formulae stated above
are removed leaving the date in one text box while the other is empty. How
can I adapt the vba to copy the data in TextBox1 and TextBox2 rather than
removing it?

Thanks for any ideas on this.

Jock

Ahhh. Right. Not sure how to approach that. Tbh, I have done it this way as I
can't get the date and usernames to appear in those text boxes when the form
is first opened; once one form full of data has been moved, code fills in
these two textboxes ok, it's just the very first one.

Thanks though Tom, I appreciate the reply.

Jock


"Tom Ogilvy" wrote:

You can have a value or a formula in a cell. The textbox will put a value in
the cell and remove the formula.

Break the controlsource and manage the updating of the cell with code. You
can program the content of the cell as you wish.

--
regards,
Tom Ogilvy


"Jock" wrote:

Two (of many) text boxes on a user form have control sources on the
spreadsheet hidden behind. These controls are =TODAY() and =USERNAME(). These
text boxes are locked.

The problem is that when the command button on the form is clicked to move
input data on the form to the spreadsheet behind, the formulae stated above
are removed leaving the date in one text box while the other is empty. How
can I adapt the vba to copy the data in TextBox1 and TextBox2 rather than
removing it?

Thanks for any ideas on this.

Jock

Dim bBlockEvents as Boolean
Private Sub Userform_Initialize
bBlockEvents = True
me.Textbox1.Value = Application.UserName
me.Textbox2.Value = Worksheets("sheet1").Range("B9").Text
bBlockEvents = False
End Sub

Private Sub Textbox1_Change()
if bBlockEvents then exit sub

' code to update the cells
End Sub

and so forth.

--
regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy



"Jock" wrote:

Ahhh. Right. Not sure how to approach that. Tbh, I have done it this way as I
can't get the date and usernames to appear in those text boxes when the form
is first opened; once one form full of data has been moved, code fills in
these two textboxes ok, it's just the very first one.

Thanks though Tom, I appreciate the reply.

Jock


"Tom Ogilvy" wrote:

You can have a value or a formula in a cell. The textbox will put a value in
the cell and remove the formula.

Break the controlsource and manage the updating of the cell with code. You
can program the content of the cell as you wish.

--
regards,
Tom Ogilvy


"Jock" wrote:

Two (of many) text boxes on a user form have control sources on the
spreadsheet hidden behind. These controls are =TODAY() and =USERNAME(). These
text boxes are locked.

The problem is that when the command button on the form is clicked to move
input data on the form to the spreadsheet behind, the formulae stated above
are removed leaving the date in one text box while the other is empty. How
can I adapt the vba to copy the data in TextBox1 and TextBox2 rather than
removing it?

Thanks for any ideas on this.

Jock

Thanks Tom.
Just to clarify, is this to populate the two empty fields upon opening:
adding it (somehow) to this code?
Private Sub Workbook_Open()
UserForm1.Show
End Sub

or copying text from the text boxes and not the formlae behind them when
command button is clicked, invoking this:

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet1.Range("a200").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
LastRow.Offset(1, 5).Value = TextBox6.Text
LastRow.Offset(1, 6).Value = TextBox7.Text
LastRow.Offset(1, 7).Value = TextBox8.Text

If vbYes Then
TextBox1.Text = Now()
TextBox2.Text = UserName()
TextBox3.Text = ""
TextBox4.Text = "supplier"
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""

Else
Unload Me
End If

End Sub


Thanks again Tom

regards,

Jock


"Tom Ogilvy" wrote:

Dim bBlockEvents as Boolean
Private Sub Userform_Initialize
bBlockEvents = True
me.Textbox1.Value = Application.UserName
me.Textbox2.Value = Worksheets("sheet1").Range("B9").Text
bBlockEvents = False
End Sub

Private Sub Textbox1_Change()
if bBlockEvents then exit sub

' code to update the cells
End Sub

and so forth.

--
regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy



"Jock" wrote:

Ahhh. Right. Not sure how to approach that. Tbh, I have done it this way as I
can't get the date and usernames to appear in those text boxes when the form
is first opened; once one form full of data has been moved, code fills in
these two textboxes ok, it's just the very first one.

Thanks though Tom, I appreciate the reply.

Jock


"Tom Ogilvy" wrote:

You can have a value or a formula in a cell. The textbox will put a value in
the cell and remove the formula.

Break the controlsource and manage the updating of the cell with code. You
can program the content of the cell as you wish.

--
regards,
Tom Ogilvy


"Jock" wrote:

Two (of many) text boxes on a user form have control sources on the
spreadsheet hidden behind. These controls are =TODAY() and =USERNAME(). These
text boxes are locked.

The problem is that when the command button on the form is clicked to move
input data on the form to the spreadsheet behind, the formulae stated above
are removed leaving the date in one text box while the other is empty. How
can I adapt the vba to copy the data in TextBox1 and TextBox2 rather than
removing it?

Thanks for any ideas on this.

Jock

That really doesn't clarify anything for me.

this to populate the two empty fields upon opening

solution provided. If you want to populate cells, then adjust the code to
populate the cells or do it in the workbook open event.


or copying text from the text boxes and not the formlae behind them when
is meaningless to me. If you have a controlsource set, the cells are
updated with the entries from the textboxes. If not set, then your code
would update the cells.

Any formulas in the cells would be overwritten in either context.

--
regards,
Tom Ogilvy






"Jock" wrote:

Thanks Tom.
Just to clarify, is this to populate the two empty fields upon opening:
adding it (somehow) to this code?
Private Sub Workbook_Open()
UserForm1.Show
End Sub

or copying text from the text boxes and not the formlae behind them when
command button is clicked, invoking this:

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet1.Range("a200").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
LastRow.Offset(1, 5).Value = TextBox6.Text
LastRow.Offset(1, 6).Value = TextBox7.Text
LastRow.Offset(1, 7).Value = TextBox8.Text

If vbYes Then
TextBox1.Text = Now()
TextBox2.Text = UserName()
TextBox3.Text = ""
TextBox4.Text = "supplier"
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""

Else
Unload Me
End If

End Sub


Thanks again Tom

regards,

Jock


"Tom Ogilvy" wrote:

Dim bBlockEvents as Boolean
Private Sub Userform_Initialize
bBlockEvents = True
me.Textbox1.Value = Application.UserName
me.Textbox2.Value = Worksheets("sheet1").Range("B9").Text
bBlockEvents = False
End Sub

Private Sub Textbox1_Change()
if bBlockEvents then exit sub

' code to update the cells
End Sub

and so forth.

--
regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy



"Jock" wrote:

Ahhh. Right. Not sure how to approach that. Tbh, I have done it this way as I
can't get the date and usernames to appear in those text boxes when the form
is first opened; once one form full of data has been moved, code fills in
these two textboxes ok, it's just the very first one.

Thanks though Tom, I appreciate the reply.

Jock


"Tom Ogilvy" wrote:

You can have a value or a formula in a cell. The textbox will put a value in
the cell and remove the formula.

Break the controlsource and manage the updating of the cell with code. You
can program the content of the cell as you wish.

--
regards,
Tom Ogilvy


"Jock" wrote:

Two (of many) text boxes on a user form have control sources on the
spreadsheet hidden behind. These controls are =TODAY() and =USERNAME(). These
text boxes are locked.

The problem is that when the command button on the form is clicked to move
input data on the form to the spreadsheet behind, the formulae stated above
are removed leaving the date in one text box while the other is empty. How
can I adapt the vba to copy the data in TextBox1 and TextBox2 rather than
removing it?

Thanks for any ideas on this.

Jock