Posts by Batman

    Although it is obviously working OK now, if the formulae are built using the R1C1 notation, should not the Target.Offset(0, 1) property be FormulaR1C1 instead of Formula, or doesn't this make any difference?


    Regards,
    Batman.

    Hi von,


    As I had already created the code based on your previous request a few hours ago before I left work, I will post it anyway. It assumes that workbook Project requires the password "project" to open it. You will need to change the directory to reflect your own.



    That will allow users to enter their data into 'Personal Planning' and will also open and add the data into 'Project'.


    For your new requirement, can you please attach a sample of what an individual personal file will look like. I need to know how the information is laid out, compared to Project, and where we will get the person's name from.


    If you are not using a form to enter the data, what is going to trigger sending the data to the Project workbook when the user has entered it? Click a button? Automatic as soon as a number is entered in the personal book? Something else?


    Also, if the personal book will contain many entries, how will the macro know which entry, or entries, to copy?


    Sorry to come back with more questions, but I hope we will be able to do what you want.


    Regards,
    Batman.

    Hi von,


    For the time being I will assume that you have lots of 'Personal Planning' workbooks, but that they all contain every person's name and all the projects. However, each workbook will only contain the data that they have entered, i.e. their own.


    The code will check that you are not overwriting any entries in the 'Personal Planning' workbook, but will not do similar checks in the 'Project' workbook as it will assume that no two people are updating the same information.


    To get you started, take a copy of the Project workbook and name it 'Personal planning' if you haven't already done so;


    Open the 'Personal Planning' workbook;


    Go into the Visual Basic Editor;


    Make sure the Project Explorer is displayed (View > Project Explorer);


    Under Microsoft Excel Objects, double click on the ThisWorkbook icon;


    In the code module for ThisWorkbook, enter the following code:


    Code
    Private Sub Workbook_Open()
    
    
        Worksheets("Feuil1").Activate
        UserForm1.Show
    
    
    End Sub


    Save and close the workbook. Now when you open the workbook, the form will be automatically displayed.


    I will work on the code to update the project workbook and get back to you later.


    Regards,
    Batman.

    Hi von,


    1) Is there just one workbook called 'Personal Planning', or will there be many workbooks with the same name - one for each person?


    2) Will the 'Personal Planning' workbook(s) have the same worksheet names as 'Project' and will the data be in the same positions (names, projects, weeks)? I'm looking here to find out whether the coding that populates the Combo Boxes will need to be changed.


    3) When the form has been completed in 'Personal Planning', should the data be stored in both workbooks or just 'Project'? I'm looking to find out whether, if there is only one 'Personal Planning' workbook, I can simply write the data to that workbook and update 'Project' by creating formulas that link to 'Personal Planning'.


    4) Should I assume that the 'Project' workbook will not be open when the user enters data using the form in 'Personal Planning'? I'm looking to find out whether, if I can't use formulas to update 'Project', I need to open the workbook before writing to it.


    Sorry for so many questions, but I need to understand exactly how you want the process to work before deciding how to go about it.


    Regards,
    Batman.

    Hi von,


    I have now made a number of changes to the program so that it


    • requires the entry of project 1
    • ensures that if a project is selected, an entry is made in the appropriate number of days field
    • checks to see whether any entries will overwrite an existing entry and prompt the user for a reply


    The order of the program processing has been changed so that all of the validation is carried out before the updating occurs.


    I have done some testing, but if you find any problems, please let me know.



    I would also suggest that you change the tab order of the boxes (right click the form and select Tab Order) so that the first box selected when the form opens is the week number and the user can tab through the fields in the order in which they should be entered.


    Hope this helps.


    Regards,
    Batman.

    Hi von,


    You will need to remove the code

    Code
    Unload Me
    End


    from all but the last section of your program, as this is closing the form and ending the program.


    I will work on some code to check whether a value already exists in a field before writing to it. At the same time, I will look to add a few other useful checks.


    Regards,
    Batman.

    Be careful if you use Rep013's formula, as this will return a nil value in cases where your cell value referred to falls at the limits of each range, e.g. 9.99 and 10 will both return 0. Try using:


    =IF(AND(A1>=0.01,A1<=0.99),0.3,IF(AND(A1>=1,A1<=9.99),0.35,IF(AND(A1>=10,A1<=24.99),0.6,0)))


    Regards,
    Batman.

    Hi von,


    If your workbook could be used by several people with different settings of the "hide extensions of known file types" option (i.e. the file extension might, or might not, be displayed depending on who is using it) you might encounter problems with the program.


    It would be safer to store the workbook's name in a variable and refer to it using the variable, e.g.



    Also, I assume that your week numbers in cells E4:T4 will change over time. To avoid having to change your program if the numbers are hard-coded, you might want to consider the following code (or something like it) to populate ComboBox2:


    Code
    Dim cell As Variant, rngWeeks As Range, col As Integer
    
    
        col = 5
        Set rngWeeks = Range("E4", Cells(4, Range("E4").End(xlToRight).Column))
        With ComboBox2
            For Each cell In rngWeeks.Cells
               .AddItem cell.Value
            Next cell
        End With


    Hope this helps.


    Regards,
    Batman.

    I don't know whether the code below is of use with Outlook.



    Hope this helps.


    Regards,
    Batman.

    Apologies. That's what comes of testing with data not representative of the original.


    I have also found that the line writing the value to the sheet was incorrect. Replace with:


    Code
    ro.Cells(1).Offset(0, ComboBox2.ListIndex + 3) = TextBox1.Value


    Regards,
    Batman.

    Hi von,


    Check to see whether you can adapt this code for your program.



    Hope this helps.


    Regards,
    Batman.

    Hi von,


    Can we assume that the items in ComboBox2 are populated from column headings in your worksheet, or similar, so that there is a relationship between the column to write to and the ListIndex property of the ComboBox?


    Regards,
    Batman.

    Hi Jimmy,


    I seem to recall from your previous thread that the location of the file you were trying to open appeared to be an intranet site rather than an ordinary file server. My experience doesn't extend to reading files from web sites, but I have been back to the earier thread and copied the address in case someone with relevant experience can help:


    ' \\DFZ22092.link2.gpn.gov.uk\86367512$\My Documents\activity stats\PERF DB'S\Dormant '


    Regards,
    Batman.

    Hi tess457,


    Try using:


    Code
    Application.Dialogs(xlDialogPrintPreview).Show
       ' or
       Application.Dialogs(xlDialogPrint).Show


    Hope this helps.


    Regards,
    Batman.

    Hi robolsson,


    Welcome to the ozgrid forum.


    Assuming you name your cells containing the names as 'NamesRange', in the first date cell in column C (assumed C1) enter the conditional format formula:


    =Match(A1, NamesRange, 0)


    This should set your formatting as required.


    Hope this helps.


    Regards,
    Batman.

    Hi jamap,


    To achieve what you are looking to do, I have added a new row in the 'VB Data Summary' worksheet as row 1. For presentation purposes only, I have set the text colour to white, but the row can be hidden displayed, text normal, whatever. This new row contains in the first column for the relevant type the type name (i.e. Error or Wish). In the subsequent 4 columns are held any categories that are to be included in that column.


    In order to demonstrate the process, I have also inserted extra columns for the categories not currently catered for by your program.


    A new process has been added, that runs at the start of the program, to read all the types and categories into an array (avarDefects) which will have 3 'rows' and as many 'columns' are required. 'Row' 1 contains the Type description, 'Row' 2 the category and 'Row' 3 the relevant column to populate.


    As the program reads a record it passes the details of the Type and Category for that record to a function which finds that combination in the array and returns the appropriate column number.


    As with your current program, if the Type/Category combination is not found, the record is not processed, other than in the grand totals.


    I have had to remove the data worksheet in order to get the file size down below the limit for attaching to this post, so you will need to reinsert that before you can run it.


    There are probably many different ways of approaching this problem, but I hope this one comes somewhere close to what you are looking for.


    Regards,
    Batman.

    Hi jamap,


    Your program determines which column to allocate based on:


    (1) A 'type' value in column 8; and then
    (2) A category value in column 5.


    For a program to be able to relate imported data to columns in a worksheet without any hard coding of column numbers there must be some indication in the worksheet to copy to of how to identify that criteria.


    For instance, if headings in row 2 were to contain the primary values (currently Wish or Error) and row 3 were to contain the secondary values (SW, SP, TP, etc.), the program could read the contents of those rows and determine column numbers accordingly. If you have several categories to be added into the same column, you would need to decide on a structured way of allowing the program to recognise this, e.g. by having the heading as 'SP,TP'. In this way, the program could recognise that there is more than 1 category to allocate and proceed accordingly.


    I don't have details of the layout of your "VB Data Summary" sheet to see whether its layout could currently be used or whether it would need to be adapted for the program to be able to use it.


    I'm still a little bit confused, as the Book1 example you attached earlier contained no data in column 8, so none of your records in that file would have been actioned by your program.


    Whether or not we can achieve what you are looking for will probably depend on whether the headings in your summary workbook can be maintained in such a way as to be able to relate them to the data in your import file.


    Regards,
    Batman.

    Hi jamap,


    Apologies if I have misinterpreted your requirements. Your original posting seemed to imply that you wanted to identify which column to use (e.g. Const swdefects_col = 1) based on a defect type. In your example file, the defect type appeared to be held on column E, so I was checking the contents of that column to identify the type of defect.


    There are many details missing with regard to how your process currently works, so I have assumed that the column numbers are not pre-determined for the various defect types (e.g. not always 1 for SW, 6 for HW, etc.), instead that the column numbers will be allocated by the program each time it is run.


    To hold the defect types, I have created an array (astrDefects) which, for the purposes of this program, is a list of defect types. The array starts off with a single field which is empty. When the program reads a defect type from the file, it checks each field in the array to see if it can find that defect in the array. When the first record is read there are no defects in the array, so the program inserts the first defect type (SW) into the first field of the array and expands it by 1 field (ReDim Preserve ...). So now the array has 2 fields, the first containing SW, the second empty.


    If the second record read also has a defect type of SW, it will find this value in field 1 of the array, so 1 is used in the calculation to determine the output column. If the second record does not have a defect type of SW, the new type will be placed into the second field of the array (and a third, empty field created) and 2 used in the column calculations.


    Your original posting indicated that there were 5 columns per defect type, so to calculate the start column for any defect type the relevant array field number (e.g. 1 for SW) is multiplied by 5 and 4 deducted from the result.


    If it is the case that the columns for specific defect types are determined by your worksheet before importing a file, we can adapt the code I gave you to read all the different types into the array before we start reading the import file. If it is possible for a defect type to be missing, we can allow for that too.


    I hope it is a little clearer. If you can let me know where I have gone wrong, I should be able to change the code accordingly.


    Regards,
    Batman.

    Hi Squire King,


    Good point raised by Roy and, if I were coding this for someone on site here I would probably do it that way. However, although not as failsafe as Roy's approach, copying down the formulas will give your users the ability to change the formulas without the need for program changes.


    The code below will need tweaking to set your default directory that the files will be stored in.


    I have inserted a basic check that assumes all the files will have the same header (currently "Heading") in cell A1. If this is OK, the text will need to be changed for the proper header text; if not, we will need to remove the check from the program.


    I have assumed that you will access the directory containing the files using a predefined drive letter; again, if this is not the case some changes may be necessary.


    The program assumes that it will be run from a macro button located in the worksheet that contains the cumulative data (sheet 'whatever' of myWorkbook). If this is not the case we will need to make some minor changes.


    The number of header rows in the import file is assumed to be 1; it also assumes that you already have data in myWorkbook and that the formulas to copy down are already present.



    When the file has been read, the text "Imported" will be added to the file name, and the program checks that any file name to be imported does not contain that text (which can easily be amended).


    I hope this is along the lines of what you are looking for, but let me know if it's not working in any way.


    Regards,
    Batman.