none
Excel VBA Question please : how to RRS feed

  • Question

  • Hello all,

    Thank you very much for reading this post and taking your time to answer me.

    My sole experience with VBA consists of "copy paste commands" so would very much appreciate it if you gave out little snippets of information about what each bout of programming does in your answer :).

    Am looking to use a specific excel sheet, let's call it "summary" so that modifying the name on that excel sheet modifies directly the name of each excel tabs (they're already created), and vice versa, modifying the excel name of each tabs would modify the content of the excel sheet "summary".

    Ex: in Excel sheet "summary" cell A5 to A35 would each contain information, say "1", "2" "3" and so on until "30".

    Running the macro would enable the sheets after summary ("sheet 1" sheet 2" "sheet3"  until "sheet 30") to automatically be renamed "1", "2", "3" and so on.

    Likewise, renaming sheet "3" into "test sheet" would automatically change the content of cell "A7" from "3" to "test sheet".

    Your help would be very much appreciated.

    Thank you and regards,

    Johanssen

    Tuesday, August 23, 2016 6:51 PM

Answers

All replies

  • The following assumes that Summary is the first sheet, and that all other sheets are listed in A5:A35.

    Right-click the sheet tab of the Summary sheet and select 'View Code' from the context menu.

    Copy the following into the worksheet module:

    Const FirstRow = 5  ' First row with a sheet name
    Const LastRow = 35 ' Last row with a sheet name
     
    Private Sub Worksheet_Activate()
        Dim r As Long
        For r = FirstRow To LastRow
            If Worksheets(r - 3).Name <> Range("A" & r).Text Then
                On Error Resume Next
                Range("A" & r).Value = Worksheets(r - 3).Name
                If Err Then
                    MsgBox Chr(34) & Range("A" & r).Text & Chr(34) & _
                        " is already in use as a sheet name!", vbCritical
                End If
                On Error GoTo 0
            End If
        Next r
    End Sub
     
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("A" & FirstRow & ":A" & LastRow), Target) Is Nothing Then
            If Target.Count > 1 Then
                Beep
            Else
                On Error Resume Next
                Worksheets(Target.Row - 3).Name = Target.Text
                If Err Then
                    MsgBox Chr(34) & Target.Text & Chr(34) & " is not valid " & _
                        "or it is already in use as a sheet name!", vbCritical
                    Application.Undo
                End If
            End If
        End If
    End Sub

    When you change the name of a worksheet, the list of names will be updated when you activate the Summary sheet.


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

    Tuesday, August 23, 2016 7:50 PM
  • Hello Hans,

    Thank you so much for your swift reply.

    Afraid I can't make it work, and that is totally my fault due to my lack of programming skills. Would it be possible for you to send me a link to an excel upload with the working VBA code?

    Would upload the failed version, but as this account is new, it is being restricted from posting links (I have uploaded the excel file "exemple" on fileupload-but can't post the link)

    Thanks again,

    John

    Tuesday, August 23, 2016 8:14 PM
  • See https://www.dropbox.com/s/ssiqx5t9xisyb7i/RenameSheets.xlsm?dl=1

    Download the workbook to your hard disk.
    Right-click the file in Windows Explorer / File Explorer and select Properties from the context menu.
    Click Unblock, then click OK.
    Make sure that you enable macros when you open the workbook.


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

    Tuesday, August 23, 2016 10:02 PM
  • Hello Hans,

    Thank you so much for your answer. This is exactly what I needed!

    Regards,

    John

    Wednesday, August 24, 2016 12:56 PM