Posts by Kng62ka

    Re: Office 2016 macro errors when using non-English interface

    Hey mcockbur... I don't have 2016 installed (yet - we are about to change to this version) but I work with Excel in English while all of my coworkers work with the Spanish version. Long story short: It is better to code in English, yeah... but you have to be EXTREMELY careful because it's extremely easy to make mistakes that will mess the formulas when opening them in computers that use another language but English.

    Example: A formula that I set up to count the times a checkbox had been marked - COUNTIF(A:A;"True") - gave me issues for the longest time ever because, although the checkmark result did show up as 'True' in my computer, it showed as 'Verdadero' in the Spanish version. After reading a while, I decided to delete the formula and enter it again using Excel's 'Insert Function' button. The formula that it gave me was COUNTIF(A:A;TRUE) - That is, Excel removed the " " so now the 'True' in the formula translates to 'Verdadero' in my coworkers' computers (wasn't doing it before because I had " ")... which means that now the formula works in all of our computers (regardless of how Excel shows the result of the checkboxes).

    In all honesty, although I prefer coding in one single language (and since VBA is mostly English, this normally means I code in English) I have been thinking about changing to the Spanish version just to avoid these kind of mistakes that (although silly once you realize what's happened) can be a huge headache to figure out.

    Re: Userform formulas (vlookup, etc) do not work while other workbooks are open / on

    royUK, I am not sure if I didn't explain myself well or if I am not understanding you well... When I say formulas, I don't mean excel formulas - I mean code in VBA. The vlookup code in VBA from my userform won't work unless its workbook is right behind the userform. I want it to work even if the workbook is behind 10 different screens.
    Here is my userform:



    I have created a userform for my company's employees so that they can more easily keep track of the activities they do during the day.
    This form pulls client information from a (hidden) sheet in the same workbook and also writes informations to a (visible) sheet in the same workbook.
    Because our employees work a lot with excel, I had to set "Modal" to FALSE and add a minimize button to the form

    Problem: When our employees forget to maximize the excel so that it is right behind the userform, the userform gives them errors and the vlookup simply doesn't work (I've deactivated errors for vlookup so it didn't go crazy every time someone hit backspace).

    I have thought of minimizing the excel workbook right behind the userform, but I know our employees will probably end up minimizing the excel workbook as soon as they need more screen space anyway
    I have also thought of somehow making sure that my formulas specify the name of the workbook... but I have no idea how to do this (I self-taught myself VBA so I am probably unaware of a lot of basic information)

    Basically... I need help figuring out if I can do something about this issue, or if I should just tell them "You are just gonna have to make sure the right excel workbook is underneath the userform if you want the vlookup to work and the information to get written"

    Re: Excel Worksheet Function getting details from other sheets dynamically

    I created dynamic ranges to solve the issue of having to specify the worksheet, because this way you only have to specify the name of the list.


    'Vlookup by client name
    On Error Resume Next
        Me.combo_clientnumber.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 2, False)
        Me.tbox_association.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 3, False)
        Me.tbox_clienttype.Value = WorksheetFunction.VLookup(Me.combo_clientname.Value, Range("lookup_clientname"), 4, False)

    Re: 2 combo boxes with vlookup in the same userform that interact with each other, tb

    !!! After 1 week of working on this problem, I just thought of something (sort of) new that actually solved my issue. It was a super simple thing, as I was expecting. I am adding the solution here in case anybody else in the future stumbles upon this thread with a similar issue.

    REAL PROBLEM: Vlookup doesn't work when numbers are used as the reference value
    SOLUTION: Convert numbers to text by selecting them > clicking on 'Data' > clicking on 'Text to Columns' > format as delimited TEXT ... this converts all of the numbers to text by adding ' at the start of each number, and now vlookup will work


    I am quite new to VBA, and everything I know I've taught myself... so sometimes I miss "simple" things that I hadn't encountered before. This may be one of those things.

    Bakcground: I created a userform so that employees at my company are able to more easily complete their daily work diary. The form includes many fields, some related to the employee(seller) and some related to the customer. On the customer side, I have 2 combo boxes and multiple texboxes: 1 combo contains customers' names and the other 1 contains customers' numbers.

    What I want: To be able to choose a name OR a number and have the rest of the customer's information populate on the rest of the fields.

    The problem: The first combo box 'combo_clientname' and its corresponding vlookup code works perfectly BUT the second combo 'combo_clientnumber' isn't working even though the code is basically identical. While the list of clients' numbers does show, the vlookup returns nothing.

    The error Excel is giving me is: Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class (normally I add "On Error Resume Next" in vlookups in case somebody hits backspace or something it doesn't give them this error, but I deleted this line to see what Excel would tell me about the error it was encountering... and it gave me this one)

    Things I have tried: Removing the first vlookup formula in case I couldn't have 2 lookups in the same userform, formatting client number as "general" in the range itself in case being formatted as "number" was what was causing the error, reading and reading and reading through many different forums to no avail - I can't figure out what I am doing wrong!

    Here is my code: