Posts by Batman

    Hi gsbax,


    1) In your attached example, how do you relate Location1 ... Location23 on Sheet2 to the entries in the Loc column of Sheet1 (513, 514)? Or are they not related?


    2) Do you want the results to go into the Actual column in Sheet2?


    3) Is the Trainee column to be populated? If so, how do you identify a trainee and what totals do you want in there?


    Regards,
    Batman.

    Paul,


    I tried any number of ways yesterday in Excel 97 to trigger the Worksheet_Change event with the drop down validation box, including linking the cell changed to another cell, using Rand() and creating a volatile UDF, but none of them worked.

    I believe you will need to use some form of key (e.g. tab or arrow) to allow Excel to recognise that you have finished keying into the cell. The worksheet_change event will not occur until Excel has recognised this change.

    Hello again lilocowboy,


    Following on from my comments on your earlier thread, your current code will check and take action on the contents of several different cells every time you update a single cell in your worksheet. I believe you only really want it to check which cell has been updated and take the action appropriate to that cell.


    I'm confused about what value you are testing for before unlocking the cells as your code will unlock the specified cells if the cell you check is empty, but your comment says you want to reselect the cell checked if the value is not = 1 (in which case you presumably don't want to unlock the other cells).


    Try using the code:



    I hope this comes somewhere close to what you are looking for.


    Regards,
    Batman.

    Assuming that there is only 1 pivot table in the sheet, the code below will display the address of the first cell to the right of the first row of data:



    Hope this helps.


    Regards,
    Batman.

    Hi RFMC,


    Welcome to the forum.


    The following is a simple process that reads a text file containing only the next number in a sequence. The program opens the file and copies the number into cell A1 of sheet 'Sheet1'. The number is then incremented by 1 and the text file updated.


    You will need to create the text file before you first run this. The code needs to be placed into the ThisWorkbook code module and the workbook saved as a template. The invoice number will be automatically entered whenever you create a new workbook from the template.



    This may well need a bit of refinement, but I hope it is along the lines of what you are looking for.


    Regards,
    Batman.

    Jimmy,


    The code below integrates your current macro to check the NI number with a new one to archive a record.


    The code assumes an archive workbook with a path and name of "C:\Jimmy\Archive.xls" and an archive table sheet of "Archive Data". You will need to change the program (once only for each) to reflect your personalised names.


    Replace the current code with the new code below:



    I hope this is getting close to what you are looking for. You will need to place a copy of the code into the worksheet code module (right-click sheet tab, select View Code) for any worksheet that contains data to be archived in this way.


    Regards,
    Batman.

    Jimmy,


    A couple of questions:


    1) Your code at the moment only allows for 100 entries in the active accounts sheet (it is checking for an NI number in cells G3:G102). Is it possible that your data may at some stage extend beyond row 102? If it is, then your current program won't work beyond that point.


    2) Rather than moving the data to a different workbook every time you archive a single record, I would suggest that the archive table is held in a separate worksheet in the same workbook as the active accounts table. Then, at the end of the month before you distribute the active accounts, you can simply create a copy of the active accounts sheet for distribution as follows:


    Right-click the active accounts sheet tab;


    Select Move or Copy ...;


    In the To Book dropdown select (new book);


    Tick the box headed 'Create a Copy';


    Click OK.


    This will create a separate workbook containing the active accounts that you can save and distribute as required. Doing this will save any complications of either the archive workbook having to be open at the same time as the active accounts book or the program having to find and open the workbook, write the entry then save and close it every time you archive a single account.


    Are you happy with the program working in this way?


    Regards,
    Batman.

    Ho Grasshoppa,


    Welcome to ozgrid.


    If you place the following program in the worksheet code module (right click the sheet tab and select View Code) of the sheet where you enter data, it will copy cells A to D of the row updated whenever a cell in column D is changed:



    This will only work if the MyBook (or whatever you want to call it) workbook is open, otherwise you would need to add code to check whether the workbook is open, if not open it and then copy the data (and possibly save and close it). If this approach is what you are looking for and you are not sure how to go about this, let me know.


    You don't say how to identify when to copy a range, or how to identify what range to copy, so you may need to make other changes to cope.


    I mention this as I work for an airline and would be quite interested in your offer!


    Regards,
    Batman.

    Assuming that your code is within the statement:


    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    ... code
    End Sub


    as suggested earlier in the thread, this program is automatically called by Excel each time you make a change to any cell in the worksheet. At the same time, Excel passes to the program the address of the cell (or cells) that has been changed. This address is held in the variable Target, which is declared as a Range type variable.


    So, if the cell you have just changed is B4, the variable Target will hold that address, i.e. "$B$4", and you can use this in the program, in this case using the code:


    Code
    If Target.Value = "Name" or Target.Value = "" Then
        ... do actions


    If you really want to, you can ignore the address in Target and code the If statement as


    Code
    If Range("B4").Value = "Name" or Range("B4").Value = "" Then
        ... do actions


    This should work, but only for (in this case) cell B4. When you come to want to check the values entered in the other cells of your worksheet (in the example you posted there several different cells that want to unlock others) you will need to keep adding code to identify each of these cells and the subsequent cells they each want to unlock. You could build code such as:


    Code
    If Range("B4").Value = "Name" or Range("B4").Value = "" Then
        ... code
    End If
    If Range("F4").Value = "Address" Then
        ... code
    End If
    If Range("H4").Value = "Whatever" Then
        ... code
    End If


    but with so many to check this would become cumbersome and would result in unnecessary processing for the program. What I was trying to give you was a method of identifying which cell had changed (by using Target.Value instead of the more specific Range("..").Value) and then, if the cells to unlock are held in the worksheet (e.g. in the cell(s) immediately to the left of the one to change) you can build a small program that would not need to be changed even if you added more data to the worksheet.


    The Offset property can be used to reference the cell before the one changed as in the following example:


    Code
    If Target.Value <> "" Then
        strUnlock = Target.Offset(0, -1).Text


    The variable strUnlock then holds the text contained in the cell to the left of the one changed and can be used to identify which cells to unlock, as in:


    Code
    Range(strUnlock).Locked = False


    I hope this clarifies a few things for you, but please let me know if it is still unclear.


    Regards,
    Batman.

    Hi lilocowboy,


    Your code at the moment is looking for an undeclared variable called B4 which will have a null value. I suspect this is why the code only ever goes into the first part of the If statement. Try changing this to Target.Value and see whether you make any progress.


    Regards,
    Batman.

    Don,


    Apologies, I thought afterwards that it would only work if the controls had been placed in exactly the right order.


    If you pass the control's Name property value as a string to the SetError subroutine, you should be able to reference the control with the variable in the format:


    Dataform.Controls(strVariableName).BackColor = lErrColor


    Alternatively, if you set the Tag property of each control to the control's number (use a small loop program), you can read the control number from the Tag property and use that to reference the control.


    I hope I'm understanding your intentions properly and that these latest suggestions are of some use.


    Regards,
    Batman.

    Alternative approach


    Hi Don,


    Welcome to the forum.


    I don't know of a way to reference a control by name from a variable (that's not to say there isn't one, just that with my limited experience I haven't found one).


    However, if your validation routine is able to trap the control's TabIndex number (e.g. intIndex = me.ActiveControl.TabIndex) you can pass the integer to a subroutine, within which you can use:


    Me.Controls(intIndex).BackColor = iErrColor


    Hope this helps.


    Regards,
    Batman.

    Jimmy,


    The program you have at the moment runs every time you make a change to the main accounts worksheet. It checks firstly that the cell changed is in column G; if it is it validates the format of the entry for a valid National Insurance number format and makes sure it is not a duplicate of an existing entry. If invalid, the entry is cleared and the cell re-selected.


    Whether or not it is wise to incorporate the new program into the existing one depends on when you want the new program to run. To merge the two programs would mean that your archive process would run every time you changed a value in column B to 'N'. If this is what you want, then it should be possible to merge the two. If, however, you just want to run the archive periodically, say just before you send the sheet to the district offices, then I would suggest a different approach.


    If you can let me know when you want the new program to run, I will give you some suggestions on how to set it up.


    Regards,
    Batman.

    Hi lilocowboy,


    Apologies - you need to use the Worksheet_Change event, not the Worksheet_SelectionChange.


    With the number of cells you want to use to unlock other cells, we need to find a way of identifying the cells to unlock for each selection. One suggestion would be to use the cell before the one you test to hold a list of the cells it unlocks. For example, if the user enters to cell B4, in cell A4 enter D4,F4,H4 with no quotes or = sign to unlock these 3 cells. You can either hide this column or set the font colour to white so you don't see it.


    Clear down any code in the worksheet module (right-click sheet tab and select View Code) and enter the following:



    If you want to test the contents of the cell changed, you could use another hidden column containing the name to test against.


    My main problem at the moment is that the FormulaHidden property doesn't seem to do anything, even if I set the cell contents to be a formula, lock the cell and protect the sheet - it is still visible. If it doesn't work for you either, we may need to raise another thread to sort that out.


    Let me know how you get on.


    Regards,
    Batman.

    Jimmy,


    I have made a number of basic assumptions:


    1) You will run the macro from a button in the main accounts worksheet (or at least when that sheet is active);
    2) The name of the workbook to copy to is constant, and is hard-coded into the program (book = "Trf Test", sheet = "Test");
    3) The workbook to copy to is open;
    4) The main workbook has column headings in row 2 (to identify the number of columns to copy);



    I hope this helps.


    Regards,
    Batman.

    Instead of creating a new worksheet to hold the data, try opening the file then moving it to your workbook. I don't have an HTML file to test with, but it works OK with an ordinary Excel workbook with 1 worksheet.



    I hope this helps.


    Regards,
    Batman.

    Changes for variable numbers per shift


    Hi Rod,


    The changes you need to make are:


    1) In cells C2:O2 of the Preferences sheet, enter the values you want for each shift. Apologies if this overwrites a heading; we can overcome this later if necessary.


    2) Just below Option Explicit, insert:
    Dim rngAvailable as Range, intMaxShifts as Integer


    3) Below 'Set rngShifts ...' enter
    Set rngAvailable = Worksheets("Preferences").Range("B2:O2")


    4) Below 'rngRota.ClearContents' enter
    intMaxShifts = WorksheetFunction.Sum _
    (Range(rngAvailable(1, 1), rngAvailable(1, 14)))


    5) Replace the single line 'If aintShiftTotals(shift) < ...' with
    If aintShiftTotals(shift) < rngAvailable(1, shift) and boolOKToWork Then


    6) Replace the line 'If intNumShifts = 140 ...' with
    If intNumShifts = intMaxShifts Then Exit For


    This should allow you to specify any number you like for each shift.


    I suspect this thread has now gone beyond being of general interest to the forum, so if you get any further problems, or would like any more changes, it may be better if you send me a private e-mail.


    Regards,
    Batman.