Posts by Michael Simpson

  • Filter rows AND columns in pivot table

    I'll append an example of the sheet layout I have in my pivot table.
    I was asked to help a colleague with this problem.


    Basically, I would like to filter the results so that all rows from column B onward (note that there are approximately 100-150 columns)
    that only contain zeroes are excluded/hidden as well as all rows where all row values from column B are zeroes are excluded/hidden.


    Note that this workbook does not contain any macros, so if the solution required a macro, I could write it, but I'm not 100%
    certain the colleague would accept that sort of change to the workbook (what with the requirement for a trusted location etc)


    In the attached example, I want all the rows in green to be excluded (since all the columns contain zero) as well as all the yellow columns (since all the rows contain zero).


    [ATTACH=CONFIG]71479[/ATTACH]

  • Sort produce unexpected results

    Re: Sort produce unexpected results


    Thanks Kenneth, but I think it's easier than that. It occurred to me on the way home on the tube, that, MAYBE, it's to do with me having a Swedish Windows installation.
    If we ignore the first characters (V's and W's), then the second ones are all sorted correctly. Now Swedish DOES have the character W, but maybe not in MS's world ?


    On the assumption that my idea is correct, is there a way of specifying the sort character string to Excel ?


    Just tried an experiment. Exported the sheet as a csv file, then opened it and sorted it on the relevant column; same results. Strange .....


    After some searching, I found this link http://www.mrexcel.com/forum/e…al-sorting-mixes-v-w.html
    which talks about the same thing. In the end, I followed the suggestion proposed by Gerald Higgins which works, but it seems strange that
    such a workaround should be needed for something so simple.

  • Sort produce unexpected results

    I realize the title will result in people thinking "aha, he hasn't got a clue what he's doing", but let's see if you still think this after I explain my problem.


    One of my sheets contains 3 relevant columns; A is empty, then comes B with international country names, then C with a 2-character international ISO code, then D with a numeric value 1-3 indicating
    whether the country is a low (1) to high (3) risk country (money laundering).


    I have the following sort code in my macro:-


    I have also tried sorting column C MANUALLY using Excel's sort option. In both cases, I'm receiving the following results at the bottom of the list


    Now, whichever way I look at it, VU (Vanatua) SHOULD come before WS (Samoa) and Wallis and Futuna shouldn't come as high up as it does either.


    Can anyone suggest what might be wrong ?

  • Combo Box in user form to select a file from all open files to use in a macro.

    Re: Combo Box in user form to select a file from all open files to use in a macro.


    Not sure if this is another (possible) answer to your problem. I have written a workbook that fires up a userform. This form automatically "fetches" in all xlsx files and creates/populates a combo box with
    their first letter (for example, one option would be the A-E filenames, another F-J etc etc).
    You then select the combo box and a listbox is populated with the files matching the starting letters. You select the files that are of interest, click the OK button and whatever you want
    done can be performed based on the selected files.


    .... or is that overkill for what you want ?

  • Prevent ComboBox Change Event Firing On Save As

    Re: Change combo event for Save as but not for Save


    Sorry Dave, I thought my title was explicit enough.

    Let me phrase it this way. On reviewing the VBA code, I can't see anything that would cause the combobox event to fire programmatically, thereof my confusion. Here is the combobox event code

    All I can say is this. If I choose a combobox option, I get the MSGBOX at the top of the code. If I do a manual Save I don't get the message (ie, I don't run through the combobox event). If I select Save as I get the msgbox.


    Is there some other setting/generalized variable that I'm unaware of ? (And by generalized variable, I mean some "esoteric" Excel workbook variable that I don't know about based on my limited knowledge)

  • Prevent ComboBox Change Event Firing On Save As

    I've programmed my way around this, but I'm curious as to whether anyone can explain what's going on and whether there's a simple setting that might negate the "problem".


    Basically, my worksheet has a combo defined. If I do a Save (manually), the change combo event doesn't kick in. If I do a Save as (again, manually) it does.

  • Change Numeric Constants In Column To Zeros

    Re: Change Numeric Constants In Column To Zeros


    I hope this can be considered a follow-on question to the previous rather than a new topic.


    I want to do the same thing as above, but for a range of cells (B2:M7). I tried the following

    Code
    ActiveSheet.range("B2:M7").SpecialCells(xlCellTypeConstants, xlNumbers) = 0


    but that failed, then I tried


    which worked for column B, but failed with a 1004 on column C.


    What is the correct way of doing this ?

  • Change Numeric Constants In Column To Zeros

    Re: Change Numeric Constants In Column To Zeros


    Thanks for the answer. I had to change the B:B to something like B:X (or similar) since I wanted to change ALL the cells in the sheet (apart from the superfluous rows on the next sheet).


    Any explanation as to why the original code (which I googled somewhere) would have behaved as it did and why your code doesn't. (I assume it has nothing do do with the fact that your code was in its own SUB ?)

  • Change Numeric Constants In Column To Zeros

    I have the following code

    Code
    Call LastCell_Example(LastCell)
  lastrow = Cells(Rows.Count, "B").End(xlUp).row
  Set Datarange = ActiveSheet.Range("B1:" & LastCell)
  ' Set to 0 all cells that contain constants, but ONLY those that are numeric
   Datarange.SpecialCells(xlCellTypeConstants, xlNumbers) = 0


    lastcell_example contains


    When I run the code above, I don't understand the fact that the first rows in the sheet AFTER "Courtage" are also being changed to zero.


    Obviously, I'm not limiting my datarange correctly, but could someone explain what I need to do to limit the changed data to ONLY the current sheet.

  • Updating Distributed VBA Macro Code

    Re: Updating Distributed VBA Macro Code


    Thank you. Unfortunately, it's difficult sometimes to explain exactly what you're trying to do, without getting bogged down in extraneous explanations.


    I've never used addins before, and reading up on them, I got the impression that they were the way to maintain centralized VBA code (that I can change), rather than each user having their own workbook with (possibly) outdated copies of the "same" (centralized) VBA code. (Bit like the fact that as soon as you start Firefox, if it's been updated at mozilla.org, your local code is updated automatically)


    My users' scenario are 2.

    • User runs a non-excel application. This starts excel, passing the name of the workbook and a long string as a parameter as well. The open event detects this string and as a result, runs a function that imports data and populates a sheet (having changed the workbook address to the user's C-drive)
    • User opens the previous (C-drive) workbook. This time, the open workbook event detects no string passed, and does nothing, and simply allows the user to review their data etc etc


    Note that in item 1, the user doesn't have to press any buttons or do anything to get the data passed as the parm into a sheet.


    I want to try and achieve exactly this, but somehow or other have the workbook run external code that is placed and maintained centrally. Ideally (?), when the (C-drive) workbook is called via item 1, it will recognize that it has been passed a string parm and will run VBA code in a workbook on a network drive. As to whether the network drive code is defined as an addin or not, that's beyond my competence.

  • Updating Distributed VBA Macro Code

    Re: Addins


    Thank you both for answering. Let's see if I can answer your suggestions as I understand them. At the moment, imagine my application is started from a DOS window. I pass it the name of the workbook I want to open, as well as a long parameter string. The open event in the workbook takes the long string and runs a function that analyses the string and populates a sheet with data as a result. This is working fine at the moment. My problem is that this workbook is copied to each user's private drive (obviously including any vba code). As a result, if thecentral workbook VBA code is updated, the users never see it (since they're now accessing their own private copy).


    To reply to your suggestions.


    Tony. I don't want the user to have to press any button after the workbook is loaded. Is there a possibility to start excel, pass the name of the workbook as a parm and the name of some function to run ? (This doesn't get me past the updated VBA code though)


    Simon. Yes _ I want the code to run on opening the workbook (which, as mentioned, it's doing).


    Like I say, what happens if the central VBA code is updated though ?

  • Reviewing Multiple Answer Suggestions

    When submitting a new topic, you are shown a list of possible suggestions that might already have answered your question. Running Firefox, this is then opened in a new window, but (for me at least) without the back/forward buttons. If I then review one of the suggestions, I can't see how I'm supposed to return to review another one instead (all I have is the close window button).


    Am I missing something obvious ?

  • Updating Distributed VBA Macro Code

    Addins


    Wasn't sure how to specify a title for this topic and didn't want to second-guess where the error was - thereof the concise title.


    Am trying to get my head round addins and how to use them. The background is as follows. I have a workbook (call it X) on a network drive. When users run an application, this workbook is copied to a drive the user owns (if not already there) and the next available sheet is populated with data via VBA code. The actual code that populates the sheet is quite complicated, so I wanted to be able to update the user's workbook with updated VBA code as required (without them having to re-copy the workbook from the network dive). I read this site's description on addins and got the impression that that was what I was after. I therefore created the following

    • an xla file with ONLY VBA code (including VBA code for the open workbook event)
    • an xls file with ALL VBA code stripped from it


    I then added the xla addin to the xls file on the network drive.


    My problem is that when I then open Excel (empty) , the open workbook code from the addin is run automatically (ie, the X application code is run). If instead I open Excel with parameters using X, it seems that I first run the xla code, and then, once this has run, I get the standard question "Do you want to enable/disable macros" and the application code is run again.


    Long and the short - is there a better way to ensure distributed VBA code can be updated automatically from a central network drive when that VBA code is updated ?

  • Updating Distributed VBA Macro Code

    Assume the follownig:-


    • a centralalised xls file containing VBA code
    • users use this xls by copying it to their own C-drive
    • I find a bug in the VBA code and update the centralised xls file


    I'm wondering if there is a way to update the user's C-drive VBA code with some centralised VBA code when required. I've written code that can check if a file has been changed compared to the last time it was read in, so that shouldn't be a problem, but I'm not sure if updating/replacing VBA code is doable, and if so, what would be the best way to "store" the updated VBA code (in an xls file, in some sort of simple external text file, ....)