Change the Caption of a Button error...

[Alex Piotto]

Hi everybody.
I am trying to change the caption of a button using a value from a cell (named PERIODO).

But i get an error like:

Runtime error 1004
Unable to set the caption property of the Button Class

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("PERIODO")) Is Nothing Then

Buttons("Button 1").Caption = "Save As:" & vbNewLine & Sheets("RESUMO").Range("PERIODO") & vbNewLine & "and Clear"

End If

End Sub

Now, if i take out the last part, (& vbNewLine & "and Clear") it works...

What am i missing?

 

[Fluff]

That works for me, what is in the PERIODO cell?

 

[Alex Piotto]

Hi Fluff, the cell contains a date manually inserted... like OUTUBRO 2020. But i noticed that if i insert shorter text it works... maybe is a bug...
Sometimes i get the error sometimes do not. If i write in portuguese there are more characters and it does not work.

 

[Fluff]

What version of Excel are you using?

 

[Alex Piotto]

2007... still...
Now it seems to work but the carriage returns inside the button are too big and part of the caption is hidden

 

[Fluff]

In that case it sounds as though something has changed since 2007, as I cannot get the error you are getting.
If the text in the cell is too long, the caption simply doesn't update.

 

[Alex Piotto]

Thanks for the quick answer. I may have to find another way. Tomorrow!

 

[Fluff]

Sorry I couldn't be of more help.