Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Defining Value of a UserForm textbox (2003 SP)

    Good evening

    I have tried searching the Interwebby but cannot find a simple answer to this, would somebody mind posting some example code that I can keep somewhere to show the following when using text boxes on user forms

    Alpha only
    numeric only (whole numbers)
    numeric only, 2 decimals
    currency

    Hope thats not too much trouble

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    Could you explain in detail and precisely what you want?

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    Hi Hans

    I have a couple of text boxes on UserForm,

    one is txtBoxType which will contain the name of a manufacturers box, this could be a mixture of Alpha,Numeric or specials such as _ - & etc. so by adding no code it behaves correctly.


    the second is txtCost which I have coded as follows (Ihave found this in a book)

    Private Sub TxtCost_Change()
    If TxtCost = vbNullString Then Exit Sub
    If Not IsNumeric(TxtCost) Then
    MsgBox "Sorry, numbers only"
    TextCost=VBNullString
    End If
    End Sub

    No matter what I try though the result in the cell on my worksheet is a left alligned number although the cell is formated as currency, it occurs to me that this is because the example snippet of code above is only allowing numbers. So my question and thinking process is that there must be a 'standard' set of commands/code to make text boxes return values to forms in certain formats, for example


    Private Sub TxtCost_Change()
    If TxtCost = vbNullstring <font color=448800>(number + 2 decimal places, right align) </font color=448800>
    Then Exit Sub
    If Not IsNumeric(TxtCost) Then
    MsgBox "Sorry, numbers only"
    TxtCost = vbNullstring <font color=448800>(number + 2 decimal places, right align)</font color=448800>

    End If
    End Sub

    I hope that this explaination makes a little more sense

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    I am gradually (slowly and painfully) getting there

    I have now added - TxtCost.Value = Format(TxtCost.Value, "#,##0")

    However it returns whole numbers only , I am struggling to get it to return something like 11.85 and it still does not right align in the cell, it does however work in calculations which is a good +

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    I don't understand why the result is left-aligned or why it would be necessary to specify the format. If I set the ControlSource of a text box on a userform to a cell that is formatted as currency with 2 decimal places, any valid value entered in the text box will be displayed in the cell as currency with two decimal places, no code needed.

    But if you want two decimal places in the line of code you posted, you must specify them:
    <code>
    TxtCost.Value = Format(TxtCost.Value, "#,##0.00")</code>

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    Ok, hours later I have

    txtCost.Value = Format(txtCost.Value, "#,##0.00")

    This now puts in a whole value up to 9 i.e. if I enter 3 it shows as 3.00 and for some bizare reasons if I press the any number between 5 & 9 it increases the pence each time I push it by 1p, if I change the line to

    txtCost.Value = Format(txtCost.Value, "#,##00000.00") and in the text box press 1 it shows 0,001.00 and again if I press or hold 5 - 9 it will increase by 1p

    I am sure I am getting close!!

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    Hi Hans

    Thanks, I did not realise that you had replied when I replied to myself at 10:27, they must have crossed, however the example you showed is the same as I put in my text and no matter what I do it shows a whole number (in desperation I even copied and pasted your line over mine), as soon as I type a 1, 1.00 is entered. How would I set the ControlSource it gives me no options and Googling does not throw up anything?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    I don't understand. 1 = 1.00. If you need the cell to display the decimals, you can format it the cell contents

    Do you want if someone enters a 1 to actually be 0.01? If so you can just divide the entry by 100 before putting it into the cell.

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    Hi Steve

    On the user form I would like to be able to put xx.xx ie 9.87, 143.98 etc. my cell is formatted as currency and the text box command is

    Private Sub TxtCost_Change()
    txtCost.Value = Format(txtCost.Value, "#,##0.00")
    If txtCost = vbNullString Then Exit Sub
    If Not IsNumeric(txtCost) Then
    MsgBox "Sorry, numbers only"
    TextBox1 = vbNullString
    End If
    End Sub

    But when I run the form as soon as I enter the text box Cost and type a number it takes that as a whole number, ie. if I type 1 it immediately shows 1.00, if I press 4 it changes immediately to 4.00 and won't let me change it unless I press number 5 through 9 and it will then increment the pence by 1p, if say I enter 1 it will show 1.00 automatically and I can't change it but if I then press 5,6,7,8 or 9 it will change to 1.01 etc.

    Hope thats clear

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    I can't replicate the problem. I have the issue that since you FORMAT the textbox, it is not a number and I get the error...

    Could you attach an example file with the code having the problem you indicate?

    Steve

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    Hi Steve

    Thanks for the offer of having a look, I have had to cull the WB to bring the size down but this stand alone WS exactly shows the problem with the Add Stock Form / the Cost text box.

    (I realise that I have a problem with the row count for the Add Country code but I will try and solve that later)

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    Here is possible solution. First, unlink the textbox from the cell on the worksheet. Then remove your textbox change event code and put the code below in the module behind the userform:

    <code>
    Private Sub TxtCost_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim curCost As Currency
    If TxtCost = vbNullString Then Exit Sub
    If Not IsNumeric(TxtCost) Then
    MsgBox "Sorry, numbers only"
    TextCost = vbNullString
    Else
    curCost = TxtCost
    Worksheets("Sheet1").Range("A1").Value = curCost
    End If

    End Sub
    </code>


    Change "Sheet1" and "A1" in the above code to the sheet and cell where the cost is to be stored. Format the cell to display the value as you desire.
    Legare Coleman

  13. #13
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    Hi Legare

    Thanks for this but I cannot get this to work as I presume you expected

    1) I need to enter a currency value to 2 decimal places, with this code I can enter a single 1 and it will give 1 or 5.76 or 12.95 whatever is entered, not 1.00 or 5.76 or 12.95 etc. as I require

    2) My Cost range starts in B5 so I changed the code to B5, as soon as I tab within the userform B5 is updated before I close the form, If I then enter subsequent entries they all update OK but change B5 to whatever the last entry was in txtCost

    3) It still does not show as currency.

    It is probably me not quite grasping it but any further help greatfully accepted.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    Format the appropriate cells as currency.

    If you force the userform (as you seem to want to do) to display the textbox with non-numerics then you can not test for non-numerics since you are building them into the cell. And also after the first number is entered, the decimal point and the 2 zeros are added so the next number entered into the textbox goes AFTER the 2 zeroes unless the users deletes them (and continually deletes them until the correct number is entered).

    The only way around this would be to use 2 textboxes: one to enter and validate and the other to display what is entered and formatted

    Steve

  15. #15
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defining Value of a UserForm textbox (2003 SP)

    Hi Steve

    I am probably wording wrong what I am trying to do and am confusing the issue, I think I will settle for the fact that I am able to enter a number and although the cell is formatted as currency it won't show the sign, the more important thing for me was that on another worksheet I could do calculations in currency based on this cell, for example I have a column that adds all of the same stock item and then multiplies it by the price from this cell and that is still showing in currency which it does.

    Thanks though to you and all that attempted to help along the way with this one

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •