YTL Excel #116: Edit Recorded Macro

Hi, I have recorded a macro attached:-
How do I edit it so that I can click on any cell and it will use that cell and its contents?

Any help is very much appreciated.

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.

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.

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

There are a number of excel documents on my computer which when i open I cannot edit the macro's i have created.

ANy suggestions? I have unlocked all workbooks/worksheets, as i thought this might have something to do with it.

Thanks very much!!

I have created an Excel macro which is used to format data imported from a text file. The macro also contains formulas that are copied down. When I try to run the macro on a range that contains more or less records than the original file used to create the macro, I am getting errors. When I run the macro, how do I tell Excel to copy the formulas down to the last cell, whether or not it is more or less records than the original file?

Please help!!!!

Hi all,

Like the title says, I have no experience with using VBA to create Excel forms, not even sure on the correct terminology for what I'm trying to do. Basically, I work in a psychology lab, and we use an eyetracker to collect data. The software that runs the eyetracker comes with specially formatted excel sheets that allow for the export of data from the program in an easily readable layout. You select the cases you want and then run calculations on them. In the image below you can see the field for selecting the cases (AOIs) and then the button to run the calculations (show statistics).

What I want to change is the way that the AOIs are selected. Right now I can't drag select or use shift to select a range of AOIs. I have to click once on each entry. So, in the below image I had to click on each entry from 49-58 to select them. Can anyone tell me a way to change the behaviour of that field so that I could drag-select or use shift? If its not something that can be easily described, would anyone be able to edit the sheet for me relatively easily?

Talk to you soon,
Daniel