Posts by Batman

    Greg,


    Do the values to be sorted on include negatives? If not, one approach, at least for the SortA program, would be to sort in descending order, use Match to find the first zero value row, which should be towards the bottom, then sort the remainder of the table in ascending order.


    Regards,
    Batman.

    Rod,


    No problem. The changes are straightforward, but will take me a while to code and test, and it's time to hit the road for home. I'll post the changes tomorrow.


    Regards,
    Batman.

    You will need to insert the first group of statements (If Len ... End If) between the second (Private Sub ... End Sub). This then becomes your macro that executes every time you select a new cell in that worksheet.


    You will need to replace the "... Do whatever" with the actions you want the macro to carry out when a value has been entered.


    You may well find that you also need to add code to check whether the Target range contains more than 1 cell, and to check whether the Target cell is within the range that should generate an action.


    Let me know if you are still having problems.

    Hi lilocowboy,


    Right-click the sheet tab and select View Code. If you do not see the following outline module already created, type it in:


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    
    
    End Sub


    Whatever macro you write there will be actioned each time a different cell is selected in that worksheet. It will be actioned even if you have only 1 unprotected cell in the sheet and try to tab out of it.


    The address of the cell you have just come from will be held in the range variable Target. You can use the Text or Value properties of Target to find out whether a value has been entered, e.g.:


    Code
    If Len(Target.Value) > 0 Then
        ... Do whatever
    Else
        Application.EnableEvents = False
        Target.Select
        Application.EnableEvents = True
    End If


    Once you have established that there is a value in the cell, you can go to the next cell; the question then is how do you determine which cell to unlock based on the cell that has just been updated. You will need to unprotect the sheet, unprotect the next cell, select the next cell to update and re-protect the sheet.


    In the code above, if there is no value in the Target cell, the code reselects the cell while preventing the macro being invoked for a second time when the cell is selected by using the EnableEvents property.


    Does this give you any pointers to the way forward?


    Regards,
    Batman.

    Hi John,


    Your problem seems to be connected to the fact that what you have changed the entry in the Combo Box to does not exist in the drop down list. This results in a list index value of -1.


    It is probably caused by typing a value into the box's text area rather than selecting an entry from the drop down list using the mouse.


    You may be able to overcome the problem by expanding the CboName_Change macro, inserting an If statement around the main body of the code, e.g.:



    If you type values into the Combo Box selection, you may find that this code will be invoked for each character that you type, so it will be better to use the drop down box to select an entry. If this is not possible, try experimenting with using the code with a different "event" (e.g. BeforeUpdate, AfterUpdate, etc.), but these will occur at different points and may not give you what you want.


    Regards,
    Batman.

    Rod,


    The difficulty of varying the number of shifts available per day depends on whether you want one total per day or, more easily, one total per shift. I would have thought that the latter would be more sensible anyway, as otherwise you might end up with 17 people on the first shift of the day and none on the second.


    As I don't work in an environment where shifts are common, could I get you to clarify the term "swing shift" and how the numbers per day (e.g. Swing 1-6 on Day 1) would be used? Should these numbers also be allocated on a shift basis, or can they only be done per day?


    Regards,
    Batman.

    Many thanks, Derk.


    In which case, I'm still having trouble finding a formula that checks whether all the values in a range are equal to the same numeric value (as opposed to TRUE in rep013's suggestion, which works fine). Whatever I've tried only seems to work on the first value in the range.


    Regards,
    Batman.

    lilocowboy,


    Do you have specific cells that you want protected (e.g. those you enter data into) and others to be unprotected (e.g. the ones with formulas)? If so, highlight the cells you want unprotected, click Format > Cells > Protection and untick the Locked selection. You will need to do this when the sheet is unprotected. Then, when you re-protect the sheet those cells will be available (you can tab between them) and the others will be protected.


    If you only want a cell to become unprotected once another cell has been changed, you will need to do this via a macro. To see where the code will need to be placed, right-click the worksheet tab and select View Code. Click the right-hand drop down box and select Change. This will give the outline for a macro to be actioned each time that worksheet changes.


    Regards,
    Batman.

    If you are looking for a common import process that will determine the machine settings before opening a text file (using the process suggested by Gollem), try storing one or both of the following in variables to use with OpenText:


    strDecimal = Application.International(xlDecimalSeparator)
    strThousand = Application.International(xlThousandsSeparator)


    Hope this helps.


    Regards,
    Batman.

    I'm sure I'm missing something simple here, but I can't see what.


    In cells A1:A3 I have values of 1; in cells B1:B3 I have the array formula =A1=1 (filled down). In cell B4 I have the array formula =(B1:B3)=TRUE [or =(B1:B3=TRUE) - they both seem to work the same].


    If all 3 values in column A = 1, cell B4 returns TRUE; if I change cell A1 to 0, A1 and B4 change to FALSE; however, if I reset A1 to 1 and change either A2 or A3 to 0, B4 stays TRUE.


    Any ideas as to where I have gone wrong?


    Many thanks,
    Batman.

    John,


    Has the code been entered into the code module for the form (can you see the code when you double click on the combo box)?


    Have you seen my previous post about setting the RowSource property?


    Have you entered the value in the combo box by typing it in or selecting from the drop down list?


    When the run time error appears, can you hang the cursor over the various parts of the statement, particularly Cells, CboName and ListIndex. If there is no value in the combo box (or one that does not exist in the drop down list), the ListIndex property may be set at -1, hence the program could be trying to allocate a value from row zero (-1 +1) of the worksheet.


    I'm not sure where you reside, but as it's now 10:45pm here, I won't be able to do much more until tomorrow morning. However, if you update the thread someone else may be able to pick it up in the meantime.


    Regards,
    Batman.

    Hi John,


    Sorry, I forgot to mention that you will need to set the RowSource property of the Combo Box to:


    Sheet1!a1:a3 (or whatever your sheet name is)


    in order to display the drop down list for selection.


    Regards,
    Batman.

    Hi John,


    Assuming that you have the 6 boxes (i.e. Text Boxes) on the same form as the Combo Box, and that the names are in cells A1:A3 of the active sheet and the objectives in B1:G3, put the 6 text boxes in a frame and use the Change event of the combo box to carry out the actions. The following code should get you started if you double-click on the combo box:


    Code
    Private Sub ComboBox1_Change()
        Dim ctrl as Control, indx as Integer
        
        indx = 2
        For each ctrl in Frame1.Controls
            ctrl.Value = Cells(ComboBox1.ListIndex + 1, indx)
            indx = indx + 1
        Next ctrl
    End Sub


    Hope this helps.


    Regards,
    Batman.

    Updated for new functionality


    Rod,


    The code below should allow your employees to enter NA for any shift for which they are not available. It will also make sure there are no blanks in their data, check that all values from 1 to (14 - the number of NAs) are entered, and that they do not work 2 shifts on the same day, either through their selection or by earlier preferences not being available. You should now be able to remove the formulae in the spreadsheet.


    To make the code work, in the Rota worksheet you need to enter in cells B1:O1 an indicator that the shift belongs to the same day, e.g. B1="Day1", C1="Day1", D1="Day2", E1="Day2", etc. It doesn't matter what the text is, as long as the 2 values for the same day are the same and are next to each other.



    I have changed the code in quite a few places, so you would be advised to copy and paste the complete text. I have carried out quite a few tests and it seems to work OK for me, but as always let me know if you have any problems.


    Regards,
    Batman.

    Hi Rod, and thanks for the kind comments.


    There were always likely to be a few complications with this; the reason for the formulae in the worksheet and the shortcuts in the program was to make the size of the code manageable and to be able to give you a reply within a reasonable time.


    Not only is it currently possible for an employee to select and be given 2 shifts on the same day, but they could also be given 2 shifts on the same day if their earlier preferences are not available. Also, I assume that it may be possible for people to be unavailable on certain days (e.g. they are on approved holiday leave, etc.), so you may want to allow them to be able to enter, for example, NA.


    I will try to have a look at a few of these changes later on today.


    Regards,
    Batman.