Posts by gijsmo

    Re: SaveAs Multiple Worksheets into Multiple Workbooks

    I don't have a quick VBA solution right now but one way to fix this I think is to edit the links. In Excel 2003, select Edit --> Links on the newly created spreadsheets. Then select the Change Source option and change the source of the links from the Master spreadsheet to the newly created spreadsheet (essentially point the links back to itself). If this is successful, the Edit --> Links option will no longer be available in the new spreadsheet as there are no longer any external links.

    Re: SaveAs Multiple Worksheets into Multiple Workbooks


    I have updated the code to accommodate what you asked, the only thing I didn't understand was the "commission month". However, if it relates to changing the final workbook name from "100", "200", etc to "100-Jun-11", "200-Jun-11", then it won't be hard to see where this needs to be done:

    The code is a bit more complex now as there are additional subs required to perform the task. The main sub CopySheetsToNewWorkbooks is still the one you need to call to perform the work.

    Hopefully, the comments will make the rest self-explanatory! And hopefully, you can see how this can be relatively easily expanded to include "300", "400", etc.

    Re: Inserting missing rows in a column of supposed consecutive numbers

    Ok, making a few assumptions and with little (or no) error checking, try this bit of code:

    Main assumptions made:
    1. The macro code runs in the activeworkbook
    2. The sheet containing the starting numbers is active
    3. Column A contains the rows of starting numbers
    4. All of the numbers are in sequential ascending order (from row 1 to the last row)
    5. Cell "A1" will contain a starting number >= 700000
    6. If Cell "A1" has a starting value > 700000, rows will be inserted above this
    7. Cell "A1" will end up with a number = 700000 at the finish of this routine

    Re: Named range in add-in

    Ah...yes, I see where you are coming from. Sorry, I mistook your original question to be "macro" related.

    I gather from your example that you are trying to have a set of holiday dates located separately (or "hidden") from the active workbook.

    I have done this another way in the past by using a "named array" to hold the dates of public holidays. This is like using a named constant except it is in array format. For example, this might look like:
    ={39392,39441,39442,39448,39475,39517,39528,39531,39563,39608,39756} if you use serial format and I believe you can also use something like:
    ={"1-JAN-2001","31-DEC-2021"} etc
    You define this array (in Excel 2003, Insert --> Name --> Define) exactly as is with the curly brackets.

    Then in your formulas, you can still say:
    assuming you named the named array as "Holidays" of course.

    This is a bit cumbersome because you need to type (or paste) all this in to Excel but it is quite effective for "hiding" and/or centralising these types of values.

    However, if you want to move those holiday dates to a named range in an add-in then I believe the following will work:
    1. Create a new spreadsheet with the holidays somewhere on it.
    2. Create the named range called "Holidays".
    3. Save the spreadsheet as an add-in. Let's call the file "Holidays.xla"
    Refer here if you have not done this before:
    4. You can now reference the Holidays named range in the add-in (a) directly or (b) indirectly:
    4a. If you make the add-in available to Excel (via Tools --> Add-Ins in excel 2003), you change your formula to:
    4b. If you do not make the add-in available, you need to know the exact path of where the add-in was placed on the computer and then the formula looks something like:

    Note that if you go down the option 4b path, you will probably find that Excel will want to update the "external links" each time you open the workbook. If you then make the add-in available, Excel will correct the formula but you may end up with "broken" links.

    Hope this helps!

    Re: Named range in add-in


    Normally when I reference a named range (when the macro code is in the workbook that has the named range) I use something like the following:

    Set rRange = Workbooks([B]ThisWorkbook[/B].Name).Names("MyRange").RefersToRange

    If you move that code to an add-in, I would expect to change that bit of code to:

    Set rRange = Workbooks([B]ActiveWorkbook[/B].Name).Names("MyRange").RefersToRange

    Remembering that the workbook that actually has the named range needs to be active when you call that bit of code via your add-in.

    Re: Using checkboxes to add/remove values to a table

    No problems Tim, glad to help...and China's not that far from Australia!

    Try your hand with a multiselect listbox at some stage instead of multiple checkboxes, the coding will be simpler as you won't have multiple events to contend with (so no more onchange event for each checkbox, just one change event for the listbox). But you'll need to familiarise yourself with how they work.

    So as a final bonus, I have attached a version of the demo that uses a multiselect listbox instead of checkboxes so you can see how this might work. Only the code in the userform has changed.

    Re: Using checkboxes to add/remove values to a table

    I'm sorry Tim, without a better understanding of what you are trying to do, it is a bit difficult to respond to all your questions.

    To answer your last question first:


    Also - if I want to add a new table to the same userform (if the first one was say 'class' the second would be 'block') would I need to basically copy everything in Main and all the cbWind code a second time? Then tie them all (the same way I did with the Class) to the new table?

    The short answer is "No".

    The module called Main contains generic code that can be used by multiple userforms with some small tweaks. In my last example, the range name "TABLE" was essentially hard coded in the Main module. With some small change, the named range can be passed in to the FetchMatching and AddorDeleteData routines. Then each userform that needs to use these routines can pass the appropriate named range in and the named range/s can just be stored as constant/s in the userform.

    A revised version of my demo code with these changes is attached. With this change, you only need to have one copy of the code in module Main (this is the reason for separating this code into a module in the first place). You will see that the named range is now a parameter to the routines in the module Main and the named range "TABLE" is stored as a constant in the userform.

    Then the code that is in the userform in my demo needs to be copied to each userform that needs this functionality.

    I don't fully understand your other question:


    Quick question though - I have a delete button - which basically erases the tbName from the Name list - what code do I need to add to that button to make sure that it erases the name in the Class table under the classes that to which its attached?

    I am not sure what you mean when you say "it erases the name in the class table...". I think you mean that pressing Delete will remove all the instances of that name in each of the columns within the named range in question.

    If so, you need to make sure EnableEvents is true and then set the value of each of the checkboxes to False. As you set each one to False, each of the corresponding checkbox onchange events will be called and will actually perform the deletion of the text in the table.

    A simple way to remember how EnableEvents needs to be set is that if you want to set the value of the checkboxes after you search for matching text, EnableEvents needs to be False. If you want to add or delete data when you select/unselect a checkbox, EnableEvents needs to be True.

    I hope that answers your questions, I am starting another job in the next couple of days so I won't have as much time to look at this Forum for a while.

    Re: Concatenate with leading zeros

    As mikerickson has said, you will need to convert these numbers to Text values. If you want to do this via a macro rather than messing around with inserting columns in your sheet, highlight the column (or part of the column) you want to modify and run the SelectionPadLeft macro below:

    This code will only modify cell values that are numeric to begin with.

    Re: Using checkboxes to add/remove values to a table

    OK Tim, an updated version of the demo is attached.

    This one shifts the cells up rather than deleting the cell as you requested. This will prevent the data below the table from being "pulled up". However, remember that the way this code is written at the moment, the table can grow (ie. the rows can increase) if you keep adding text values to the columns. This has the potential to overwrite data below the table.

    Using a multi-select listbox is an alternative to checkboxes for what I think you are trying to achieve but that is something you will need to try out as I don't know your experience level with listboxes.

    The main thing to pay attention to in the userform in my demo is the use of the EnableEvents variable as this determines what happens in the onchange event for the checkboxes. For example, the btnGo_Click routine updates the values of the checkboxes so EnableEvents is set to false during this process. If there was no EnableEvents variable then the checkbox onchange event would happen as the value of the checkboxes are changed in the btnGo_Click routine - this means data would be added or deleted when in fact you just want to update the value of the checkboxes.

    Everything is else was intentionally written generically enough so that it could be ported to any project.

    I am currently using Excel 2003 at home and have no immediate access to Excel 2007 or 2010 so unfortunately I am unable to open your sample spreadsheet (it gives me conversion errors when I use the Office 2007 compatibility pack).

    Re: Using checkboxes to add/remove values to a table

    Thanks Tim.

    I have treated the "Table" as if they were 4 separate columns with a variable number of rows of data in each column. For the purposes of the demo, the table is on sheet 1 in columns A to D. Row 1 of the table contains "Class 1" to "Class 4" as column headings.

    You can use a named range but there are some complications. Before explaining that, I'll just step through the code in a bit of detail so you can see how this works.

    The FetchMatching routine does the work of looking through the table to determine if the text you entered is found in one or more of the columns. It uses the "generic" FindInRange routine to do the actual searching. The FindInRange routine needs (obviously enough) a range to work with. The range in my original demo is determined by working out the last empty row in the column and using row number 2 down to the last non-empty cell as a range to search (row 1 is assumed to be a header). You can fairly easily use a named range here.

    The AddorDeleteData routine does the work of adding or deleting the cells that match the test string you typed in on the userform. Right now, it works in a relatively straightforward fashion, finding the last row as explained above. If you want this to use a named range you need to be aware that the overall length of the named range (ie, the number of rows) may grow or shrink when you add or delete a cell in one of the columns. So the named range needs to be adjusted accordingly, this is where it gets a little complicated.

    That's a whole lot of explaining but won't get you much further if you are not familiar with VBA. So I have attached another demo using a named range called TABLE. For the purposes of the demo, I have moved the data away from the top left corner of the first sheet so you can see that the code will work no matter where the table is (ie, the code works purely on the named range now).

    Again, not knowing exactly what you are trying to achieve, my code makes many assumptions. Specifically, it still assumes that there are 4 separate columns with a variable number of rows of data but the all the data is bound by the named range eg, in my case the named range is B3:E10 because column C has the most rows including data in row 10 (the other columns don't).

    Of course, the other thing to remember is that any data outside of the named range (even if it's in the one of the columns) will be ignored as the code only looks at the named range.

    Re: Using checkboxes to add/remove values to a table


    I remember what being a novice is like with Excel VBA. What you are trying to do is not overly complicated but daunting if your new to VBA.

    There are a number of ways this could be tackled and with the limited information you supplied a number of assumptions would need to made about the data on your sheet.

    However, to get you started, I have attached a demo that I think does something very similar to what you are trying to achieve.

    Take a look and maybe you can adapt the code to suit your own worksheet.

    Re: Non-activex Datepicker Calendar Control

    If your projects are anything like mine, I need the datepicker functionality that was provided by the DTPicker control only on a UserForm (ie, not on any of the worksheets). As this functionality is common to a number of my projects, I thought I'd investigate putting my "calendar code" into an Excel Add-In rather than duplicating the code for each project.

    My first attempt failed as the code in my project couldn't reference the code in the userform in my add-in. After more research, I found an elegant way around this with minimal modification to code in my existing projects. Essentially, each project declares a generic public 'Calendar' object in a module somewhere and that object is set in ThisWorkBook in the Workbook_Open procedure. The trick to remember is not to unload the Calendar object anywhere.

    So, having built the add-in the only additional step is to make a reference to the add-in for each project that requires the date picker functionality. I found a great little article on doing this here:…Functions_in_VBA/630.html

    As you will see in that article, it is not even necessary to have the add-in loaded in Excel, you just need to add a reference to it for each project that requires it. You can do that by browsing to the .xla file in the VBE (Tools --> References --> Browse).

    The project file and add-in can be placed in the same folder or the add-in can be copied to the folder where your other add-ins reside. Read the note in that article above called About Distributing Your Files.

    My calendar add-in is attached as is a demo file that shows you how you can call the functions in the add-in on a UserForm. Remember that you will need to set the reference to the add-in in the demo code to link it to the location where you placed the add-in.

    I am in the process of testing this on an application that has many UserForms and multiple instances of a calendar field on some of those UserForms and so far this is successful as long as the Calendar object is not unloaded or 'reset' in any way.

    Re: Copying selected rows from 2 sheets on a main third sheet

    This is similar to another post:

    and this is yet another use for the FindAll function:

    So modifying the code slightly from my previous example should give you the result you are looking for.

    I have attached a sample file with code so you can see how this works. The sample has a variable number of entries with the same key on sheet 3 but the code will also work if the number of entries on sheet 3 is constant as it is matched based on the ID value in column A.

    Re: Fill drop down list with all occurrences matching user input


    Based on the information you have provided, this is yet another use for the FindAll function:

    Using that and a bit of code, you can populate a ListBox in a Userform by pasting the results of the FindAll call into an array.

    A demo of using this method is provided in the attached sample. Of course, there is no real validation and the results are just displayed in a ListBox but this should get you started.

    Re: Creating a popup populated by a partial search

    Take a look at the FindAll function here:

    Incorporating that with a UserForm containing a listbox that holds the results when there are multiple possible matches should answer your questions.

    I have done a mock-up of this using your code in the attached file. One thing you will notice is that the CheckCAS function has had to be heavily modified to allow partial string matches. You may need to consider if you need the original functionality back and/or write two separate functions.

    Re: SaveAs Multiple Worksheets into Multiple Workbooks

    I am not sure where (or when) exactly you want to invoke the code to copy these "groups" of worksheets into new workbooks but the following code should at least point you in the right direction of how to achieve this:

    One option would be to place this code in the Workbook_BeforeClose or Workbook_BeforeSave routines.

    Also, if you wanted to move rather than copy the sheets, you would need to change the wSheet.Copy to a wSheet.Move (I am assuming you don't want to move the "Data" sheet, just copy it).