Very simple macro that worked properly all along, now shuts down Excel

[zookeepertx]

Hello all,

I have a very simple macro that calls up a userform, at least it used to. The coding is very short and simple. It has worked fine for quite a while - I use it every day - but suddenly, one day, running the macro shuts down Excel entirely. I mean, if I have 5 workbooks open, they all close! Can anyone give me a clue as to why this might be?
Here's the code:

Sub Show_Colors()
'Calls up Userform with color palette for cells
    Color_Palette.Show vbModeless
End Sub

Thank you for any insights.

Jenny

 

[DanteAmor]

Color_Palette.Show

What that line does is open a userform, so at the time of opening it is when the problem occurs, we should review the code you have in that userform.
Put here all the code of the userform to review it.

Also try the following:

Sub Show_Colors()
'Calls up Userform with color palette for cells
  Color_Palette.Show
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[zookeepertx]

What that line does is open a userform, so at the time of opening it is when the problem occurs, we should review the code you have in that userform.
Put here all the code of the userform to review it.

Also try the following:

Sub Show_Colors()
'Calls up Userform with color palette for cells
  Color_Palette.Show
End Sub

The userform needs to be modeless so it will stay open while I work with the sheet. It lets me choose from various colors to apply to the cell and it's just a pain having to open the color palette from the ribbon each time.
Here's the userform code:

Private Sub Label1_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label1").BackColor
End Sub
Private Sub Label2_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label2").BackColor
End Sub
Private Sub Label3_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label3").BackColor
End Sub
Private Sub Label4_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label4").BackColor
End Sub
Private Sub Label5_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label5").BackColor
End Sub
Private Sub Label6_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label6").BackColor
End Sub
Private Sub Label7_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label7").BackColor
End Sub
Private Sub Label8_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label8").BackColor
End Sub
Private Sub Label9_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label9").BackColor
End Sub
Private Sub Label10_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label10").BackColor
End Sub
Private Sub Label11_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label11").BackColor
End Sub
Private Sub Label12_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label12").BackColor
End Sub
Private Sub Label13_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label13").BackColor
End Sub
Private Sub Label14_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label14").BackColor
End Sub
Private Sub Label15_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label15").BackColor
End Sub
Private Sub Label16_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label16").BackColor
End Sub
Private Sub Label17_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label17").BackColor
End Sub
Private Sub Label18_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label18").BackColor
End Sub
Private Sub Label19_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label19").BackColor
End Sub
Private Sub Label20_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label20").BackColor
End Sub
Private Sub Label21_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label21").BackColor
End Sub
Private Sub Label22_Click()
ActiveSheet.Range(Selection.Address).Interior.Color = Me.Controls("Label22").BackColor
End Sub
Private Sub UserForm_Click()

End Sub

One thing I did notice a while back is that if I have hidden rows and select the visible cells to color with this palette, it would only color some of them. It's as if it suddenly had some limit as to the number of cells it's willing to color at one time. But it didn't used to do that; it originally would color any number of cells that I selected. That problem also occurred one day, out of the blue. I didn't have time to address it at the time, but I wonder if it could be related.

Jenny

 

[DanteAmor]

I don't see a problem with your code.
Since nothing is executed when opening the userform.

I guess the problem is in the sheet, maybe it has some damage.

Copy your userfom to a new workbook and try on a new sheet.
If it works, then copy only the data from your (old and damaged) sheet to the new sheet.

 

[zookeepertx]

The macro isn't stored within a workbook as I use it in different instances; it's in my Personal workbook. I've tried to open it in several different workbooks, including opening a brand new, blank one and the same thing happens.
I also tried inserting a new module and pasted the macro code in there, then tried to run it and it shut Excel down again.

I don't understand how something that worked one day can just stop working overnight, especially to stop working on ANY workbook. It's just not fair!

It was SO handy and saved me a lot of annoyance. I just don't get why stupid Microsoft removed the ability to have a floating color palette!

Can you think of anything else?

Thanks.

 

[zookeepertx]

I don't see a problem with your code.
Since nothing is executed when opening the userform.

I guess the problem is in the sheet, maybe it has some damage.

Copy your userfom to a new workbook and try on a new sheet.
If it works, then copy only the data from your (old and damaged) sheet to the new sheet.

I think I've got it! These 2 lines at the bottom of the UserForm code appeared to cause it:

Private Sub UserForm_Click()

End Sub

I don't know how they got there, but I took them out and it's working correctly now. So far, anyway. It looks like the code was looking for another color - Label #22 - but there are only 21 colors in the palette. Hoping that fixes it for good.

Thanks for giving me a clue what to look at. Guess I had to go to lunch and come back to it to see it.

Jenny