Calculating a textbox on a userform

  • Thread starter Thread starter Patrick Simonds
  • Start date Start date
P

Patrick Simonds

I have a stumbling block in my code below.

If I do not assign a value to ComboBox11 (and the default value I want is 0)
then I get a type mismatch error when I start to enter a number in TextBox2.
If I do assign a value to ComboBox11 (using .ComboBox11.Value = "0") then
everything works with the notable exception that I when I use the dropdown
on the ComboBox to select one of my other choices (0, .5, 1) I can not
change the value. It remains at 0 no mater what I do.

Anyone have any thoughts on this?



Sub TotalHours1()

Dim bTest1 As Boolean
Dim bTest2 As Boolean
Dim bTest3 As Boolean
Dim dblElapsed

With UserForm1

.ComboBox11.Value = "0"

If IsNumeric(.TextBox1.Text) And _
IsNumeric(.TextBox2.Text) Then

dblElapsed = (CDbl(.TextBox2.Text) - CDbl(.TextBox1.Text) -
CDbl(.ComboBox11.Text))

.TextBox3.Value = Format(dblElapsed, "#0.00")

If .ComboBox1.Value = "" Then GoTo EnterCode
GoTo EndMacro

EnterCode:

.ComboBox1.Value = "01"

Else

'MsgBox "There is an invalid time"

End If

GoTo EndMacro

ClearBox:

UserForm1.TextBox1.Value = Format("", "")
UserForm1.TextBox2.Value = Format("", "")
UserForm1.TextBox3.Value = Format("", "")

EndMacro:

End With

End Sub
 
Patrick

I suggest you use the .ComboBox11.ListIndex = 0 property
to set the value to the first item in the list and only
use the ComboBox11.Value property to read its value

Kevin Beckham
-----Original Message-----
I have a stumbling block in my code below.

If I do not assign a value to ComboBox11 (and the default value I want is 0)
then I get a type mismatch error when I start to enter a number in TextBox2.
If I do assign a value to ComboBox11
Click to expand...
(using .ComboBox11.Value = "0") then
 
Thanks for your reply. Your question lead me to my real problem. As part of
the Private Sub UserForm_Initialize() is the line:

ComboBox11.Text = rng(1, 93).Value

This is in place so that if the UserForm is called and data exists in the
referenced cell, it will display in the ComboBox on the UserForm (very
useful if you want to be able to go back and edit existing data). So the
real question becomes how to make the value of ComboBox 11 be say 1 if the
referenced range ( rng(1, 93).Value) is blank?
 
You could try something like

If IsEmpty(rng(1, 93)) Then
ComboBox11.Text = 1
Else
ComboBox11.Text = rng(1, 93).value
End If

I still foresee problems if your value isn't an item in
the list of values for the combo box

Kevin Beckham
 
Back
Top