Posts by black knight

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi, i need to:

    Create some sort of formula combination or macro that will:

    Recognise a cell with a value of 1, 2 or 3 in. If 3 is in the cell, the cell to its left will be counted and added to a total. If the cell that has 3 in changes the value is removed from the total.

    Ive tried lots of methods but i cant figure this one out! :S help!


    Re: Allow Drop-down Box Selection Linked To Locked Cell

    Hi dave, i have office 2000 SP3 installed also, and ive tested it with the 2000 excel and 2003 excel. 2000 has far less features so im guessing one of them is required for the data validation list to be locked. Microsoft obviously didnt see this problem before office 2003.

    Back to my original question, any way of coding this instead?

    many thanks, Black knight.

    We have Microsoft Excel 2000 and i have locked all worksheets separately. I have enabled Data Validation on a range of cells and enabled a list box.

    The way the workbook works is you log in with a name, the cell range references the names in each cell to another cell which was your login name. Those that match, after clicking a button will unlock those specified cells.

    Then users can use the data validation listbox function to select only their name from the cell that was linked to the log in name box. (the users name that logged in only shows in the list)

    The problem is, all cells by default are locked in the range with the names in. You cannot delete the values in any other way, unless you unlock the cells. (at least thats what is supposed to happen)

    In Excel 2003 if you try to choose your name from the drop-down box over a cell that already has a name in or a name different to your own, excel will deny the change and say that the cell is locked.

    In Excel 2000 however, if you try to choose your name from the drop-down box over a cell that already has a name in or a name different to your own, excel will allow the change. Even though the cell is locked for editing.

    My question is:

    Is there any code anybody knows that i can use to make excel copy what excel 2003 does, im sure that the problem is a fault with excel 2000 and microsoft fixed it when they released excel 2003.

    Thanks for your help.

    Hi, i need to know some code that will bring up a Form when the workbook is opened. The problem is it is used by many users on the network. it needs to be able to run the code when it starts up and detect if the workbook has already been opened.


    hi. is there a way of calling a module to run from within the coding section of a worksheet. For example, i have some code to run which is added in to a worksheet. The code set exceeds the 64kb barrier so i have split it into separate modules. i need to add code to a worksheet to run each module separately when activated. is this possible? something like:

    run module1

    was what i thought i had to use?

    Re: Determine Last Saved Date Of Workbook

    hey guys, thanks for your posts! i only managed to get zimitry's coding to work when i removed the

    Set fsoFile = fs.GetFile(strFile)

    code. With the code i removed, it returned an error of "424" object required. Unfortunately dave your code didnt seem to work, it had a run-time error "5" - Invalid call or procedure. any ideas why?

    Hi. I am looking for a macro that will run on worksheet activation. it needs to run only if the file had been saved from the previous week.

    would something like this work?

    Re: Unlock Blank/Empty Cells

    this code will unlock a range of cells that matches a cell:

    This code can also apply the locked cell option to MERGED cells. Can this be modified to only apply to BLANK cells? or doesnt anybody know? Thanks.

    Re: Unlock Blank/Empty Cells

    using the special cells feature i have come up with this:

    Sub test_click()
        Range("grey").SpecialCells _
        (xlCellTypeBlanks).Locked = False
    End Sub

    how would i add merged cells to this?

    something like this:???

    Sub nnn()
        Range("grey").SpecialCells _
        (xlCellTypeBlanks).MergedArea.Locked = False
    End Sub

    Re: Unlock Blank/Empty Cells

    i tried

    .MergeArea.Locked = False

    and it doesnt seem to leave MERGED cells that have data in LOCKED like it is supposed to. it still works the same as before by unlocking empty cells. i have yet to try the "SpecialCells" feature but i am looking into it now. Thanks for your help so far guys, much appreciated.

    BTW, to make this clear; all we need to do is keep the MERGED cells with DATA IN locked and the empty CELLS & MERGED cells unlocked.

    Re: Unlock Blank Or Empty Cells

    Thanks, that works exactly how i want it to, however only on single cells. is it possible to make it do the same to merged cells. in the other code i have i have used


    thanks for all of your help so far, it is much appreciated!

    Re: Unlock Blank Or Empty Cells

    for some reason it does the same as the code i already have, it unlocks cells that are NOT blank. (Cells with text or numbers in). it does not unlock BLANK cells which is annoying. also, is it possible to make it so it effects a "mergearea" instead of cells?

    what if we put something before "



    Hi. Is it possible to unlock Blank or Empty cells within a Range?

    " for example; all cells in the range (A1:F10) > 0 .locked = False "

    anybody know? i have some code from something simmilar i have added below:

    i hope that can help :)