Changing button text on a shared workbook: Run-Time Error '-2147024809 (80070057)'

Question

I recorded a macro to change the text on a button to show the current date.

The worksheet is not protected.

When I shared the Workbook, and click on the button, I get this error:

Run-time error '-2147024809 (80070057)':
Requested Shapes are locked for selection

I unchecked the "Lock" and "Lock Text" (when I right click on the button and click "Format Control" and under "Protection" tab), but it didn't resolve the issue.

I want the macro to work while it is being shared.

Sub updateDate()
'
' updateDate Macro
'

'
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
    Selection.Characters.Text = Date
    With Selection.Characters(Start:=1, Length:=9).Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    End With
    Range("B1").Select
End Sub

PatricK · Accepted Answer · 2017-07-06T06:46:58.510

You cannot access some Objects/Class when you had it Shared/Protected. But what your goal is can be achieved, you don't have to change the Fonts once it is done (bare in mind that this Sub should only be called when the ActiveSheet had this button):

Sub updateDate()
'
' updateDate Macro
'
'
    ActiveSheet.Buttons("Button 1").Text = CStr(Now) ' Date
    Range("B1").Select ' <-- Not needed
End Sub

EDIT
To change Shapes when it is Shared, you first need to UnShare it, make changes and then Save it SharedAccess.

Try add another button and assign it to MakeButtonRedBold:

Sub MakeButtonRedBold()
    MakeButtonStyle01 "Button 1"
End Sub

Private Sub MakeButtonStyle01(ByVal sButtonName As String)
    Dim oBtn As Object
    On Error Resume Next
    Set oBtn = ActiveSheet.Buttons("Button 1")
    On Error GoTo 0
    If Not oBtn Is Nothing Then
        Application.DisplayAlerts = False
        ThisWorkbook.ExclusiveAccess ' UnShare Workbook
        With oBtn.Font
            .Name = "Calibri"
            .Bold = True
            .Size = 11
            .Color = RGB(255, 0, 0)
        End With
        ThisWorkbook.SaveAs ThisWorkbook.FullName, AccessMode:=xlShared ' Share the Workbook
        Application.DisplayAlerts = True
    End If
End Sub

Thank you so much for this. This code works while the workbook is being shared. Would you happen to know how to make the text bold and the color red. I tried do this "ActiveSheet.Buttons("Button 1").Font.Bold = True" but when I shared the workbook and click it I would get a runtime error 1004 unable to set the bold property of the font class. Thank you again for the assist. — Wiz321, Jul 06 '17 at 06:42
@Wiz321 See EDIT for changing Fonts in shared workbook. The method is to un-share before change, then save-as shared afterwards. — PatricK, Jul 06 '17 at 22:48
Thank you so much for providing your code. This really work. — Wiz321, Jul 28 '17 at 05:44

score 0 · Answer 2 · answered Jul 22 '19 at 06:31

I got same error when trying to view png files in vb6.0 sp6 win10-64bit. Maybe you could use it.
Short answer: On Error Resume Next

All code that showed a png file, i found out yesterday.
Changed to Image1.image on Form1 because properties: Stretch = True

Private Sub Form_Load()
' To make this work
' In Menu Project/References [x] Microsoft Windows Image Acquisition Library V2.0
' C:\WINDOWS\System32\wiaaut.dll
' http://www.vbforums.com/showthread.php?813811-How-to-load-PNG-image
' Make png-support.exe DID WORK if added line below
On Error Resume Next ' Get rid of error: vb6 runtime error-2147024809

Dim img As New WIA.ImageFile
img.LoadFile "c:\temp\test.png" ' ok filetypes png jpg gif tif
Set Image1 = img.FileData.Picture
Set img = Nothing

End Sub

Changing button text on a shared workbook: Run-Time Error '-2147024809 (80070057)'

2 Answers2

Linked