Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    textbox as value, not text (2002)

    I have a worksheet with several textboxes I inserted from the Control Toolboox. I linked the textboxes to cells in my spreadsheet.

    When I input the numbers in the textboxes, number as text are entered in the linked cells. I have several formulas that use the linked cells numbers, so I need numbers, not text in my linked cells.

    How do I solve this?

    Thank you
    Guillermo

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: textbox as value, not text (2002)

    You can use the VALUE function to convert the text to values. If your Textbox returns contents to A1, =VALUE(A1) will return a numeric value, provided of course that the contents can be evaluated a numeric value.


    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: textbox as value, not text (2002)

    That would work.

    But,,,, problem: I have several formulas that refer to A1. And replacing A1 in the formulas , for Value(A1), would add a lot of text in my formulas and make syntax difficult to understand. (I have several cells that are linked to several textboxes. These cells provide numeric data to the formulas)

    Is there a way I can format the text box so the cell receives the numbers that the user is typing , already as numeric data, so I dont have to edit the formulas?

    Thank you
    Guillermo

  4. #4
    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: textbox as value, not text (2002)

    Put the cellLink in another cell (eg A2), then put in A1 =value(A2)

    Now all your fomulas will work without changing since A1 has a value.
    Steve

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: textbox as value, not text (2002)

    User input in a textbox (inserted from the Control Toolbox and linked a spreadsheet cell) always defaults to text?

    There is no way to overide that? Not even with code?
    Guillermo

  6. #6
    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: textbox as value, not text (2002)

    You could get the results of the textbox in a string variable
    Add to a single, double, long, or integer variable the results using Value (or other function) then place the results of the VALUE variable in the cell of interest. You would NOT be using the LinkedCell property at all in this case.

    Steve

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

    Re: textbox as value, not text (2002)

    That's what it is called a TEXTbox. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    If you don't link the textbox to the cell, then you could use the textbox change event routine VBA code to take the text from the textbox, convert it to a number, and store the number into the cell.
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: textbox as value, not text (2002)

    it seems that this does this does the trick:

    Private Sub x_spacing_Change() ' x_spacing is the textbox
    Dim userinput As Integer
    userinput = x_spacing.Value
    Sheets("hidden_data").Range("xspace").Value = userinput
    End Sub

    But I am surprised that although having and advanced user interface like the properties window for the textbox, that it slipped from the developers, that textbox user input could be formatted there as an option! Recognizing user input as text , numbers, etc and sending with that format to the linked cell should be straight forward and automatic.

    Thank you
    Guillermo

  9. #9
    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: textbox as value, not text (2002)

    You might want to add error checking. If the TEXT they entered can not be interpreted as an integer, you will get a runtime error

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: textbox as value, not text (2002)

    how would I do that?
    Guillermo

  11. #11
    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: textbox as value, not text (2002)

    Here is some simple code. It checks to see if the "string" is a number and if it is, enter it as an integer, if not it beeps and gets rid of the last item entered.

    Steve

    <pre>Option Explicit
    Private Sub x_spacing_Change() ' x_spacing is the textbox
    Dim sUserInput As String

    sUserInput = x_spacing.Value
    If IsNumeric(sUserInput) Then
    Sheets("hidden_data").Range("xspace").Value = _
    Int(sUserInput)
    Else
    Beep
    If sUserInput <> "" Then _
    x_spacing.Value = _
    Left(sUserInput, Len(sUserInput) - 1)
    End If
    End Sub</pre>


Posting Permissions

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