Posts by gmccreedy

    Re: Protecting Cells In Shared Workbook


    you can simply add a workbook change event.


    Code
    1. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    2. Cells.Select
    3. Selection.Font.ColorIndex = 0
    4. End Sub


    this will automatically change the font colors back to black in all cells on active sheet.


    Or you can assign it to do it for the entire workbook


    Code
    1. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    2. Dim ws as worksheet
    3. For Each ws In ActiveWorkbook.Sheets
    4. Cells.Select
    5. Selection.Font.ColorIndex = 0
    6. Next ws
    7. End Sub

    Re: Game Codes


    Did you read the notes???


    "You can work in groups of up to three people for this MP"


    maybe you should make some friends....

    Re: Add Indirect Formula Via Macro


    thanks to all for the help. I finally got it...


    Here is the solution.


    Code
    1. target = Cell.Address
    2. Cell.Formula = "=INDIRECT(CONCATENATE(" & Range("BackEnd!E15") _
    3. .Address(, , , True) & ",""!"",""" & target & """))"


    Again, I needed the cell address to be entered as " ".


    Dave, By recording it sets an actual reference to the cell address...so it ends up being a circular reference since the concatenate is trying to use the address as opposed to joining the "name" of an address. Thanks anyway...the recorder is actually what I used to get me started.

    Re: Indirect Vba Formula


    here is the other problem. Where the "Target" is entered in the formula...it has to be in " ". So that it is observed as text for the Concatenate to work. The indirect function activates after reading the completed concatenate.


    I have been looking at this for so long its killing me.

    I need to create a formula for a series of ranges that have a variable sheet name (which is located on sheet Backend!E15) and when it creates the formula will reference the exact same cell on the variable sheet.


    this is what i have so far...



    but this is the answer I am getting in the first cell of the range...


    Code
    1. =INDIRECT(CONCATENATE(BackEnd!'E15',"!",target))


    as you can see I am having trouble getting the target address to lock in. To make things worse, its needs to be in " " so the concatenate creates the corect address link.


    Any help is much appreciated.

    Re: Prevent Save as Version Message


    did you modify the code to suit that I posted above is the first question.


    And yes...the file format should adjust to the version (I think). Like I said, if you play around with the "recorder", it should give you the proper script that you need for this type of process (again assuming you modify the file save location to suit)
    a few versions

    Code
    1. FileFormat:= xlExcel9795 ' Microsoft Excel 97
    2. FileFormat:= xlExcel5 ' Mircosoft Excel 95


    HTH

    Re: Prevent Save as Version Message


    Like Dave said...use force the save into an earlier version (or whatever you like)...


    Code
    1. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    2. ActiveWorkbook.SaveAs Filename:="P:\Excel-Files\Book1.xls", FileFormat:= _
    3. xlExcel5, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    4. , CreateBackup:=False
    5. End Sub


    You can actually record this process with the macro recorder...however...you would probably need to do some adjusting with the file path and name to suit your needs...such as keeping the same file name and path.

    Re: Methodology For Average


    numbers look fine...its doing exactly what you are telling it to do...sum up the row then divide by a user defined number.


    what else are you looking for it to do?

    Re: Link A Cell From Sheet1 To Sheet2 To Pull Data Depending On The Time


    Dave, I helped him out with no macros...just conditional formatting and formulas. But that is definetly a great option here.


    Like i mentioned earlier...the way he was entering his time values was not really an excel format of time.


    emelendez80. BTW. The way it's set up, you shouldn't change any of the time slots on the entry sheet. I had to add a 12 hour increment after noon past since you were only utilizing a 12 hour cycle, but excel doesn't recognize time without the AM/PM designation. So...in order to keep your sheet consistent with the way you set it up, I made it work. (See the hidden row 3 in worksheet.)

    Re: Reading Cell Values That Dont Exist


    how about this...I am not really sure how this code is working, so I haven't tested it.



    I trumped down the two loops to one. It seemed redundant to loop the same variables twice.