Posts by Fencliff

    Re: Working With Values N Dynamic Arrayi

    I have absolutely no idea what you are trying to do, but here is an example of how to iterate a 2d array.

    Re: Passing Parameter To Vba From C++


    I don't have much experience with C++, so can't help you with actual code. From what I can remember, this is fairly easy through MFC, but you might have to do some marshalling because all native C++ data types are not compatible with Excel's. In any case, you could use Excel's COM automation by connecting with CreateDispatch and instead of using Workbook_Open() you could run the VBA code directly using Application.Run and pass the parameters from your DLL.

    Search MSDN or google for MFC automation with Excel, should get you started.

    Re: Handling Of #div/0! In Select Case Statement?

    Quote from Robin1981


    On Error Resume Next

    at the start of your sub

    Bad idea.

    Try this instead:

    If Not Application.WorksheetFunction.IsError(rngCell) Then
            'Select Case ...
            rCell.Interior.ColorIndex = 15
        End If

    Re: Inserting Check Mark By Cliking On A Cell

    That's correct. What's wrong is hard to say.

    - You pasted the code in the wrong place
    - Excel's events are disabled - restarting Excel would help
    - You are debugging some other code.
    - Million other reasons.

    If you send the workbook where you pasted this, maybe somebody can take a look at it.

    Re: Password Protect A Macro At Run

    That's a fairly vague requirement. What level of security do you need? Do you need individual passwords for different users? Etc.

    Here is the simplest possible implementation, and not secure by any measure, but will keep the average user away:

    Re: Labels Content Equal String Based On Scrollbar Value


    Unfortunately you can't build variable names like strings. In this case you need to use an array.

    Re: Remove All Labels In A Frame

    Just remember that you can only delete controls created at runtime. Controls created in design mode can not be removed programmatically. You could still hide them, though.

    Re: Inserting Check Mark By Cliking On A Cell

    The character "a" in the font Marlett displays a check mark. The font is available in all versions of Windows, so you should be able to use it safely.

    The click part here is a little more complicated, because Excel doesn't expose any events to determine a cell being clicked. You can either choose to use DoubleClick or SelectionChange Events. DoubleClick has the disadvantage that it might not be entirely obvious for the user, and SelectionChange will also place the check mark if user navigates to the cell using the keyboard.

    In either case, place this code in the sheet module where you want this to take effect.



    Re: Tab Delimited File Saves Commas As Dot

    I've heard the horror stories of Swedish Excel. I am living in Denmark myself, but using English Excel, thank god!

    For that same reason I have no way of testing this, but have you tried saving with FileFormat:=xlCurrentPlatformText ?

    Re: Userform Validation

    I would reconsider the lower boundary. What if the user wants to erase the current value, and gets an error? The below code will show and error when the user reaches 51 characters, and trim off the extra bits. If you want validation for zero-lenght strings, I would do it when the "OK" button or similar is clicked

    Private Sub TextBox1_Change()
        If Len(Me.TextBox1.Value) > 50 Then
            MsgBox "Error!"
            Me.TextBox1.Value = Left(Me.TextBox1.Value, 50)
        End If
    End Sub

    Re: Breakdown Number Of Part Matches


    I think it would be fun to write the bartering logic, but as I said, unfortunately I don't really have the time to do this at work, and you're not going to get me anywhere near a spreadsheet at home :)

    If this is something you do annually and on other infrequent occasions, I think that would be overkill anyway. It took me 5 minutes to dish out the clients by hand, so given even you would have to do this up to 5 times a year, and this logic would take 2-3 hours to write, the ROI just isn't there, unless you plan to do this for the next decade or so :)

    Re: Breakdown Number Of Part Matches

    I took a look at it also, and, well... to crunch this brute force would be ridiculous, especially in VBA!

    I don't really have time to implement this (sorry!) but the best way I can figure to solve this would be start by assigning the clients to CSRs according to method I provided earlier. Given a reasonable margin of error, let's say a maximum variance of 10 clients. After that you can exclude those CSRs that are really close to the "optimal" sum, that is Total Clients/5 +- 2. If any CSRs do not fit into this, you can start bartering letters between CSRs so that the deficient ones put out a buying signal, and the ones in excess give a selling signal. By that logic you could have a reasonable amount of combinations of compare and trade until all CSRs are within the limit.

    Re: Data Editor On Userform

    Did you really just nonchalantly ask if somebody would do this for you?

    Maybe you start writing code yourself, and when you get stuck with an actual problem, ask?

    Re: Find Cell Value In PDF File

    Like I said, I don't have Acrobat or Acrobat Reader installed, so unfortunately I can't take a look any deeper. The registry mess the reader creates was fun enough to clean the first time around.

    Take a look at

    That targets older versions of the typelib (4.0, 5.0, the current version I think is 8.0) and the AcroAVDoc class seems to be named CAcroAVDoc.

    In general though, Google is your friend.