I have a UserForm with a series of TextBoxes each with a SpinButton
attached. The user can either enter a value into the TextBox or use
the SpinButton to change the value. This value is then returned to a
cell for calculations to be made. If the user selects the value in the
TextBox and presses delete, the value changes to a zero. If the zero
is deleted, only then is the TextBox cleared. I would like the TextBox
to be cleared the first time delete is pressed as the user might not
notice the value has changed to zero. I have also tried to clear the
value using code to set the ControlSource cell value to "" but this
leaves zeroes as well.

Any help would be very much appreciated.

set rng = Range(Textbox1.ControlSource)
rng.clearContents
Textbox1.Value = ""

--
Regards,
Tom Ogilvy



"markb" wrote:

I have a UserForm with a series of TextBoxes each with a SpinButton
attached. The user can either enter a value into the TextBox or use
the SpinButton to change the value. This value is then returned to a
cell for calculations to be made. If the user selects the value in the
TextBox and presses delete, the value changes to a zero. If the zero
is deleted, only then is the TextBox cleared. I would like the TextBox
to be cleared the first time delete is pressed as the user might not
notice the value has changed to zero. I have also tried to clear the
value using code to set the ControlSource cell value to "" but this
leaves zeroes as well.

Any help would be very much appreciated.

Tom
Many thanks for your reply. Please forgive my ignorance, but I'm not
sure where to include this code.

On 27 Feb, 14:35, Tom Ogilvy
wrote:
set rng = Range(Textbox1.ControlSource)
rng.clearContents
Textbox1.Value = ""

--
Regards,
Tom Ogilvy



"markb" wrote:
I have a UserForm with a series of TextBoxes each with a SpinButton
attached. The user can either enter a value into the TextBox or use
the SpinButton to change the value. This value is then returned to a
cell for calculations to be made. If the user selects the value in the
TextBox and presses delete, the value changes to a zero. If the zero
is deleted, only then is the TextBox cleared. I would like the TextBox
to be cleared the first time delete is pressed as the user might not
notice the value has changed to zero. I have also tried to clear the
value using code to set the ControlSource cell value to "" but this
leaves zeroes as well.

Any help would be very much appreciated.- Hide quoted text -

- Show quoted text -

I can't say for sure. I don't have the big picture on what you are doing or
what happens when or what values are acceptable in the textbox. My first
guess would be the change event of the textbox. Have an if statement check
if the textbox is blank or has a 0 in it. If so, run the code. Or use the
exit event.

--
Regards,
Tom Ogilvy


"markb" wrote:

Tom
Many thanks for your reply. Please forgive my ignorance, but I'm not
sure where to include this code.

On 27 Feb, 14:35, Tom Ogilvy
wrote:
set rng = Range(Textbox1.ControlSource)
rng.clearContents
Textbox1.Value = ""

--
Regards,
Tom Ogilvy



"markb" wrote:
I have a UserForm with a series of TextBoxes each with a SpinButton
attached. The user can either enter a value into the TextBox or use
the SpinButton to change the value. This value is then returned to a
cell for calculations to be made. If the user selects the value in the
TextBox and presses delete, the value changes to a zero. If the zero
is deleted, only then is the TextBox cleared. I would like the TextBox
to be cleared the first time delete is pressed as the user might not
notice the value has changed to zero. I have also tried to clear the
value using code to set the ControlSource cell value to "" but this
leaves zeroes as well.

Any help would be very much appreciated.- Hide quoted text -

- Show quoted text -

Thanks again for your help, Tom. I'll try your suggestions.

On Feb 27, 7:21 pm, Tom Ogilvy
wrote:
I can't say for sure. I don't have the big picture on what you are doing or
what happens when or what values are acceptable in the textbox. My first
guess would be the change event of the textbox. Have an if statement check
if the textbox is blank or has a 0 in it. If so, run the code. Or use the
exit event.

--
Regards,
Tom Ogilvy



"markb" wrote:
Tom
Many thanks for your reply. Please forgive my ignorance, but I'm not
sure where to include this code.

On 27 Feb, 14:35, Tom Ogilvy
wrote:
set rng = Range(Textbox1.ControlSource)
rng.clearContents
Textbox1.Value = ""

--
Regards,
Tom Ogilvy

"markb" wrote:
I have a UserForm with a series of TextBoxes each with a SpinButton
attached. The user can either enter a value into the TextBox or use
the SpinButton to change the value. This value is then returned to a
cell for calculations to be made. If the user selects the value in the
TextBox and presses delete, the value changes to a zero. If the zero
is deleted, only then is the TextBox cleared. I would like the TextBox
to be cleared the first time delete is pressed as the user might not
notice the value has changed to zero. I have also tried to clear the
value using code to set the ControlSource cell value to "" but this
leaves zeroes as well.

Any help would be very much appreciated.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I have tried to use the code you suggested in the following ways:
Firstly I have a procedu

