textbox as value, not text (2002)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

how would I do that?

Reply | Quote

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

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

Reply | Quote

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

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

Reply | Quote

how would I do that?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

That’s what it is called a TEXTbox.

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.

Reply | Quote

That’s what it is called a TEXTbox.

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote