• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Sub causes Excel to crash

Hi experts

I have the attached code that formats cells. I've discovered if I select an entire worksheet (which might happen) it causes Excel to crash.

How can I fix?
Public Sub StyleHeading()
  ApplyHeading Selection
End Sub

Public Function ApplyHeading(ByVal r As Range)
 
 Dim c
 For Each c In r
    With c.font
        .Name = "Arial"
        .Size = 12
        .Bold = True
        .Color = RGB(0, 100, 177)
    End With
 Next
 
End Function

Open in new window

0
Fi69
Asked:
Fi69
  • 7
  • 6
  • 4
  • +1
2 Solutions
 
EyalCommented:
Range.Activate
    With Selection.Font
       .Name = "Arial"
        .Size = 12
        .Bold = True
        .Color = RGB(0, 100, 177)
    End With
0
 
Fi69Author Commented:
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'.
0
 
dlmilleCommented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
EyalCommented:
I never did it before but maybe you can check if A1 is in selected range and last--bottom is also seleced
0
 
dlmilleCommented:
if cells.countlarge = r.count then your range is the entire worksheet.

Dave
0
 
EyalCommented:
cool
0
 
dlmilleCommented:
Whooops - overflow on the other side - need countlarge on both sides, lol

If Cells.CountLarge = r.CountLarge then your range is the entire worksheet...

Cheers,

Dave
0
 
NorieCommented:
Definitely not for points, more curiousity.

Have you tried using a Style?
0
 
Fi69Author Commented:
Uh oh, am I missing something... I didn't think Excel had styles?
0
 
NorieCommented:
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

Open in new window

By the way, why are you looping through all the cells in the selection?
0
 
Fi69Author Commented:
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.
0
 
Fi69Author Commented:
Just found them. Had no idea that was there!!!

0
 
NorieCommented:
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.
0
 
Fi69Author Commented:
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.
0
 
NorieCommented:
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?
0
 
Fi69Author Commented:
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.
0
 
NorieCommented:
That makes sense I suppose.

So would just applying a 'standard' style do the job?
0
 
dlmilleCommented:
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
0
 
NorieCommented:
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
0
 
Fi69Author Commented:
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!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now