Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a textbox on a userform
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a textbox on a userform
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 (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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a textbox on a userform
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? If I do assign a value to ComboBox11 "Kevin Beckham" wrote in message ... 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 (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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating a textbox on a userform
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 -----Original Message----- 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? If I do assign a value to ComboBox11 "Kevin Beckham" wrote in message ... 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 (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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform textbox | Excel Worksheet Functions | |||
How to add textbox/buttons to an UserForm | Excel Programming | |||
hide a textbox on a userform? | Excel Programming | |||
UserForm TextBox to ActiveSheet TextBox over 256 characters | Excel Programming | |||
Help with Syntax UserForm TextBox | Excel Programming |