Spinbutton Mysteriously Erases Textbox

[ryanmcwh]

I'm writing an employment evaluation. I have several userforms with
textbox1 and textbox2. textbox1 is a value between 0 and 3 controlled
by a spinbutton (spinbutton1), the only spinbutton on the userform.
textbox2 is a plain text entry box used for comment entry. The issue
I'm having is that if I type my comments in textbox2 first then click
either the up or down buttons on the spinbutton, textbox2 is erased.
What's strange is that if I click the spinbutton first, giving the
employee a rating, and then type in textbox2, and then go back and
click on the spinbutton, the text remains in textbox2. Essentially you
must give a rating first or you will lose any text you enter. I don't
have any code that should erase textbox2 for any reason. Both
textboxes are linked to separate cells on the same sheet. Below is the
code for the spinbutton which is the only code behind the userform.
Thanks for any advice!



Private Sub SpinButton1_SpinDown()
Sheets("calc").Select
For Each cell In ActiveSheet.Range("C57")
If TextBox1.Value > 0 Then cell.Value = cell.Value - 0.1
Next
End Sub

Private Sub SpinButton1_SpinUp()
Sheets("calc").Select
For Each cell In ActiveSheet.Range("C57")
If TextBox1.Value < 3 Then cell.Value = cell.Value + 0.1
Next
End Sub

 

[Martin Fishlock]

Hi Ryan:

I would sugget that you try and step through your code and find where the
event occurs.

Your code appears to be ok as given but it will be the other code that is
causing problems.

BTW, you don't need for each cell or the select as you are only checking one
cell and I recommend using constants for sheet names and cell addresses to
make changing easier. just use a with:

Try:

Const cszSheetName as string = "calc"
Const cszCellRef as string = "C57"

Private Sub SpinButton1_SpinDown()

With Sheets(cszSheetName).Range(cszCellRef)
If Me.TextBox1.Value > 0 Then .Value = .Value - 0.1
end with
end Sub

Private Sub SpinButton1_SpinUp()
With Sheets(cszSheetName).Range(cszCellRef)
If TextBox1.Value < 3 Then .Value = .Value + 0.1
end with
End Sub

 

[ryanmcwh]

Hi Ryan:

I would sugget that you try and step through your code and find where the
event occurs.

Your code appears to be ok as given but it will be the other code that is
causing problems.

BTW, you don't need for each cell or the select as you are only checking one
cell and I recommend using constants for sheet names and cell addresses to
make changing easier. just use a with:

Try:

Const cszSheetName as string = "calc"
Const cszCellRef as string = "C57"

Private Sub SpinButton1_SpinDown()

With Sheets(cszSheetName).Range(cszCellRef)
If Me.TextBox1.Value > 0 Then .Value = .Value - 0.1
end with
end Sub

Private Sub SpinButton1_SpinUp()
With Sheets(cszSheetName).Range(cszCellRef)
If TextBox1.Value < 3 Then .Value = .Value + 0.1
end with
End Sub

Thanks Martin...I thought this would work but unfortunately I'm having
the same issue with your code.

 

[ryanmcwh]

Thanks Martin...I thought this would work but unfortunately I'm having
the same issue with your code.

Martin...I figured it out. My textbox was in a frame and when i
removed the frame the behavior stopped. I don;t know if it's a bug or
what. I'm going to do some more testing but the frame which was just
around textbox2 appears to have somehow been the cause. Thanks again
for the suggestion!