-2

(FYI: I'm a front-end developer and skilled with Excel formulas and Conditional Formatting, but a beginner when it comes to VBA and Macro design).

The Issue

I'm using Excel 2016 on Windows 10 Home. I originally had a workbook that was created in Excel 2007 (or older). I -- not knowing VBA, but understanding basic programming principles -- inserted a VBA Macro/Script (pretty sure the source called it a macro) into my excel file and everything worked perfectly.

Since 2008 or so I have continuously generated a new version of the spreadsheet as its own file, and the macro has continued to work (if you are curious, it automatically colors odd rows, and adjusts for insertion or deletion of rows) perfectly. However, when I decided to take a shot at editing the macro in Excel 2016 today, I found that I was unable to even find the code! The macro is not listed in the macros list (Alt - F11).

What I've Tried Already

  1. Enabled developer tab.
  2. "Enable all macros" was already enabled.
  3. Clicked "Macros" (same as ALT + F11). No macros listed.
  4. Tried all options in the "Macros In" dropdown. Still no macros listed.
  5. CTRL + R to open Project Properties window. Went through each file and folder listed. None of them opened up any kind of macros dialog.
  6. Check to see if the macro somehow became an "add-in" but the "Add-Ins" button on the ribbon is grayed out.

Conclusion

I'm not certain how to proceed, Google has been fruitless, and Stack Overflow doesn't seem to have the answer to this either. Every time I search for macro issues in Google, I either get results that apply specifically to Word macros (which isn't applicable), or Excel results, but only about hidden worksheets, or grayed out security settings--my settings are already allowing macros.

To sum up then, here is my understanding of the problem:

A macro I created in a previous Excel spreadsheet has been passed down each of the multiple times I duplicated the spreadsheet, but I can't figure out how to either A) Edit the macro, or B) Delete the macro and start fresh with whatever the Excel 2016 method of creating macros or VBA scripts is.

halfer
  • 19,824
  • 17
  • 99
  • 186
Eric Hepperle - CodeSlayer2010
  • 3,745
  • 10
  • 38
  • 53
  • 1
    Maybe it is an XL4 macro. Look at the Name Manager for defined name that includes a "dot". Eg: `GET.xxx` Also check for any hidden or very hidden worksheets. – Ron Rosenfeld Sep 03 '16 at 22:46
  • Thanks for the comment @RonRosenfeld! However, I'm not sure how to do the stuff you mention. Can you clarify? – Eric Hepperle - CodeSlayer2010 Sep 03 '16 at 23:16
  • @pnuts I don't think so ... how can I find out? – Eric Hepperle - CodeSlayer2010 Sep 03 '16 at 23:20
  • @CodeSlayer2010 `Formulas` Ribbon ► `Defined Names` Tab ► `Name Manager` and examine the defined names. And if it is not that, you'll probably need to have someone knowledgeable examine the workbook. Or post a copy that demonstrates the problem, with sensitive information removed, at some site and post a link here. – Ron Rosenfeld Sep 04 '16 at 01:06
  • How do you normally invoke that macro to color the rows? – Siddharth Rout Sep 04 '16 at 03:52
  • 2
    just looking at *..automatically colors odd rows..* sounds like a conditional formatting with a formula like `=MOD(ROW(A1),2)`... Have you checked the conditional formattings? – Dirk Reichel Sep 04 '16 at 04:34
  • @DirkReichel Thanks, that did it! I feel so silly in times like these. The solution was that it was indeed conditional formatting, so I just modified the rule to use a different color. If you had posted a separate answer, I would have marked this as correct. Thanks again! – Eric Hepperle - CodeSlayer2010 Sep 27 '16 at 16:51

1 Answers1

0

As Dirk suggested in the comments, the formatting I was trying to remove/modify was actually caused by conditional formatting, not a VBA macro. The solution, then, was simple:

  1. In the Ribbon, in the "Styles" section, click Conditional Formatting > Manage Rules.
  2. In the window that pops up, select the applicable rule and click "Edit Rule".
  3. make your changes.
halfer
  • 19,824
  • 17
  • 99
  • 186
Eric Hepperle - CodeSlayer2010
  • 3,745
  • 10
  • 38
  • 53