Posts by gmccreedy

Re: Protecting Cells In Shared Workbook

just realized this is no good. will never let you select a cell. let me think about it for a little bit.

I am sure someone else here has an idea though...

Re: Protecting Cells In Shared Workbook

you can simply add a workbook change event.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Cells.Select
    Selection.Font.ColorIndex = 0
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

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim ws as worksheet
    For Each ws In ActiveWorkbook.Sheets
    Cells.Select
    Selection.Font.ColorIndex = 0
    Next ws
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.

target = Cell.Address
    Cell.Formula = "=INDIRECT(CONCATENATE(" & Range("BackEnd!E15") _
        .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.

Re: Indirect Vba Formula

it hangs up on "Backend" in the formula.

Thanks for the quick reply

Edit: Ficed issue by adding " "

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...

Option Explicit


Sub formulaset()
Dim Cell As Range
Dim target As String
For Each Cell In Range("b4:al132")
Application.ScreenUpdating = False
target = Cell.Address


    Cell.FormulaR1C1 = "=INDIRECT(CONCATENATE(BackEnd!E15,""!"",target))"


Application.ScreenUpdating = True


Next Cell
End Sub

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

=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: Unhide Sheet Based On Cell Value Contents

Thank you so much!!! I was so close...had small typo in the If statement...thanks again!!!

Re: Unhide Sheet Based On Cell Value Contents

ok...my example was a little generic, I apologize. Is there a way to make this a loop as opposed to a series of "Case" statements?

I actually have about 30 sheets.

I have a series of sheets hidden in a workbook. (example sheets named a, b and c).

I want to be able to unhide a sheet based on a Cell "A1" entry on sheet D

So if A is entered, sheet A would unhide, and B and C would remain hidden.

Re: Link Cell From 1 Sheet to Another: Pull Data Based On Time

absolutly...just have to set the date as a variable somewhere on the table chart.

Do you have a sheet set-up already with the 31 other sheets?

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

FileFormat:= xlExcel9795 ' Microsoft Excel 97
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)...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ActiveWorkbook.SaveAs Filename:="P:\Excel-Files\Book1.xls", FileFormat:= _
        xlExcel5, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
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: Checking To See If A Workbook Is Open

look at the related links (similar threads) above....

Re: Macro Problem

what row is the header?

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.

Private Sub CommandButton1_Click()
     ' start
     
    Dim hld(80, 10)
     
    Workbooks.Open Filename:= _
    "K:\quotes.xls", ReadOnly:=True
     
    For i = 1 To 10
        For j = 1 To 80
            If IsError(Worksheets("sheet1").Cells(j, i)) Then
            Worksheets("sheet1").Cells(j, i) = 0
            Else
            hld(j, i) = Worksheets("sheet1").Cells(j, i)
            '
            Worksheets("straddles").Cells(j, i) = hld(j, i)
        Next j
    Next i
    ThisWorkbook.Saved = False
    ActiveWindow.Close False
End Sub

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

Re: Reading Cell Values That Dont Exist

not sure if that will work...you are taking it out of the loops when calling into the error statement. You either need to return it somehow...or put the goto location back into the loop.

Re: Reading Cell Values That Dont Exist

how about...

on error goto novalue
' do something
novalue:
cell=0

this assumes that the variable "cell" has meaning...