Posts by bilnyx

I am using the below code to unloock certain columns based upon a value in cell a1. The value is the previosu month end date. It was working, but now I am getting " run-time error 1004 Unable to set the locked property of the range class"

Can anyone help me to fix the error.

Thanks for the help in advance

Sub UnlockMe() 
    Dim cl As  Range 
    ActiveSheet. Unprotect "password" 
    For Each cl In Range("$B$1:$M$1") 
        If cl = [a1] Then 
            cl.EntireColumn.Locked = False 
            cl.EntireColumn.Interior.ColorIndex = 6 
        Else 
            cl.EntireColumn.Locked = True 
            cl.EntireColumn.Interior.ColorIndex = xlNone 
        End If 
    Next cl 
    ActiveSheet.Protect "password" 
End Sub

Is there anyway to view groups on a worksheet that is protected. I have a worksheet that is locked, and I have columns that are grouped, but the user cannot expand the groups when the worksheet is locked. Is there another way I can keep the worksheet locked and still be able to expand my groups?

Thanks in advance for any help anyone cna provide.

Thanks

Is there anyway to have columns automatically be hidden based upon a cell value? I have a column for each month, and I would only like to show months that have occured, and keep future months hidden from view. I have the current month end date in cell a1 and the following columns each have a date as the header?

Thanks

Is there a way to run a create a button that once clicked will run a macro that will insert a formatted row under the last record on the spreadsheet? The spreadsheet is locked and I would like it to remain locked with the exception of the new row or rows(based upon the number of button clicks) the user will add. I dont want the user to be able to make any changes to the formulas or other rows in the sheet, just be able to add data on the new row.

Thanks

Re: Lock Cell Based Upon Formula

That worked great. I have a follow up question.

Is there some code I can add that will highlight the unlocked column in a color?

Thanks again for the help!

Re: Month End Date Formula

thanks for the help!

Is there a formula in excel that will automatically populate a field with the prior months end date? For example - If my spreadsheet is opened on March 25, I would like the date in cell A1 to read 2/28/07.

Thanks

I am getting #VALUE! when my formula are adding cells that have no value. The cells I am trying to add have formulas in them, but no values matched because I used Vlookups with ISNA to avoid #N/A. Is there an easy fix to rid my spreadsheet of these?

Thanks

Re: If Statements?

worked great! thanks so much for the help!

This is really a wonderful resource for excel questions.

I have another question. Does anyone have a simple fix for the following problem:

I have columns for each month. I need to create another column that will show only current month's expense. It will pull based the date on the spreadsheet. I have attached a sample of what I am looking to do.

Thank you for the help.

Is it possible to lock all cells with the exception of a select few(say a column) based upon a date? I have a spreadsheet where I want to lock all cells with the exception of the current months information. I want the spreadsheet to auto unlock the date that is listed on the top of the spreadsheet. I have columns for all months of the yea and I have the current month listed in cell A1. I want match the A1 cell to the column header to unlock the correct column.

Is there an easy way to do this?

Thanks in advance for any help you can provide.