Deleting value in TextBox with SpinButton attached

[markb]

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 Ogilvy]

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

 

[markb]

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

 

[Tom Ogilvy]

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.

 

[markb]

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

 

[markb]

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

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.

 

[Tom Ogilvy]

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.

 

[markb]

Unfortunately this creates other problems because of the link between
the TextBox and SpinButton. I'm now thinking that it is this link that
is causing the problem.

The SpinButton has a permitted value between 0 and 100. If the user
changes the TextBox, this updates the SpinButton and vice versa. So if
a user deletes the value in the TextBox, maybe the SpinButton can only
read this as a zero and so returns zero back to the TextBox?? As zero
is a permitted value sometimes, I can't use an If statement to replace
a zero with a blank.

Worst case, I could change my calculations so a blank and zero are
treated the same way, and then get the conditionality I wanted some
other way, like a CheckBox. But there are 36000 formulae in my
worksheet, so any other ideas would be very welcome!

Regards
markb

 

[Tom Ogilvy]

I don't know how you have a spinbutton linked to your textbox.