Posts by vwankerl

    Re: Attempting to use dependent checkboxes to autofilter data

    That is a good start! If you can experiment with the code i sent you, you might find that if you select omit cancelled and leave complete un checked, you would see all including complete but not cancelled (and of course vise versa for complete). If you want to see everything then leave omit and show unchecked for both cancelled and complete. If you only want to see cancelled then click show for cancelled (and vice versa for complete). I am not sure you need to add the Include button to accomplish the task.

    I added some comments to the AutoFilter setup lines above. They cover pretty much every option that makes logical sense. Try the various options with the example I sent and see if they don't do the job. I am signing off for the night.

    Re: Attempting to use dependent checkboxes to autofilter data

    I am sorry about not mentioning about the Ok button. The code for the Ok button is the code labeled CommandButton1_Click. Do you know how to work with the Properties of the UserFrom and its Controls? There are lots of things you can do just by managing those properties. Lots of tutorials that cover UserForm programming available here on OzGrid and elsewhere and almost every book about VBA or Visual Basic has at least one chapter on UserForms. If you need more help just ask though.

    Re: Attempting to use dependent checkboxes to autofilter data

    To do the OptionButtons in a Frame, put a Frame on the UserForm canvas - make it bigger than necessary and make it pretty later. Now place two OptionButtons into the Frame. They will become linked together automatically by putting them into the Frame. You can change the properties of each separately but they will remain linked. I hope that helps.

    Re: Attempting to use dependent checkboxes to autofilter data

    One other thing, the reason your idea is not working quite right is that you are calling the macros too early in the option selection process. You have to delay the macro call until the user has an opportunity to make all the desired selections. That is why I suggested that you use OptionButtons in a Frame instead of Checkboxes since OptionButtons (sometimes called RadioButtons) allow one or the other but not both and the combined value can be accessed from the Frame. It is also why you need the Ok commandbutton to signal when the selection process is done so you can perform the macro. That is why Excel 2003 AutoFilter forces you to use (Custom) and displays a dialog box to set up.

    Re: Attempting to use dependent checkboxes to autofilter data

    In reference to my previous, this allows and of the various combinations of the Show/Omit selections and attempts to setup the AutoFilter accordingly. if the logic is not accurate, you can make changes as necessary. Note the two last AutoFilter statements (the longest ones that contain 2 criteria) are of opposite Operators. That is so the instance of "=" (Show) for both Cancelled and Complete will show only those but any time the "<>" (Omit) is specified it works differently (Boolean logic raising its head).

    Re: Attempting to use dependent checkboxes to autofilter data

    I would recommend that instead of using the CheckBox control you used two OptionButton controls (one for Show and one for Omit) for each selection and put them in a Frame (two OptionButtons per Frame), add an Ok button to signal when you are done making your selections, and have some code like this:

    And of course, you need some code in a Module to Show the form.

    Re: VBA to click on active row and have it appear in Userform

    This looks like an interesting challenge. I will play around with it and let you know what I come up with. One question, You say there are 16 columns and 16 textboxes. Which textbox is for the column labeled "Case #"? I may have other questions, but I think I understand what you want to do.

    Re: Is it possible to include Multiple Delimiters in Workbook.Open Function?

    Based on the VDE Help in Excel 2003 and Excel 2007 the Workbooks.Open method parameter "Delimiter" only uses the first character in the string passed to it. If you have to open multiple text files with different delimiters you will need to use VBA to query the user for the files and associated delimiters. Done right, the amount of user interaction could be minimal but it will still require some programming.

    Re: textbox option button combo box

    After looking over your question and the attached spreadsheet, my recommendation is that you need to use some tutorials for doing VBA Userforms. Pay particular attention to using the Userform and Controls Properties. It is there that you will find ways to hide controls and make them visible using VBA. All that you want to do is doable and there are lots of people here who could do it for you, but you would probably not learn anything and be back asking for features or options. Get some tutorials (and maybe a couple VBA books, e.g. Professional Excel Development from Amazon - the current bible for VBA development). Lots of help there.

    Re: Rearrange cells by specific criteria

    My first thought was to use a PivotTable also. I am pretty rusty on those so can't help there. If you need to do this with older versions of Excel let us know but you will need to do some VBA probably.

    Re: Create separate files &amp; mail through Outlook

    Here is a code segment from an app I use quite a bit that will, from VBA, build and send an Outlook message with a file attachment. There is another more generic way to send an email from VBA but it does not allow all the features that you have with this method. This is pretty well documented at various places on the internet - search for Excel VBA send email Outlook.

    Here are a couple links: and
    There are many others.

    Note, this segment assumes you have already created a workbook file and have the name and path of this file to use for the attachment.

    Hope it works for you.

    Re: Copy 700 times

    Here is my offering. I am looping through each cell rather than copying blocks of cells. Also, you are not limited to 700. Note that I am also copying the StormId and the lat/long from each row of Help so you can have the reference without having to maintain the Fill to match the other sheets. Let us know how these work for you.

    Re: Code work in Excel 2007 gets error in 2003

    Just a quick reply: There are major differences between 2003 and 2007 when it comes to Sorting. Just look at the differences with the Sort dialogs between the two. Likewise, the Sorting in Macros is very different. If you have a macro that uses sorting in Excel 2003 it might very well work in 2007, but going the other way is not a good idea unless you understand all the differences. You may have to design a different sort process for the 2003 macro. If you are going to support older Excel versions, you really need to have test systems with all the supported versions of Excel so you can figure out the gotcha's between versions.

    Re: Copy 700 times

    I think I understand this question but need a little more information. You say you "need the maximum which stands in column DU of the Help tab to be copied". I note that column DU in the Help tab has a Min function , not a Max function. Are you saying that you need to values that are in column DU of the Help tab to be copied to the Fill tab or do you need a Max function to be performed before coping to the Fill tab?

    I am trying to capture all the ways a user may choose to Copy, Cut and Paste. I currently have hot keys covered, but need a way to capture the Standard Toolbar clicks and the Edit menu clicks. This could also be extended to other functions on the Standard toolbar and menus. I am fairly expert in VBA.