Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox question
Hi
I have 3 textboxes in UserForm where user inputs value and the sum must always add up to 100. If any 2 textboxes have valid input, the third will be filled in automatically. Here is my attempt using nested IF, but I doubt I'm declaring my textboxes or used the "IsEmpty" function correctly here because type mismatch error occurs...Can someone please help me making it work? Note: The following code relates to first textbox (txtSilt) only. Codes for the other 2 textboxes are similar and I'll post them if it helps. Private Sub txtSilt_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) ' Declare 3 textboxes (txtSilt, txtSand, txtClay) Dim NumtxtSilt As Variant, NumtxtSand As Variant, NumtxtClay As Variant NumtxtSilt = Me.txtSilt.Value NumtxtSand = Me.txtSand.Value NumtxtClay = Me.txtClay.Value ' If the input is NOT numerical, show warning label, ' show and copy null value to worksheet, by calling sub CopySiltEmpty If Not IsNumeric(NumtxtSilt) Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- You must enter a NUMBER" CopySiltEmpty ' Or if the sum of Silt and Sand is within 100%, ' hide warning label, show and copy both Silt and calculated Clay value ' to worksheet, by calling sub CopySilt and sub CopyClay ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtSand) And NumtxtSilt + NumtxtSand <= 100 Then NumtxtClay = 100 - NumtxtSilt - NumtxtSand CopySilt CopyClay ' Or if the sum of Silt and Sand exceeds 100%, ' show warning label, show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtSand) And NumtxtSilt + NumtxtSand 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Total soil composite percentage must add up to 100%" CopySiltEmpty ' Or if the sum of Silt and Clay is within 100%, ' show and copy both Silt and calculated Sand values to worksheet ' by calling sub CopySilt and sub CopySand ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtClay) And NumtxtSilt + NumtxtClay <= 100 Then NumtxtSand = 100 - NumtxtSilt - NumtxtClay CopySilt CopySand ' Or if the sum of Silt and Clay exceeds 100%, ' show warning label, show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtClay) And NumtxtSilt + NumtxtClay 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Total soil composite percentage must add up to 100%" CopySiltEmpty ' Or if the input is outside 0 and 100, show warning label, ' show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf NumtxtSilt < 0 Or NumtxtSilt 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Please enter a number between 0 and 100" CopySiltEmpty ' Else hide warning label, show and copy data to worksheet ' by calling sub CopySilt Else CopySilt End If End Sub ' Below is stored in Module 1 Sub CopySiltEmpty() Dim ws As Worksheet Dim rngSilt As Range Dim NumtxtSilt As Variant Set ws = ThisWorkbook.Worksheets("Form Control 1") Set rngSilt = ws.Range("B4") ' Clear input and change textbox BackColor to yellow NumtxtSilt = frmUSLE.txtSilt.Value NumtxtSilt = Empty frmUSLE.txtSilt.Value = Empty frmUSLE.txtSilt.BackColor = &HFFFF& ' Copy empty value to the worksheet rngSilt.Value = Empty End Sub ' Below is stored in Module 1 Sub CopySilt() Dim ws As Worksheet Dim rngSilt As Range Set ws = ThisWorkbook.Worksheets("Form Control 1") Set rngSilt = ws.Range("B4") ' Set textbox format to one decimal point frmUSLE.txtSilt.Text = Format(frmUSLE.txtSilt.Text, "#,##0.0") ' Copy the data to the worksheet rngSilt.Value = frmUSLE.txtSilt.Value ' Hide warning label, change textbox BackColor to white frmUSLE.labSiltWarning.Visible = False frmUSLE.txtSilt.BackColor = &H80000005 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox question
IsEmpty is only useful to see if a Variant variable in not yet initialize
(that is, Dim'med, but with nothing assigned to it yet). I would Dim the NumtxtSilt, NumtxtSand and NumtxtClay variables as Strings and test it for "no text" this way... If Len(NumtxtSilt) 0 Then ' Variable has text in it Else ' Variable has no text in it End If As for your use of IsNumeric, consider the following message I've posted in the past.... IsNumeric is not a very good "number proofer" given what most programmers expect it to do. Perhaps you will find the following which I have posted in the past to the compiled VB newsgroups (but which applies equally to the VBA world as well)... I usually try and steer people away from using IsNumeric to "proof" supposedly numeric text. Consider this (also see note below): ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)") Most people would not expect THAT to return True. IsNumeric has some "flaws" in what it considers a proper number and what most programmers are looking for. I had a short tip published by Pinnacle Publishing in their Visual Basic Developer magazine that covered some of these flaws. Originally, the tip was free to view but is now viewable only by subscribers.. Basically, it said that IsNumeric returned True for things like -- currency symbols being located in front or in back of the number as shown in my example (also applies to plus, minus and blanks too); numbers surrounded by parentheses as shown in my example (some people use these to mark negative numbers); numbers containing any number of commas before a decimal point as shown in my example; numbers in scientific notation (a number followed by an upper or lower case "D" or "E", followed by a number equal to or less than 305 -- the maximum power of 10 in VB); and Octal/Hexadecimal numbers (&H for Hexadecimal, &O or just & in front of the number for Octal). NOTE: ====== In the above example and in the referenced tip, I refer to $ signs and commas and dots -- these were meant to refer to your currency, thousands separator and decimal point symbols as defined in your local settings -- substitute your local regional symbols for these if appropriate. As for your question about checking numbers, here are two functions that I have posted in the past for similar questions..... one is for digits only and the other is for "regular" numbers: Function IsDigitsOnly(Value As String) As Boolean IsDigitsOnly = Len(Value) 0 And _ Not Value Like "*[!0-9]*" End Function Function IsNumber(ByVal Value As String) As Boolean ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9.]*" And _ Not Value Like "*.*.*" And _ Len(Value) 0 And Value < "." And _ Value < vbNullString End Function Here are revisions to the above functions that deal with the local settings for decimal points (and thousand's separators) that are different than used in the US (this code works in the US too, of course). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String ' Get local setting for decimal point DP = Format$(0, ".") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) 0 And Value < DP And _ Value < vbNullString End Function I'm not as concerned by the rejection of entries that include one or more thousand's separators, but we can handle this if we don't insist on the thousand's separator being located in the correct positions (in other words, we'll allow the user to include them for their own purposes... we'll just tolerate their presence). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String Dim TS As String ' Get local setting for decimal point DP = Format$(0, ".") ' Get local setting for thousand's separator ' and eliminate them. Remove the next two lines ' if you don't want your users being able to ' type in the thousands separator at all. TS = Mid$(Format$(1000, "#,###"), 2, 1) Value = Replace$(Value, TS, "") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) 0 And Value < DP And _ Value < vbNullString End Function Rick "Sam Kuo" wrote in message ... Hi I have 3 textboxes in UserForm where user inputs value and the sum must always add up to 100. If any 2 textboxes have valid input, the third will be filled in automatically. Here is my attempt using nested IF, but I doubt I'm declaring my textboxes or used the "IsEmpty" function correctly here because type mismatch error occurs...Can someone please help me making it work? Note: The following code relates to first textbox (txtSilt) only. Codes for the other 2 textboxes are similar and I'll post them if it helps. Private Sub txtSilt_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) ' Declare 3 textboxes (txtSilt, txtSand, txtClay) Dim NumtxtSilt As Variant, NumtxtSand As Variant, NumtxtClay As Variant NumtxtSilt = Me.txtSilt.Value NumtxtSand = Me.txtSand.Value NumtxtClay = Me.txtClay.Value ' If the input is NOT numerical, show warning label, ' show and copy null value to worksheet, by calling sub CopySiltEmpty If Not IsNumeric(NumtxtSilt) Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- You must enter a NUMBER" CopySiltEmpty ' Or if the sum of Silt and Sand is within 100%, ' hide warning label, show and copy both Silt and calculated Clay value ' to worksheet, by calling sub CopySilt and sub CopyClay ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtSand) And NumtxtSilt + NumtxtSand <= 100 Then NumtxtClay = 100 - NumtxtSilt - NumtxtSand CopySilt CopyClay ' Or if the sum of Silt and Sand exceeds 100%, ' show warning label, show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtSand) And NumtxtSilt + NumtxtSand 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Total soil composite percentage must add up to 100%" CopySiltEmpty ' Or if the sum of Silt and Clay is within 100%, ' show and copy both Silt and calculated Sand values to worksheet ' by calling sub CopySilt and sub CopySand ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtClay) And NumtxtSilt + NumtxtClay <= 100 Then NumtxtSand = 100 - NumtxtSilt - NumtxtClay CopySilt CopySand ' Or if the sum of Silt and Clay exceeds 100%, ' show warning label, show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtClay) And NumtxtSilt + NumtxtClay 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Total soil composite percentage must add up to 100%" CopySiltEmpty ' Or if the input is outside 0 and 100, show warning label, ' show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf NumtxtSilt < 0 Or NumtxtSilt 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Please enter a number between 0 and 100" CopySiltEmpty ' Else hide warning label, show and copy data to worksheet ' by calling sub CopySilt Else CopySilt End If End Sub ' Below is stored in Module 1 Sub CopySiltEmpty() Dim ws As Worksheet Dim rngSilt As Range Dim NumtxtSilt As Variant Set ws = ThisWorkbook.Worksheets("Form Control 1") Set rngSilt = ws.Range("B4") ' Clear input and change textbox BackColor to yellow NumtxtSilt = frmUSLE.txtSilt.Value NumtxtSilt = Empty frmUSLE.txtSilt.Value = Empty frmUSLE.txtSilt.BackColor = &HFFFF& ' Copy empty value to the worksheet rngSilt.Value = Empty End Sub ' Below is stored in Module 1 Sub CopySilt() Dim ws As Worksheet Dim rngSilt As Range Set ws = ThisWorkbook.Worksheets("Form Control 1") Set rngSilt = ws.Range("B4") ' Set textbox format to one decimal point frmUSLE.txtSilt.Text = Format(frmUSLE.txtSilt.Text, "#,##0.0") ' Copy the data to the worksheet rngSilt.Value = frmUSLE.txtSilt.Value ' Hide warning label, change textbox BackColor to white frmUSLE.labSiltWarning.Visible = False frmUSLE.txtSilt.BackColor = &H80000005 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox question
Thanks Rick. I'll give it a shot :-)
Just before I do, do you mind explain how I should incorporate your IsNumber function to my code and where to put it (sorry I'm a newbie in programming as you can tell)? Basically the value can only be positive (including 0) and upto 100 (formated to 1 decimal point). Thanks Sam "Rick Rothstein (MVP - VB)" wrote: IsEmpty is only useful to see if a Variant variable in not yet initialize (that is, Dim'med, but with nothing assigned to it yet). I would Dim the NumtxtSilt, NumtxtSand and NumtxtClay variables as Strings and test it for "no text" this way... If Len(NumtxtSilt) 0 Then ' Variable has text in it Else ' Variable has no text in it End If As for your use of IsNumeric, consider the following message I've posted in the past.... IsNumeric is not a very good "number proofer" given what most programmers expect it to do. Perhaps you will find the following which I have posted in the past to the compiled VB newsgroups (but which applies equally to the VBA world as well)... I usually try and steer people away from using IsNumeric to "proof" supposedly numeric text. Consider this (also see note below): ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)") Most people would not expect THAT to return True. IsNumeric has some "flaws" in what it considers a proper number and what most programmers are looking for. I had a short tip published by Pinnacle Publishing in their Visual Basic Developer magazine that covered some of these flaws. Originally, the tip was free to view but is now viewable only by subscribers.. Basically, it said that IsNumeric returned True for things like -- currency symbols being located in front or in back of the number as shown in my example (also applies to plus, minus and blanks too); numbers surrounded by parentheses as shown in my example (some people use these to mark negative numbers); numbers containing any number of commas before a decimal point as shown in my example; numbers in scientific notation (a number followed by an upper or lower case "D" or "E", followed by a number equal to or less than 305 -- the maximum power of 10 in VB); and Octal/Hexadecimal numbers (&H for Hexadecimal, &O or just & in front of the number for Octal). NOTE: ====== In the above example and in the referenced tip, I refer to $ signs and commas and dots -- these were meant to refer to your currency, thousands separator and decimal point symbols as defined in your local settings -- substitute your local regional symbols for these if appropriate. As for your question about checking numbers, here are two functions that I have posted in the past for similar questions..... one is for digits only and the other is for "regular" numbers: Function IsDigitsOnly(Value As String) As Boolean IsDigitsOnly = Len(Value) 0 And _ Not Value Like "*[!0-9]*" End Function Function IsNumber(ByVal Value As String) As Boolean ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9.]*" And _ Not Value Like "*.*.*" And _ Len(Value) 0 And Value < "." And _ Value < vbNullString End Function Here are revisions to the above functions that deal with the local settings for decimal points (and thousand's separators) that are different than used in the US (this code works in the US too, of course). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String ' Get local setting for decimal point DP = Format$(0, ".") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) 0 And Value < DP And _ Value < vbNullString End Function I'm not as concerned by the rejection of entries that include one or more thousand's separators, but we can handle this if we don't insist on the thousand's separator being located in the correct positions (in other words, we'll allow the user to include them for their own purposes... we'll just tolerate their presence). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String Dim TS As String ' Get local setting for decimal point DP = Format$(0, ".") ' Get local setting for thousand's separator ' and eliminate them. Remove the next two lines ' if you don't want your users being able to ' type in the thousands separator at all. TS = Mid$(Format$(1000, "#,###"), 2, 1) Value = Replace$(Value, TS, "") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) 0 And Value < DP And _ Value < vbNullString End Function Rick "Sam Kuo" wrote in message ... Hi I have 3 textboxes in UserForm where user inputs value and the sum must always add up to 100. If any 2 textboxes have valid input, the third will be filled in automatically. Here is my attempt using nested IF, but I doubt I'm declaring my textboxes or used the "IsEmpty" function correctly here because type mismatch error occurs...Can someone please help me making it work? Note: The following code relates to first textbox (txtSilt) only. Codes for the other 2 textboxes are similar and I'll post them if it helps. Private Sub txtSilt_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) ' Declare 3 textboxes (txtSilt, txtSand, txtClay) Dim NumtxtSilt As Variant, NumtxtSand As Variant, NumtxtClay As Variant NumtxtSilt = Me.txtSilt.Value NumtxtSand = Me.txtSand.Value NumtxtClay = Me.txtClay.Value ' If the input is NOT numerical, show warning label, ' show and copy null value to worksheet, by calling sub CopySiltEmpty If Not IsNumeric(NumtxtSilt) Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- You must enter a NUMBER" CopySiltEmpty ' Or if the sum of Silt and Sand is within 100%, ' hide warning label, show and copy both Silt and calculated Clay value ' to worksheet, by calling sub CopySilt and sub CopyClay ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtSand) And NumtxtSilt + NumtxtSand <= 100 Then NumtxtClay = 100 - NumtxtSilt - NumtxtSand CopySilt CopyClay ' Or if the sum of Silt and Sand exceeds 100%, ' show warning label, show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtSand) And NumtxtSilt + NumtxtSand 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Total soil composite percentage must add up to 100%" CopySiltEmpty ' Or if the sum of Silt and Clay is within 100%, ' show and copy both Silt and calculated Sand values to worksheet ' by calling sub CopySilt and sub CopySand ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtClay) And NumtxtSilt + NumtxtClay <= 100 Then NumtxtSand = 100 - NumtxtSilt - NumtxtClay CopySilt CopySand ' Or if the sum of Silt and Clay exceeds 100%, ' show warning label, show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtClay) And NumtxtSilt + NumtxtClay 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Total soil composite percentage must add up to 100%" CopySiltEmpty ' Or if the input is outside 0 and 100, show warning label, ' show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf NumtxtSilt < 0 Or NumtxtSilt 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Please enter a number between 0 and 100" CopySiltEmpty ' Else hide warning label, show and copy data to worksheet ' by calling sub CopySilt Else CopySilt End If End Sub ' Below is stored in Module 1 Sub CopySiltEmpty() Dim ws As Worksheet Dim rngSilt As Range Dim NumtxtSilt As Variant Set ws = ThisWorkbook.Worksheets("Form Control 1") Set rngSilt = ws.Range("B4") ' Clear input and change textbox BackColor to yellow NumtxtSilt = frmUSLE.txtSilt.Value NumtxtSilt = Empty frmUSLE.txtSilt.Value = Empty frmUSLE.txtSilt.BackColor = &HFFFF& ' Copy empty value to the worksheet rngSilt.Value = Empty End Sub ' Below is stored in Module 1 Sub CopySilt() Dim ws As Worksheet Dim rngSilt As Range Set ws = ThisWorkbook.Worksheets("Form Control 1") Set rngSilt = ws.Range("B4") ' Set textbox format to one decimal point frmUSLE.txtSilt.Text = Format(frmUSLE.txtSilt.Text, "#,##0.0") ' Copy the data to the worksheet rngSilt.Value = frmUSLE.txtSilt.Value ' Hide warning label, change textbox BackColor to white frmUSLE.labSiltWarning.Visible = False frmUSLE.txtSilt.BackColor = &H80000005 End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox question
Just put my IsNumber function in the Module along with your subroutines. As
for using it, change this line form your code... If Not IsNumeric(NumtxtSilt) Then to this instead.... If Not IsNumber(NumtxtSilt) Then Rick "Sam Kuo" wrote in message ... Thanks Rick. I'll give it a shot :-) Just before I do, do you mind explain how I should incorporate your IsNumber function to my code and where to put it (sorry I'm a newbie in programming as you can tell)? Basically the value can only be positive (including 0) and upto 100 (formated to 1 decimal point). Thanks Sam "Rick Rothstein (MVP - VB)" wrote: IsEmpty is only useful to see if a Variant variable in not yet initialize (that is, Dim'med, but with nothing assigned to it yet). I would Dim the NumtxtSilt, NumtxtSand and NumtxtClay variables as Strings and test it for "no text" this way... If Len(NumtxtSilt) 0 Then ' Variable has text in it Else ' Variable has no text in it End If As for your use of IsNumeric, consider the following message I've posted in the past.... IsNumeric is not a very good "number proofer" given what most programmers expect it to do. Perhaps you will find the following which I have posted in the past to the compiled VB newsgroups (but which applies equally to the VBA world as well)... I usually try and steer people away from using IsNumeric to "proof" supposedly numeric text. Consider this (also see note below): ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)") Most people would not expect THAT to return True. IsNumeric has some "flaws" in what it considers a proper number and what most programmers are looking for. I had a short tip published by Pinnacle Publishing in their Visual Basic Developer magazine that covered some of these flaws. Originally, the tip was free to view but is now viewable only by subscribers.. Basically, it said that IsNumeric returned True for things like -- currency symbols being located in front or in back of the number as shown in my example (also applies to plus, minus and blanks too); numbers surrounded by parentheses as shown in my example (some people use these to mark negative numbers); numbers containing any number of commas before a decimal point as shown in my example; numbers in scientific notation (a number followed by an upper or lower case "D" or "E", followed by a number equal to or less than 305 -- the maximum power of 10 in VB); and Octal/Hexadecimal numbers (&H for Hexadecimal, &O or just & in front of the number for Octal). NOTE: ====== In the above example and in the referenced tip, I refer to $ signs and commas and dots -- these were meant to refer to your currency, thousands separator and decimal point symbols as defined in your local settings -- substitute your local regional symbols for these if appropriate. As for your question about checking numbers, here are two functions that I have posted in the past for similar questions..... one is for digits only and the other is for "regular" numbers: Function IsDigitsOnly(Value As String) As Boolean IsDigitsOnly = Len(Value) 0 And _ Not Value Like "*[!0-9]*" End Function Function IsNumber(ByVal Value As String) As Boolean ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9.]*" And _ Not Value Like "*.*.*" And _ Len(Value) 0 And Value < "." And _ Value < vbNullString End Function Here are revisions to the above functions that deal with the local settings for decimal points (and thousand's separators) that are different than used in the US (this code works in the US too, of course). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String ' Get local setting for decimal point DP = Format$(0, ".") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) 0 And Value < DP And _ Value < vbNullString End Function I'm not as concerned by the rejection of entries that include one or more thousand's separators, but we can handle this if we don't insist on the thousand's separator being located in the correct positions (in other words, we'll allow the user to include them for their own purposes... we'll just tolerate their presence). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String Dim TS As String ' Get local setting for decimal point DP = Format$(0, ".") ' Get local setting for thousand's separator ' and eliminate them. Remove the next two lines ' if you don't want your users being able to ' type in the thousands separator at all. TS = Mid$(Format$(1000, "#,###"), 2, 1) Value = Replace$(Value, TS, "") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) 0 And Value < DP And _ Value < vbNullString End Function Rick "Sam Kuo" wrote in message ... Hi I have 3 textboxes in UserForm where user inputs value and the sum must always add up to 100. If any 2 textboxes have valid input, the third will be filled in automatically. Here is my attempt using nested IF, but I doubt I'm declaring my textboxes or used the "IsEmpty" function correctly here because type mismatch error occurs...Can someone please help me making it work? Note: The following code relates to first textbox (txtSilt) only. Codes for the other 2 textboxes are similar and I'll post them if it helps. Private Sub txtSilt_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) ' Declare 3 textboxes (txtSilt, txtSand, txtClay) Dim NumtxtSilt As Variant, NumtxtSand As Variant, NumtxtClay As Variant NumtxtSilt = Me.txtSilt.Value NumtxtSand = Me.txtSand.Value NumtxtClay = Me.txtClay.Value ' If the input is NOT numerical, show warning label, ' show and copy null value to worksheet, by calling sub CopySiltEmpty If Not IsNumeric(NumtxtSilt) Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- You must enter a NUMBER" CopySiltEmpty ' Or if the sum of Silt and Sand is within 100%, ' hide warning label, show and copy both Silt and calculated Clay value ' to worksheet, by calling sub CopySilt and sub CopyClay ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtSand) And NumtxtSilt + NumtxtSand <= 100 Then NumtxtClay = 100 - NumtxtSilt - NumtxtSand CopySilt CopyClay ' Or if the sum of Silt and Sand exceeds 100%, ' show warning label, show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtSand) And NumtxtSilt + NumtxtSand 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Total soil composite percentage must add up to 100%" CopySiltEmpty ' Or if the sum of Silt and Clay is within 100%, ' show and copy both Silt and calculated Sand values to worksheet ' by calling sub CopySilt and sub CopySand ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtClay) And NumtxtSilt + NumtxtClay <= 100 Then NumtxtSand = 100 - NumtxtSilt - NumtxtClay CopySilt CopySand ' Or if the sum of Silt and Clay exceeds 100%, ' show warning label, show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf Not IsEmpty(NumtxtSilt) And Not IsEmpty(NumtxtClay) And NumtxtSilt + NumtxtClay 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Total soil composite percentage must add up to 100%" CopySiltEmpty ' Or if the input is outside 0 and 100, show warning label, ' show and copy empty value to worksheet ' by calling sub CopySiltEmpty ElseIf NumtxtSilt < 0 Or NumtxtSilt 100 Then Me.labSiltWarning.Visible = True Me.labSiltWarning.Caption = "<-- Please enter a number between 0 and 100" CopySiltEmpty ' Else hide warning label, show and copy data to worksheet ' by calling sub CopySilt Else CopySilt End If End Sub ' Below is stored in Module 1 Sub CopySiltEmpty() Dim ws As Worksheet Dim rngSilt As Range Dim NumtxtSilt As Variant Set ws = ThisWorkbook.Worksheets("Form Control 1") Set rngSilt = ws.Range("B4") ' Clear input and change textbox BackColor to yellow NumtxtSilt = frmUSLE.txtSilt.Value NumtxtSilt = Empty frmUSLE.txtSilt.Value = Empty frmUSLE.txtSilt.BackColor = &HFFFF& ' Copy empty value to the worksheet rngSilt.Value = Empty End Sub ' Below is stored in Module 1 Sub CopySilt() Dim ws As Worksheet Dim rngSilt As Range Set ws = ThisWorkbook.Worksheets("Form Control 1") Set rngSilt = ws.Range("B4") ' Set textbox format to one decimal point frmUSLE.txtSilt.Text = Format(frmUSLE.txtSilt.Text, "#,##0.0") ' Copy the data to the worksheet rngSilt.Value = frmUSLE.txtSilt.Value ' Hide warning label, change textbox BackColor to white frmUSLE.labSiltWarning.Visible = False frmUSLE.txtSilt.BackColor = &H80000005 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox Question | Excel Programming | |||
Textbox Question | Excel Programming | |||
Textbox question | Excel Discussion (Misc queries) | |||
Textbox question? | Excel Discussion (Misc queries) | |||
Textbox question 2 | Excel Programming |