Sub causes Excel to crash

Range.Activate
    With Selection.Font
       .Name = "Arial"
        .Size = 12
        .Bold = True
        .Color = RGB(0, 100, 177)
    End With

I've actually got to work with each cell in the range. I forgot to put that bit of code there, but I take a note of the settings, so I can activate the undo button.

I'm wondering...is there a way to pick that the whole worksheet is selected? That way, I could activate different code that doesn't 'remember the settings'.

Not for points.  Just a consideration:  There should be no need to activate/select:

Public Sub ApplyHeading(ByVal r As Range)

   With r.Font
        .Name = "Arial"
        .Size = 12
        .Bold = True
        .Color = RGB(0, 100, 177)
   End With

End Sub

I never did it before but maybe you can check if A1 is in selected range and last--bottom is also seleced

if cells.countlarge = r.count then your range is the entire worksheet.

Dave

cool

Uh oh, am I missing something... I didn't think Excel had styles?

It does and you could create your own style, apply it to the cells you want.

Might make it easier to 'undo' as well by just changing the style back to 'Normal' or whatever.

Public Sub StyleHeading()
ApplyHeading Selection
End Sub

Public Function ApplyHeading(ByVal r As Range)

    r.Style = "NewStyle"

End Function

Select allOpen in new window

By the way, why are you looping through all the cells in the selection?

As you may have guessed I'm not very familiar with Excel. We had better go back to basics as I've got to get my head around this. How do you apply styles manually in Excel - there's no styles gallery from what I can see like in Word?

PS: I've justed removed the looping - just applying to selection. Thought I had to do it for undoing, but have just worked out I don't have to do that.

Just found them. Had no idea that was there!!!

If you are using Excel 2007 you'll find a Cell Styles button in the Styles section on the Home tab.

When you click you'll get a dropdown with all the 'standard' styles.

At the bottom you can select New Cell Style... and create your style.

The initial format of your new style will be based on the active cell but you can change it however you want.

You can also create styles in code but unless you are using simple formatting then it might get complicated.

Thank you so much. I've had a play and set them up. Table styles too I discovered. Oopsy!

Just thinking... if I apply those styles through buttons on a custom ribbon tab (created via custom ui editor), it won't undo will it, because it is vba code.

I don't think so, but it really depends what you mean by 'undo'.

If you wanted to just return to the 'normal' style you could just add another button for that.

To be honest, you don't see people asking about undo very often.

Why do you want to undo?

I was asked to do it because apparently some of the users wouldn't think to just reapply a style, they'd want to be able to undo.

That makes sense I suppose.

So would just applying a 'standard' style do the job?

Looks like you have it sorted - great!  I was worried about all that work simulating an undo... :)

@imnorie - is there a trick to deploying a style?

You all have a good night.  Its 4am where I am so I'm going to be in trouble if I sleep too late, tomorrow!

Dave

Dave

Not sure what you mean by 'deploying'.

For a range you just apply a style by setting the Style property of the range and that's about it.

Do you mean something else?

eg how you create them, transfer them between workbooks etc

Hi imnorie,

Through dlmille's response to my original question, I've got the sub working and not crashing. (Basically if the range is too large it's skipping over the remember sub and undo sub --- better than nothing). Thanks to you, I've also set up the Excel styles to reflect the styles I'd manually created (just in case some users are used to doing this and prefer to use those). So I think its all good!

Thank you so much for mentioning the styles!