Posts by black knight

Re: Count Cells By Number & Add Adjacent Cell If Number Is X

i changed the way i worked it using that code. I didnt know it before you posted! thanks again!

Re: Count Cells By Number & Add Adjacent Cell If Number Is X

Thanks dave,

=SUMPRODUCT(($B$1:$B$10=3)*($A$1:$A$10))

this did the trick!

ill remember =sumproduct now!

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! help!

thanks!

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.

Re: Show Message Box If File Already Open

Thanks parsnip ill try em out

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.

thanks.

Re: Run Macro From Worksheet Event

sorry dave, that doesnt work. does it need to be a class macro or something. i get error 1004 (basically it cant find it in the application list)

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

sorry dave, the code had a space in it that you posted, i just noticed ;P

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?

Private Sub worksheet_activate()
     
    Dim fso As Object
    Dim fsoFile As Object
    Dim strFile As String
    Dim strDte As Date
     
    strFile = ThisWorkbook.Path & "\" & ThisWorkbook.Name
     
    Set fso = CreateObject("Scripting.FileSystemObject")
     
    On Error Resume Next
    strDte = CDate(fsoFile.DateLastModified)
    Select Case Err
    Case 1004
         MsgBox "File Has Not Been Modified..."
        GoTo existsub
    End Select
     
    Select Case strDte
    Case Is <= DateAdd("d", -7, Date)
        Range("RANGE").ClearContents
    End Select
     
existsub:
    Set fso = Nothing: Set fsoFile = Nothing
     
End Sub

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?

Private Sub Worksheet_Activate()


if application.worksheet.lastsaved = msolastweek then


range("RANGE").clearcontents


end if
end sub

Re: Unlock Blank/Empty Cells

seemed so, i am going to unmerge them all and hope for the best. cheers !

Re: Unlock Blank/Empty Cells

sorry dave just read the post!

I can't it would affect my worksheet too much!

Re: Unlock Blank/Empty Cells

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

Sub Unlock_Cells()


    Dim test As Range
     
    For Each test In Range("cellrange")
            If Len(test.Value) > 0 Then
        UnlockCells test.MergeArea, Range("cellmatch")
End If
Next
End Sub


Sub UnlockCells(cellrange As Range, cellmatchAs Range)
    With Application.WorksheetFunction
        cellrange.Locked = (.CountIf(cellrange, cellmatch) = .Count(cellrange))
    End With
End Sub

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

.MergeArea

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 "

.locked

?"

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:

Sub test_Click() 
     
    Dim rngTemp As  Range 
     
    For Each rngTemp In Range("grey").Cells 
        If len(rngtemp.value) > 0 Then 
            LockMatchedCells rngTemp, Range("against") 
            endif 
        Next 
         
    End Sub

i hope that can help