Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Textbox questions
1. Textbox Number Format
Based on a combobox selection a textbox is filled as shown below: Private Sub cmbAWS_Change() Set rng = Range(cmbAWS.RowSource) txtSFA.Text = rng(cmbAWS.ListIndex + 1)(1, 2) End sub The value that is supposed to be read in to the textbox from the worksheet is sometimes a single decimal (5.1) or a two decimal (5.10)number depending on the combobox selection, but the value displayed is always a single decimal(5.1). How can I get the textbox to display what is shown on the worksheet (5.1 or 5.10)? 2. Deleting Value from Textbox The user is to enter a value and based on the value entered in the textbox txtDefectLength, it will use a dimension which had been previously entered into the worksheet(rng3) to calculate percent of of defect. Private Sub txtDefectLength_Change() Dim rng1 As Range Set rng1 = Worksheets("WPQ").Range("CD24") txtPrecentDefect.Value = (((txtDefectLength.Value) / (rng3 / 4)) * 100) If txtPrecentDefect.Text <= 10 Then txtResults.Value = "Passed" Else: txtResults.Value = "Failes" End If End Sub When entering a value into the textbox(txtDefectLength) it works fine, but if the text is deleted to enter a different value I become the error message "Type mismatch (Error 13)" What can I do to stop this from happening? Any help would be appreciated. Kind Regards Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Textbox questions
1. txtSFA.Text = rng(cmbAWS.ListIndex + 1)(1, 2).Text
2. Add a command button, and calculate upon clicking the button rather than textbox change. The problem with textbox change is that it fires for every change to the textbox, not just when done. You could also experiment with the textbox Exit, or AfterUpdate events. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Martin Koenig" wrote in message om... 1. Textbox Number Format Based on a combobox selection a textbox is filled as shown below: Private Sub cmbAWS_Change() Set rng = Range(cmbAWS.RowSource) txtSFA.Text = rng(cmbAWS.ListIndex + 1)(1, 2) End sub The value that is supposed to be read in to the textbox from the worksheet is sometimes a single decimal (5.1) or a two decimal (5.10)number depending on the combobox selection, but the value displayed is always a single decimal(5.1). How can I get the textbox to display what is shown on the worksheet (5.1 or 5.10)? 2. Deleting Value from Textbox The user is to enter a value and based on the value entered in the textbox txtDefectLength, it will use a dimension which had been previously entered into the worksheet(rng3) to calculate percent of of defect. Private Sub txtDefectLength_Change() Dim rng1 As Range Set rng1 = Worksheets("WPQ").Range("CD24") txtPrecentDefect.Value = (((txtDefectLength.Value) / (rng3 / 4)) * 100) If txtPrecentDefect.Text <= 10 Then txtResults.Value = "Passed" Else: txtResults.Value = "Failes" End If End Sub When entering a value into the textbox(txtDefectLength) it works fine, but if the text is deleted to enter a different value I become the error message "Type mismatch (Error 13)" What can I do to stop this from happening? Any help would be appreciated. Kind Regards Martin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Textbox questions
Bob,
Just wanted to say thanks for your help. Kind Regards Martin "Bob Phillips" wrote in message ... 1. txtSFA.Text = rng(cmbAWS.ListIndex + 1)(1, 2).Text 2. Add a command button, and calculate upon clicking the button rather than textbox change. The problem with textbox change is that it fires for every change to the textbox, not just when done. You could also experiment with the textbox Exit, or AfterUpdate events. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Martin Koenig" wrote in message om... 1. Textbox Number Format Based on a combobox selection a textbox is filled as shown below: Private Sub cmbAWS_Change() Set rng = Range(cmbAWS.RowSource) txtSFA.Text = rng(cmbAWS.ListIndex + 1)(1, 2) End sub The value that is supposed to be read in to the textbox from the worksheet is sometimes a single decimal (5.1) or a two decimal (5.10)number depending on the combobox selection, but the value displayed is always a single decimal(5.1). How can I get the textbox to display what is shown on the worksheet (5.1 or 5.10)? 2. Deleting Value from Textbox The user is to enter a value and based on the value entered in the textbox txtDefectLength, it will use a dimension which had been previously entered into the worksheet(rng3) to calculate percent of of defect. Private Sub txtDefectLength_Change() Dim rng1 As Range Set rng1 = Worksheets("WPQ").Range("CD24") txtPrecentDefect.Value = (((txtDefectLength.Value) / (rng3 / 4)) * 100) If txtPrecentDefect.Text <= 10 Then txtResults.Value = "Passed" Else: txtResults.Value = "Failes" End If End Sub When entering a value into the textbox(txtDefectLength) it works fine, but if the text is deleted to enter a different value I become the error message "Type mismatch (Error 13)" What can I do to stop this from happening? Any help would be appreciated. Kind Regards Martin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Textbox questions
It's a pleasure Martin.
What did you go with on point 2 in the end? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Martin Koenig" wrote in message om... Bob, Just wanted to say thanks for your help. Kind Regards Martin "Bob Phillips" wrote in message ... 1. txtSFA.Text = rng(cmbAWS.ListIndex + 1)(1, 2).Text 2. Add a command button, and calculate upon clicking the button rather than textbox change. The problem with textbox change is that it fires for every change to the textbox, not just when done. You could also experiment with the textbox Exit, or AfterUpdate events. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Martin Koenig" wrote in message om... 1. Textbox Number Format Based on a combobox selection a textbox is filled as shown below: Private Sub cmbAWS_Change() Set rng = Range(cmbAWS.RowSource) txtSFA.Text = rng(cmbAWS.ListIndex + 1)(1, 2) End sub The value that is supposed to be read in to the textbox from the worksheet is sometimes a single decimal (5.1) or a two decimal (5.10)number depending on the combobox selection, but the value displayed is always a single decimal(5.1). How can I get the textbox to display what is shown on the worksheet (5.1 or 5.10)? 2. Deleting Value from Textbox The user is to enter a value and based on the value entered in the textbox txtDefectLength, it will use a dimension which had been previously entered into the worksheet(rng3) to calculate percent of of defect. Private Sub txtDefectLength_Change() Dim rng1 As Range Set rng1 = Worksheets("WPQ").Range("CD24") txtPrecentDefect.Value = (((txtDefectLength.Value) / (rng3 / 4)) * 100) If txtPrecentDefect.Text <= 10 Then txtResults.Value = "Passed" Else: txtResults.Value = "Failes" End If End Sub When entering a value into the textbox(txtDefectLength) it works fine, but if the text is deleted to enter a different value I become the error message "Type mismatch (Error 13)" What can I do to stop this from happening? Any help would be appreciated. Kind Regards Martin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Textbox questions
Hi Bob,
Probably not the most elegant solution, I just moved the Code to Afterupdate, but it works. Martin "Bob Phillips" wrote in message ... It's a pleasure Martin. What did you go with on point 2 in the end? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Martin Koenig" wrote in message om... Bob, Just wanted to say thanks for your help. Kind Regards Martin "Bob Phillips" wrote in message ... 1. txtSFA.Text = rng(cmbAWS.ListIndex + 1)(1, 2).Text 2. Add a command button, and calculate upon clicking the button rather than textbox change. The problem with textbox change is that it fires for every change to the textbox, not just when done. You could also experiment with the textbox Exit, or AfterUpdate events. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Martin Koenig" wrote in message om... 1. Textbox Number Format Based on a combobox selection a textbox is filled as shown below: Private Sub cmbAWS_Change() Set rng = Range(cmbAWS.RowSource) txtSFA.Text = rng(cmbAWS.ListIndex + 1)(1, 2) End sub The value that is supposed to be read in to the textbox from the worksheet is sometimes a single decimal (5.1) or a two decimal (5.10)number depending on the combobox selection, but the value displayed is always a single decimal(5.1). How can I get the textbox to display what is shown on the worksheet (5.1 or 5.10)? 2. Deleting Value from Textbox The user is to enter a value and based on the value entered in the textbox txtDefectLength, it will use a dimension which had been previously entered into the worksheet(rng3) to calculate percent of of defect. Private Sub txtDefectLength_Change() Dim rng1 As Range Set rng1 = Worksheets("WPQ").Range("CD24") txtPrecentDefect.Value = (((txtDefectLength.Value) / (rng3 / 4)) * 100) If txtPrecentDefect.Text <= 10 Then txtResults.Value = "Passed" Else: txtResults.Value = "Failes" End If End Sub When entering a value into the textbox(txtDefectLength) it works fine, but if the text is deleted to enter a different value I become the error message "Type mismatch (Error 13)" What can I do to stop this from happening? Any help would be appreciated. Kind Regards Martin |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Textbox questions
Thanks for letting me know.
AfterUpdate is good, it's an oft overlooked event. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Martin Koenig" wrote in message om... Hi Bob, Probably not the most elegant solution, I just moved the Code to Afterupdate, but it works. Martin "Bob Phillips" wrote in message ... It's a pleasure Martin. What did you go with on point 2 in the end? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Martin Koenig" wrote in message om... Bob, Just wanted to say thanks for your help. Kind Regards Martin "Bob Phillips" wrote in message ... 1. txtSFA.Text = rng(cmbAWS.ListIndex + 1)(1, 2).Text 2. Add a command button, and calculate upon clicking the button rather than textbox change. The problem with textbox change is that it fires for every change to the textbox, not just when done. You could also experiment with the textbox Exit, or AfterUpdate events. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Martin Koenig" wrote in message om... 1. Textbox Number Format Based on a combobox selection a textbox is filled as shown below: Private Sub cmbAWS_Change() Set rng = Range(cmbAWS.RowSource) txtSFA.Text = rng(cmbAWS.ListIndex + 1)(1, 2) End sub The value that is supposed to be read in to the textbox from the worksheet is sometimes a single decimal (5.1) or a two decimal (5.10)number depending on the combobox selection, but the value displayed is always a single decimal(5.1). How can I get the textbox to display what is shown on the worksheet (5.1 or 5.10)? 2. Deleting Value from Textbox The user is to enter a value and based on the value entered in the textbox txtDefectLength, it will use a dimension which had been previously entered into the worksheet(rng3) to calculate percent of of defect. Private Sub txtDefectLength_Change() Dim rng1 As Range Set rng1 = Worksheets("WPQ").Range("CD24") txtPrecentDefect.Value = (((txtDefectLength.Value) / (rng3 / 4)) * 100) If txtPrecentDefect.Text <= 10 Then txtResults.Value = "Passed" Else: txtResults.Value = "Failes" End If End Sub When entering a value into the textbox(txtDefectLength) it works fine, but if the text is deleted to enter a different value I become the error message "Type mismatch (Error 13)" What can I do to stop this from happening? Any help would be appreciated. Kind Regards Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
Calculate Textbox value based on another textbox value.doc | Excel Discussion (Misc queries) | |||
Calculate Textbox value based on another textbox value | Excel Discussion (Misc queries) | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
UserForm TextBox to ActiveSheet TextBox over 256 characters | Excel Programming |