macro greyed out (while others are ok)

[smak]

I have a worksheet with a number of macros. some of them allow me to run them or edit them. but some dont have that option. thes are saved to the individual workbook.

Basically when I pull up my ist of macros, I can run some but not others.

Pleas help me understand why some of these are not 'usable. thank you in advance.

 

[Joe4]

Pick one of the procedures that is doing this and do the following for us:
- Let us know the name of the Module it is located in
- Post the VBA code here for us to see (see here for steps on how to post your code: How to Post Your VBA Code)

 

[smak]

module: D1_sites_add_calculation_column

code:

''****Declare variables****
    Dim I As Integer
    Dim ws1 As Worksheet
    Dim lr As Long
    
    I = 1
    Sheets("Alaska").Activate
    
    Do While I <= 12
    '****set variables***
        Set ws1 = ActiveSheet
        lr = Cells(Rows.Count, "a").End(xlUp).Row
    '****Cululative totals column****
        Columns("I:I").Select
            Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("I3").Select
            ActiveCell.FormulaR1C1 = "cumulative total tests"
        Range("I4").Select
            ActiveCell.FormulaR1C1 = "=RC[-1]"
        Range("I5").Select
            ActiveCell.FormulaR1C1 = "=RC[-1]+R[-1]C"
        Range("I5").Select
            Selection.AutoFill Destination:=Range("i5:i" & lr)
        Columns("I:I").Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        
        
        
        
        Columns("k:k").Select
            Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("k3").Select
            ActiveCell.FormulaR1C1 = "cumulative positive tests"
        Range("k4").Select
            ActiveCell.FormulaR1C1 = "=RC[-1]"
        Range("k5").Select
            ActiveCell.FormulaR1C1 = "=RC[-1]+R[-1]C"
        Range("k5").Select
            Selection.AutoFill Destination:=Range("k5:k" & lr)
        Columns("k:k").Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        ActiveSheet.Next.Select
        I = I + 1
    Loop
End Sub

 

[smak]

it works on a different document but not this one. and I can go inot the editor and run it with the editor open. but not from the actual menu. of course to enter the editor I use one of the macros that will let me edit the macro and then from there I can see the code of the ones that are not working

 

[Joe4]

The most important line of the code is the procedure name and parameters. That is the part I was most wanting to see, but it appears that you cut that part off.

If it works from one workbook but not another, if the code is exactly the same, my first thought would be that the one workbook may be corrupted.

 

[smak]

sorry for the extra attachments. I thought I copied everything.

I had the name of the macro as C5_fix_callouts . so the code looked like

 Sub C5_fix_callouts()
 ...
 End Sub

for some reason the C in the name was making it so that the menu would not let me select it. After realizing this, I changed it the C to and X and it worked. I had just chosen the letters C and X at random so they did not really stand for anything...or so I thought.

Now the name of my sub is X5_fix_callouts and it works fine.

thank you for helping me figure it out. I had no idea a name could cause such issues

 

[Joe4]

That is weird. I never heard of of anything like that happening, and not sure what it doesn't like about C5.
I did some testing of my own, and it did not like "C5" or "X5" as procedure names. I could not run either one from the menu, just like you described.
If I subsitute anything for the number, then it works OK.
I guess I have never had this issue because I typically do not use numbers in my procedure names.

Anyway, I am glad that you were able to figure out how to get it to work.

 

[Fluff]

Was it all the macros that began C#_ that couldn't run?

 

[Joe4]

Fluff helped me identify what we think is going on.

It appears that any procedure name that begins with "Rx" or "Cx" where "x" is a number is being confused with R1C1 range referencing.
And with my other tests where I was doing short two characters names like "X5" were failing because "X5" is a valid range reference.

So I guess the important takeaway is not to use anything that likes like it is (or could be the start of) a valid range reference.

 

[smak]

Yes I agee it was the Rx/CX thing you just mentioned. thanks for helping me figure it out