• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1202
  • Last Modified:

ActiveX TextBox VBA Excel 2010

Dear Experts,

I'm having trouble to enable the user to enter a decimal number in my TextBox.
Please see code below.

Private Sub TextBox1_Change()
       
        With TextBox1
       
            .LinkedCell = "Sheet1!$P$2"
             On Error Resume Next
            ActiveWorkbook.Sheets("Sheet1").Range("P2") = CLng(Me.TextBox1)
            ActiveWorkbook.Sheets("Sheet1").Range("P2").NumberFormat = comma
             On Error GoTo 0
       End With
       
 Can someone please help.

Thanks,
K
0
StierInvest
Asked:
StierInvest
  • 4
  • 3
3 Solutions
 
Chris BottomleyCommented:
Long isn't a decimal ... try csng or cdbl

Chris
0
 
StierInvestAuthor Commented:
Hi Chris,

Still no luck. As soon as I start with a "." it lets me enter it, but not "5.56" for example.

Any other ideas?

Thanks,
K
0
 
NorieVBA ExpertCommented:
CLng is converting the number into an long integer, for a decimal number you need to use Val, CSng, CDbl.

Or you could try not using anything.

Oh, and you should format the cell before you put a value in it.
ActiveWorkbook.Sheets("Sheet1").Range("P2").NumberFormat = "0.00"
ActiveWorkbook.Sheets("Sheet1").Range("P2") = Me.TextBox1 ' CDbl(Me.Textbox1)

Open in new window

Just noticed you are using the change event, that will be triggered on everytime you change anything in the textbox.

You should consider using a different event.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
StierInvestAuthor Commented:
Thanks Imnorie.

That seems to work, is there then a quick way of converting the text to a number where the cell is linked to?

Best,
K
0
 
NorieVBA ExpertCommented:
When would you want that to happen?
0
 
StierInvestAuthor Commented:
Well was hoping as soon as the user enters a number. Is there not a better way by not "*1" all of the entries?
0
 
NorieVBA ExpertCommented:
How about not using LinkedCell?

That's actually what's preventing you entering decimals.
Private Sub TextBox1_Change()

       With ActiveWorkbook.Sheets("Sheet1").Range("P2")

            .NumberFormat = "0.00"
            .Value = Me.TextBox1

            If IsNumeric(.Value) Then
                .Value = .Value * 1
            End If

        End With
    
End Sub

Open in new window

0
 
StierInvestAuthor Commented:
Perfect thank you
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now