+ Reply to Thread
Results 1 to 15 of 15

How do I fix Combobox to return a number instead of text?

  1. #1
    Forum Contributor
    Join Date
    02-02-2013
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2010
    Posts
    167

    How do I fix Combobox to return a number instead of text?

    Im using a combobox to select the employee ID number. When selected it goes into "F5" where it is suppose to. But the number is formated as text.
    How can I have it formatted as a number when it appears in "F5"? Would like to have a macro run so it would convert text to a number. Any help would be appreciated. Thanks

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: How do I fix Combobox to return a number instead of text?

    If you have "12345" in the textbox and you want it to be a numeric vs string value use:
    Range("F5").Value = CLng(TextBox1.Text)
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    02-02-2013
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2010
    Posts
    167

    Re: How do I fix Combobox to return a number instead of text?

    Please see my attached file. I was not sure where to enter the above formula.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: How do I fix Combobox to return a number instead of text?

    The Emp # in F5 of sheet Cont. Info comes from a combobox on that sheet. The combobox is populated from column A of sheet Employee, which gets the number from a user form. So Try:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    02-02-2013
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2010
    Posts
    167

    Re: How do I fix Combobox to return a number instead of text?

    This is the error that I receive when I try and run it.

    Run-time error '424':

    Object required


    Did I enter the code correctly? Below is how it is entered into the combobox

    Please Login or Register  to view this content.

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: How do I fix Combobox to return a number instead of text?

    I can't find a Textbox named TextEmployeeNo on your Userform so that's likely the reason for error.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  7. #7
    Forum Contributor
    Join Date
    02-02-2013
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2010
    Posts
    167

    Re: How do I fix Combobox to return a number instead of text?

    If you look at the second tab on the Userform that is where it is located.

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: How do I fix Combobox to return a number instead of text?

    Works fine for me.
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    02-02-2013
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2010
    Posts
    167

    Re: How do I fix Combobox to return a number instead of text?

    Yes it works fine if you enter the employee no into "F5". But if you use the combobox and select employee no it does not work. It is entered it into "F5" as text.

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: How do I fix Combobox to return a number instead of text?

    In your form "NewEmployee" you have code for CommandButtonAdd_Click() where sht is declared and set:
    Dim sht As Worksheet
    Set sht = Worksheets("Employee")
    So the code: sht.Cells(xRow, 1).Value = CLng(TextEmployeeNo.Value) adds the employee number when the add button is clicked on the form.

    Combobox1 is on "Contact Information" sheet, the variable sht does not exist in the change event code. It seems like you are trying to add the employee number from two different places, the form and the combobox, but since the combobox is linked to F5 and populated from the database updated by the form, any change in the combobox selection is updated in F5, so I don't understand why you need the change code.

  11. #11
    Forum Contributor
    Join Date
    02-02-2013
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2010
    Posts
    167

    Re: How do I fix Combobox to return a number instead of text?

    Is there anything else that can be done to help me out on this? Would really appreciate it.

    The userform was so I could store all the employees in a data base. I'm wanted to use the combobox to find an employee that will give me all the contact information on the form sheet that is named "Contact Information"
    Last edited by Roco; 05-16-2018 at 10:01 PM.

  12. #12
    Forum Contributor
    Join Date
    02-02-2013
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2010
    Posts
    167

    Re: How do I fix Combobox to return a number instead of text?

    Is there anything else that can be done to help me out on this? Would really appreciate it.

    The userform was so I could store all the employees in a data base. I'm wanted to use the combobox to find an employee that will give me all the contact information on the form sheet that is named "Contact Information"

  13. #13
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: How do I fix Combobox to return a number instead of text?

    Remove the Linked cell and ListFillRange from CB - properties and put this in Sheet module.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    02-02-2013
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2010
    Posts
    167

    Re: How do I fix Combobox to return a number instead of text?

    Thanks see is what I was wishing for. I appreciated it.

  15. #15
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: How do I fix Combobox to return a number instead of text?

    Glad to assist and thanks for rep+.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. If any-one of three cells less than a certain number, then return text
    By preveo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-20-2018, 03:20 PM
  2. Replies: 2
    Last Post: 01-28-2015, 09:07 AM
  3. [SOLVED] look up text and return corresponding number
    By doylzer in forum Excel General
    Replies: 4
    Last Post: 10-15-2014, 05:08 AM
  4. Replies: 1
    Last Post: 10-08-2014, 05:56 AM
  5. [SOLVED] Return the first number in a text string
    By MarvinP in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-22-2014, 03:16 PM
  6. I need a ComboBox to return another Combobox if a specific answer is selected
    By concatch in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-22-2012, 03:43 AM
  7. [SOLVED] Return text result from User Form ComboBox entry
    By deganwy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-23-2012, 09:40 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1