Highline Excel Class 52: Recorded Macro Basics

I have a question and in need of help about VBA. I recorded a macro and it's updating a file. The file is set to automatically read only when a user opens it up. However, when I run my macro to update the spreadsheet, it prompts a messagebox that says do you want to ReadOnly? I want it to automatically click the NO button. Right now when I run the macro i have to manually click No, but I want that to be automatic. Any help? Thanks.

the error reads "code execution has been interrupted".


the code i am using is from a recorded sorting macro that spans 4 ranges of cells, most of the time the button i have assigned the macro to works, other times i get the error above.

a little insight as to what the workbook does, as it may or may not help, i am pulling from a range off of another workbook, a multitude of vlookups and concactnations as well as list validations are used to make a revolving list of rankings, i have a count macro tied to a forward button and as the user clicks the button the list "moves" forward throughout months, another button is used to "autosort" the list into proper rankings. I think it is a really nifty tool where people can gather trends and rankings over the course of the data (2 years)

but anyway, here is the macro i recorded earlier, any idea as to why i would be getting the error?

Code:

Sub AutoSort()
'
' AutoSort Macro
' Macro recorded 8/22/2007 by jl5327
'

'
    Range("L17:L26").Select
    Range("F17:M26").Sort Key1:=Range("L17"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("L31:L33").Select
    Range("F31:M33").Sort Key1:=Range("L31"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("R17:R26").Select
    Range("Q17:S26").Sort Key1:=Range("R17"), Order1:=xlDescending, Header:= _
        xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("R31:R33").Select
    Range("Q31:S33").Sort Key1:=Range("R31"), Order1:=xlDescending, Header:= _
        xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("H2:M2").Select
End Sub

I wouldn't mind the issue, but the problem is that i will have multiple users looking at this work book.

Hello,

I'm creating a Macro with conditional formatting in a Pivot table. I need to highlight the values in the rows that contain the top 20% of the data set. This is an option in Excel 2007 conditional formatting for formating the top or bottom ranked values. The problem is when I record this formatting it only applies to the selected cell or region not the entire pivot table which is an option when you manaully apply the formatting.

Here is the code:

Code:

Selection.FormatConditions.AddTop10
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .TopBottom = xlTop10Top
        .Rank = 20
        .Percent = True
    End With
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions(1).ScopeType = xlDataFieldScope
    Selection.FormatConditions(1).CalcFor = xlAllValues

When you manually create the formatting it allows you select which data in the pivot table you want to apply the formatting to, it appears the recorded formatting does not contain any code around what is to be formatted in the pivot table.

Any help would be great.

Thanks.

I have tried to record & re-record an Excel 2007 macro NUMEROUS times; each time, I receive either a 1003 or 1004 error message. I am trying to create a very complex/multiple sheet report with includes: multiple pivot tables w/filtering/sorting/grouping & charts on each of these pivot tables (approx. 6 pivot tables/charts in total. I am new to macros & only know wnough about VBA to understand on a very BASIC level what SOME of the code means. Can anyone tell me if this is common in 07? I have opened the debugger & read the message about the error, but one didn't make sense (missing a bracket, but this piece had both an opening & a closing bracket) & I have no idea about the others. I am testing the macros on the exact same data set I recorded it from & have tried using both an xlsx & .xlsm file format type. I'm both extremely baffled & frustrated. HELP!!!

Thanks,
Freitag

So... Here I am, trying to record a macro. When I go to clean up the code, I get absolute gibberish. I went back and recorded a simpler macro (running a sort on the range B2:D32) to give you guys this sample code. Note that this is copy-pasta direct from the VBA editor into this window. I have changed nothing.
Code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range& VB_VarUserMemIdB2: D32VB_VarUserMemId '+Select
    ActiveWorkbook+Worksheets&VB_VarUserMemIdSourceVB_VarUserMemId'+Sort+SortFields _
        +Clear
    ActiveWorkbook+Worksheets&VB_VarUserMemIdSourceVB_VarUserMemId'+Sort+SortFields _
        +Add Key .<= Range&VB_VarUserMemIdB3:B32VB_VarUserMemId'VB_VarProcData SortOn . _
        <= xlSortOnValuesVB_VarProcData Order .<= xlAscendingVB_VarProcData DataOption _
        .<= xlSortNormal
    Wend ActiveWorkbook+Worksheets&VB_VarUserMemIdSourceVB_VarUserMemId'+Sort
        +SetRange Range&VB_VarUserMemIdB2:D32VB_VarUserMemId'
        +Header <= xlYes
        +MatchCase <= FALSE
        +Orientation <= xlTopToBottom
        +SortMethod <= xlPinYin
        +Apply
    ElseIf Wend
End Sub

The obvious question is "Where did all those 'VB_VarUserMemId' bits come from?" followed by "Where'd all the + and <= come from?" I know what the code is supposed to look like, and could fix it by hand... but has anybody else seen this behavior?

The only oddball thing I can think of that might cause this is the fact that I had Office 2010 Technical Preview installed on this machine very briefly. It broke a few important spreadsheets, so I reported the bugs, then uninstalled it and went back Office 2007 SP 2. Is there a chance that it mangled some sort of macro- or VBA-related component?

Any thoughts would be appreciated.

HI,

Can any one help me to learn and write about macro in excel?
I dont anything about macro?
But i know how to use formula and other stuffs in excel.
I want to know from the basics of macros from the first step.
Awaiting for the reply from any one.

Sorry to bother everyone with a simple macro problem. I am just beginning to learn the basics and could use some help.

I have my exchange rate located in A1, and a range of figures in B1:B20. I need to return the figures at the current exchange rate in C1:C20.

I know it would be simple to just use a formula but again, I am trying to learn. Thanks to all who can help.

Mike

I have a spreadsheet that shows a date, because of shipping I need to enter a
date and then calculate three months back and enter that date, For example
Cell A1 March, 2004 and I have to enter into cell B1 January 2004, how do I
get Excel to do this automatically.

Please can anyone help me answer any of these questions:

How would you add the contents of the cells from A1 to A10. Give 2 ways
to do this. (hint: Look up "Examples of common formulas" in Excel
help



How do you format a cell to have dollar signs next to the numbers?



How do you add the contents of cells A1 through A10 on sheet 1 and make
the answer show up on sheet 2?



How do you rename a sheet?



Can I delete the sheets that I don't need? If so how?



How do I freeze rows or columns of the sheet so that they don't'
move when you scroll up or down? Example: My name is in Column A, when
I scroll right my mane stays on the screen and column B moves behind A



How do you add up all the contents of column D and make the answer show
up in column C?


Thanks in advance!
abi