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
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
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
Display More
but this is the answer I am getting in the first cell of the range...
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
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
Display More
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.