Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using user form text value (excel ms office 2000)

    Hello,

    I am wodering how I can get my txt box in my form to put the number 1000 in a range I specified. currently it will only put 100 in.

    Private Sub chem1txt1_Change()
    Sheets("Data").Activate
    Range("C2").Value = chem1txt1.Value 'this seems to limit the number of digits that can go into a cell on a worksheet??
    End Sub

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

    Re: using user form text value (excel ms office 2000)

    If you set the ControlSource of the text box to Data!C2, the value of C2 will automatically be updated when the user tabs out of the text box or closes the userform.

    If you prefer to set the value in code, I would use the AfterUpdate event, but there is no reason why the Change event shouldn't work. It should be possible to enter many more characters.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using user form text value (excel ms office 2000)

    i didnt know about the propertie, from you what is the best in a designers view code or properties?

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

    Re: using user form text value (excel ms office 2000)

    It depends on what you want to accomplish. In general, using the Control Source property is by far the easiest, because Excel does all the work for you - the text box is filled automatically with the value of the cell when the userform is opened, and the cell is automatically updated when the user edits the value in the text box. So it seems ideal for your situation.

    But if you want to do more complicated things with the value of the text box before updating the value of a cell, such as performing a calculation, code may be more suitable. Moreover, it enables you to cancel the update if the user enters an incorrect value.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using user form text value (excel ms office 2000)

    so if i want to subtract to other text boxes i would have to use code. is this close to what i need for syntax?

    ie
    Private Sub Chem1tot1_Change()
    Sheets("Chemical Data").Activate
    chem1tot1 = chem1txt1.value - chem1txt21.value
    End Sub

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

    Re: using user form text value (excel ms office 2000)

    In the first place, the line

    Sheets("Chemical Data").Activate

    seems to serve no useful purpose here. In the second place, since you want the result of Chem1tot1 to be calculated from chem1txt1 and chem1txt21, you should put code in the Change event of those text boxes, not that of Chem1tot1.

    Private Sub chem1txt1_Change()
    Chem1tot1 = chem1txt1.value - chem1txt21.value
    End Sub

    Private Sub chem1txt21_Change()
    Chem1tot1 = chem1txt1.value - chem1txt21.value
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using user form text value (excel ms office 2000)

    Thank you,

    now before Ido this to all 480 text boxes, is there an easier way? like doing the calculation on a worksheet and just showing the result?

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

    Re: using user form text value (excel ms office 2000)

    I sincerely hope you haven't created a userform with 480 text boxes! I don't think users would like that...

    If you link chem1txt1 to a cell, say A1, and chem1txt21 to another cell, say B1, you can put this formula in C1: =A1-B1
    You wouldn't need the chemt1ot1 text box any more (and you shouldn't link a text box to C1, for the formula in C1 would be overwritten by the value of the text box).

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using user form text value (excel ms office 2000)

    I have created a userform with 480 txt boxes <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> but some are totals ad not all will be used just there incase <img src=/S/poke.gif border=0 alt=poke width=60 height=17>

    i just want to show the result of typing in the boxes.

    the reason I am developing a user form is because this workbook will be used in an enviroment were someone will be gathering information while a job is happening so clicking through different chemicals and entering stage volues has to be quick.

    plus the userform looks a lot cleaner.

    from a maintance point tough 480 txt boxes will be a pain in the ....

    do you have a better suggestion,
    I can post my little form if you would like

    thank you by the way for the advice

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

    Re: using user form text value (excel ms office 2000)

    I don't know where you want the data to end up, so it's hard to recommend anything, but it might be easier to let the user select something from a combo box or list box.

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using user form text value (excel ms office 2000)

    i have attached my monster, seems like its starting to become a night mare and stuff is getting ugly and the scary part is that the will be 11 more multipages that have to do the same thing

    any suggestions?

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

    Re: using user form text value (excel ms office 2000)

    If I were you, I'd forget about the userform, and let the user enter data directly in the workbook. You can use dropdown lists (from Data | Validation) to let the user select items such as chemicals.

Posting Permissions

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