Posts by Iridium

Try using the macro recorder to generate the necessary code. I tried it and got the following (not using your example exactly):

With ActiveSheet.PageSetup
     .LeftFooter = "&""Arial Black,Bold""&10hello"
End With

HTH

Seeing as this board doesn't get too much traffic could I direct you all to this thread please:

http://www.ozgrid.com/forum/viewthread.php?tid=9516

Not sure I completely understand but I think the LARGE function might be of some use - check out the xl help.

HTH

The newly added sheet will then be active so perhaps try something like this:

Sub Test()
'
Worksheets.Add ' Add a worksheet
ActiveSheet.Name = "test" ' Name a worksheet.


End Sub

HTH

I don't think there is a method to programatically unprotect a VBA project as if there was the security would be a bit pointless wouldn't it?

For some general protection FAQ check out http://j-walk.com/ss/excel/faqs/protectionFAQ.htm

HTH

Haven't tested this btw but give it a go:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range


    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


       [a1].Value = "Cell " & Target.Address & " has changed on" & Format(Now, "dd/mm/yy hh:mm:ss")
       
    End If
End Sub

HTH

Try using the macro recorder to get the basic code you need - post back if it needs modifying...

Go to Tools>Options-Transition tab and modify the 'Save Excel files as:' option

(Using xl2k2)

HTH

Just as an aside there's a macro on Chip Pearson's site at http://www.cpearson.com/excel/pivots.htm which you could modify to use an event to get your answer

HTH

Check out Rob Bovey's XY Chart Labeller addin

HTH

I'm no guru - the macro was filched from the microsoft knowledge base!

If you mean how do you run the macro then whilst in xl hit Alt+F11. On the left hand side you should see the project explorer window (if not hit Ctrl+R). You should see your workbook listed as VBAProject (yourworkbook name) - right-click on it and choose insert module. Paste the code into this. If you flip back to xl the macros can be run from Tools>Macro>Macros

That what you mean?

Not sure but try this macro:

Sub Reset_LastCell()
' http://support.microsoft.com/default.aspx?scid=kb;en-us;244435&Product=xlw2K
   ' Save the lastcell and start there.
   Set lastcell = Cells.SpecialCells(xlLastCell)
   ' Set the rowstep and column steps so that it can move toward
   ' cell A1.
   rowstep = -1
   colstep = -1
   ' Loop while it can still move.
   While (rowstep + colstep <> 0) And (lastcell.Address <> "$A$1")
      ' Test to see if the current column has any data in any
      ' cells.
      If Application _
            .CountA(Range(Cells(1, lastcell.Column), lastcell)) _
            &gt; 0 Then colstep = 0  'If data then stop the stepping
         ' Test to see if the current row has any data in any cells.
         ' If data exists, stop row stepping.
         If Application _
               .CountA(Range(Cells(lastcell.Row, 1), lastcell)) _
               &gt; 0 Then rowstep = 0
            ' Move the lastcell pointer to a new location.
            Set lastcell = lastcell.Offset(rowstep, colstep)
            ' Update the status bar with the new "actual" last cell
            ' location.
            Application.StatusBar = "Lastcell: " & lastcell.Address
   Wend
   ' Clear and delete the "unused" columns.
   With Range(Cells(1, lastcell.Column + 1), "IV65536")
      Application.StatusBar = "Deleting column range: " & _
         .Address
      .Clear
      .Delete
   End With
   ' Clear and delete the "unused" rows.
   With Rows(lastcell.Row + 1 & ":65536")
      Application.StatusBar = "Deleting Row Range: " & _
         .Address
      .Clear
      .Delete
   End With
   ' Select cell A1.
   Range("a1").Select
   ' Reset the status bar to the Microsoft Excel default.
   Application.StatusBar = False
End Sub

HTH

I'm talking out of my arse! You're right that you can't change the formatting! oops!

Erm... What formats do you want to apply? Just wondering if there's something you could do with a macro using the unprotect/reprotect method perhaps?

Quote

Originally posted by BigWorm
Hi Guys

I have an Excel sheet with a column of numbers, the cost of my items. example rows A1 to A400 have the value of my stock in them, I would like to add the numbers together, and come out with a total value of my stock.

Big Worm

Might have missed your point (brain not working) but do you need the SUM function???

Edit: like A401=SUM(A1:A400) ?

Just re-read your original post - you know that you can format individual cells as unlocked (Format>Cells... Protection tab, unchecking the locked option) before you protect the sheet? Ergo keep your cells with formulae in locked and format the cells you want to be able to change as unlocked.

That what you mean?

What version of xl are you using? In xl2k2 there are a few options that would allow you to protect the sheet but still alow various formatting options

HTH

Any use?

MS KB: XL2000: You receive the error message "file not found" when you open a workbook that contains automatic links

???

This what you mean? From the (xl2k2) help file:

Control the startup prompt for updating links
Don't ask whether to update links when I open any workbook, and update links automatically

This option is for the current user only, and affects every workbook opened. Other users of the workbook are not affected. This option also affects links to other programs.

On the Tools menu, click Options, and then click the Edit tab.
Clear the Ask to update automatic links check box. If the box is cleared, the links are automatically updated, and no prompt is displayed.
Don't ask to update links for this workbook, and let me control whether links are updated

Warning This option affects all users of the workbook. If you choose to not update links, and not to prompt, users of the workbook will not know the data is out of date.

On Edit menu, click Links.
Click Startup Prompt.
Select the option you want.
Note You will still be notified if there are any broken links.

HTH

Check out ChtFrmla.zip from Stephen Bullen's page using UDFs

Hopefully relevant...

HTH

If you mean format range A#:Q# then try conditional format in A1 (for example) of:

Formula is: =$A$1="AAAAA"

and copy the formats to the necessary range

HTH