Consulting

Results 1 to 2 of 2

Thread: VBA userform output storing number as text

  1. #1

    Question VBA userform output storing number as text

    Hi Everyone,
    I am having some trouble with some VBA code.
    I have a userform using textboxes and Comboboxes to capture new client information.
    The issue I am having is that when the data copies from the userform to the table in the worksheet, any numbers copied get stored as text rather than as a number.

    I cant seem to find a way to fix this, any ideas:

    Dim rng As Range
    Set rng = Sheets("Clients").ListObjects("Clients").Range
    Dim LastRow As Long
    LastRow = rng.Find(What:="*", _
    After:=rng.Cells(1), _
    Lookat:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row
    
    
    rng.Parent.Cells(LastRow + 1, 1) = CDate(Me.tbDate)
    rng.Parent.Cells(LastRow + 1, 2).Value = Me.tbFirstname
    rng.Parent.Cells(LastRow + 1, 3).Value = Me.tbSurname
    rng.Parent.Cells(LastRow + 1, 4).Value = Me.tbAddress
    rng.Parent.Cells(LastRow + 1, 5).Value = Me.tbCity
    rng.Parent.Cells(LastRow + 1, 6).Value = Me.cmbcountry
    rng.Parent.Cells(LastRow + 1, 7).Value = Me.tbState
    rng.Parent.Cells(LastRow + 1, 8).Value = Me.tbZip
    rng.Parent.Cells(LastRow + 1, 9).Value = Me.tbPhone
    rng.Parent.Cells(LastRow + 1, 10).Value = Me.tbemail
    rng.Parent.Cells(LastRow + 1, 11).Value = Me.cmbcurrency
    rng.Parent.Cells(LastRow + 1, 12).Value = Me.tbInvestV
    rng.Parent.Cells(LastRow + 1, 14).Value = Me.cmbMinGreen
    rng.Parent.Cells(LastRow + 1, 15).Value = Me.cmbAccMan
        
    Unload Me
    
    
    
    
    End Sub
    Can someone help point me in the right direction.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Use CDbl() and CLng() to convert the strings to numbers just like CDate

    ?cdbl("123.45")
     123.45 
    
    ?clng("1234.45")
     1234
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Tags for this Thread

Posting Permissions

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