Changing text in shapes suddenly not working

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
143
Hi Guys,

I have a workbook that contains several sheets, and have been adding macros and code to it, and i must have changed something incorrectly somewhere because i had some shapes that i could click and they would run a macro and then change the text of the shape, but the code is failing at that point of trying to change the text of the shape. Here is the code for one of the shapes i was running, which did work, but now doesnt, i get "run-time error 1004: unable to set the text property of the characters class. Can someone help out with what could have happened?
VBA Code:
Sub All_Required_Info_Cell()

ActiveSheet.Unprotect

If Range("All_Required_Info_Cell").Value <> "Yes" Then
    Range("All_Required_Info_Cell").Value = "Yes"
    'This is where it is throwing the error, on the line below:
    ActiveSheet.Shapes("All_Required_Info_Textbox").TextFrame.Characters.Text = "Mark as incomplete"
    Else: Range("All_Required_Info_Cell").Value = "No"
        ActiveSheet.Shapes("All_Required_Info_Textbox").TextFrame.Characters.Text = "Mark as complete"
End If
ActiveSheet.Protect
End Sub
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Run amended code below
Are the 2 lines in the message box identical ?

VBA Code:
Sub All_Required_Info_Cell()

ActiveSheet.Unprotect

If Range("All_Required_Info_Cell").Value <> "Yes" Then
    Range("All_Required_Info_Cell").Value = "Yes"
    'This is where it is throwing the error, on the line below:
MsgBox  ActiveSheet.Shapes(Application.Caller).Name & vbcr & "All_Required_Info_Textbox"
    ActiveSheet.Shapes("All_Required_Info_Textbox").TextFrame.Characters.Text = "Mark as incomplete"
    Else: Range("All_Required_Info_Cell").Value = "No"
        ActiveSheet.Shapes("All_Required_Info_Textbox").TextFrame.Characters.Text = "Mark as complete"
End If
ActiveSheet.Protect
End Sub
 

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
143
Yes they look identical to me. Attached is a picture
 

Attachments

  • Capture.PNG
    Capture.PNG
    2.7 KB · Views: 0

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Replace that message box with this one

MsgBox ThisWorkbook.Names("All_Required_Info_Cell").RefersTo

Does the message box return a cell reference?
 

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
143
I found the issue, the shape property text was set to locked. Thank you for helping me out with this!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,625
Messages
5,625,947
Members
416,144
Latest member
JohnMacDonald

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top