Sub clearTextBox1()
Set rng = Range(TextBox1.ControlSource)
rng.ClearContents
TextBox1.Value = ""
End Sub

I have then used:

Application.OnKey "{DELETE}","clearTextBox1"

In both a Change event or KeyPress event for TextBox1

I have also tried an Exit event:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value = "" Then clearTextBox1
End Sub

In every case if the user deletes a value in TextBox1, the value
appears to clear until Enter or Tab is pressed at which point a zero
re-appears in TextBox1. If the zero is deleted, then the TextBox
remains clear.

These TextBoxes are used for percentages so the permitted values are
0-100. But I also need a blank to be permitted. The Excel calculations
based on the output of this TextBox will treat a zero value and a
blank differently. Specifically, a zero will indicate a 0% occupancy
for a hotel room, but a blank will force the calculation to use an
average occupancy rate from a different cell.


On Feb 27, 7:21 pm, Tom Ogilvy
wrote:
I can't say for sure. I don't have the big picture on what you are doing or
what happens when or what values are acceptable in the textbox. My first
guess would be the change event of the textbox. Have an if statement check
if the textbox is blank or has a 0 in it. If so, run the code. Or use the
exit event.

--
Regards,
Tom Ogilvy



"markb" wrote:
Tom
Many thanks for your reply. Please forgive my ignorance, but I'm not
sure where to include this code.

On 27 Feb, 14:35, Tom Ogilvy
wrote:
set rng = Range(Textbox1.ControlSource)
rng.clearContents
Textbox1.Value = ""

--
Regards,
Tom Ogilvy

"markb" wrote:
I have a UserForm with a series of TextBoxes each with a SpinButton
attached. The user can either enter a value into the TextBox or use
the SpinButton to change the value. This value is then returned to a
cell for calculations to be made. If the user selects the value in the
TextBox and presses delete, the value changes to a zero. If the zero
is deleted, only then is the TextBox cleared. I would like the TextBox
to be cleared the first time delete is pressed as the user might not
notice the value has changed to zero. I have also tried to clear the
value using code to set the ControlSource cell value to "" but this
leaves zeroes as well.

Any help would be very much appreciated.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I would break the link between the textbox and the cell (remove the
controlsource if this is a userform or the linkedcell property if on a
worksheet). Then use the change event to update the cell.

without the link, there is nothing that should change the value of the
textbox. This assumes you don't manually make changes in the cell and want
them reflected in the textbox and don't have any code that does that.

--
Regards,
Tom Ogilvy


"markb" wrote in message
oups.com...
I have tried to use the code you suggested in the following ways:
Firstly I have a procedu

Sub clearTextBox1()
Set rng = Range(TextBox1.ControlSource)
rng.ClearContents
TextBox1.Value = ""
End Sub

I have then used:

Application.OnKey "{DELETE}","clearTextBox1"

In both a Change event or KeyPress event for TextBox1

I have also tried an Exit event:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value = "" Then clearTextBox1
End Sub

In every case if the user deletes a value in TextBox1, the value
appears to clear until Enter or Tab is pressed at which point a zero
re-appears in TextBox1. If the zero is deleted, then the TextBox
remains clear.

These TextBoxes are used for percentages so the permitted values are
0-100. But I also need a blank to be permitted. The Excel calculations
based on the output of this TextBox will treat a zero value and a
blank differently. Specifically, a zero will indicate a 0% occupancy
for a hotel room, but a blank will force the calculation to use an
average occupancy rate from a different cell.


On Feb 27, 7:21 pm, Tom Ogilvy
wrote:
I can't say for sure. I don't have the big picture on what you are doing
or
what happens when or what values are acceptable in the textbox. My
first
guess would be the change event of the textbox. Have an if statement
check
if the textbox is blank or has a 0 in it. If so, run the code. Or use
the
exit event.

--
Regards,
Tom Ogilvy



"markb" wrote:
Tom
Many thanks for your reply. Please forgive my ignorance, but I'm not
sure where to include this code.

On 27 Feb, 14:35, Tom Ogilvy
wrote:
set rng = Range(Textbox1.ControlSource)
rng.clearContents
Textbox1.Value = ""

--
Regards,
Tom Ogilvy

"markb" wrote:
I have a UserForm with a series of TextBoxes each with a SpinButton
attached. The user can either enter a value into the TextBox or use
the SpinButton to change the value. This value is then returned to
a
cell for calculations to be made. If the user selects the value in
the
TextBox and presses delete, the value changes to a zero. If the
zero
is deleted, only then is the TextBox cleared. I would like the
TextBox
to be cleared the first time delete is pressed as the user might
not
notice the value has changed to zero. I have also tried to clear
the
value using code to set the ControlSource cell value to "" but this
leaves zeroes as well.

Any help would be very much appreciated.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -