none
Change button text from worksheet RRS feed

  • Question

  • I have a spreadsheet with many buttons and user wants to be able to change the text of a button.

    the worksheet is protected.

    is there a way to give them a way to select the button whose text they want to change and change it.

    I am thinking they enter the text in a cell, and select the button they want to change, and then maybe another button that has the routine to change the text

    Thanks

    Wednesday, January 16, 2019 5:19 PM

All replies

  • What kind of buttons? Form Controls buttons or ActiveX Controls buttons?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 16, 2019 8:58 PM
  • They are form control buttons (Developer menu...Insert...Form Control)

    I don't want or allow the user to right click the button and change the text.

    Thanks

    Friday, January 18, 2019 7:27 PM
  • Let's say the user enters the current caption in A1, and the new caption in B1.

    Assign the following macro to the command button that lets the user change the caption:

    Sub ChangeCaption()
        Dim strOld As String
        Dim strNew As String
        Dim btnButton As Button
        strOld = Range("A1").Value
        strNew = Range("B1").Value
        ActiveSheet.Unprotect 'Password:="secret"
        For Each btnButton In ActiveSheet.Buttons
            If btnButton.Caption = strOld Then
                btnButton.Caption = strNew
                Range("A1").Value = strNew
                Exit For
            End If
        Next btnButton
        ActiveSheet.Protect 'Password:="secret"
    End Sub

    Uncomment the two 'Password:="secret" bits if the sheet is protected with a password, and change "secret" to the password you used.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 18, 2019 7:48 PM