Worksheet Deactivate
Results 1 to 4 of 4

Thread: Worksheet Deactivate

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,591
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Worksheet Deactivate

    I want to automnatically rename the tab based on whats in cell B8.

    Help Please.

    Code:
     
    
    Private Sub Worksheet_Activate()
    
    
    'ActiveSheet.Name = Range("B8")
    
    
    End Sub
    Last edited by gheyman; Jul 23rd, 2019 at 07:50 AM.
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    Board Regular
    Join Date
    Jul 2011
    Posts
    475
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheet Deactivate

    Code:
    Private Sub Worksheet_Activate()
    
    me.Name = me.Range("B8").value
    
    
    End Sub

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,556
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Worksheet Deactivate

    Try this with a bit of error checking included:

    Code:
    Private Sub Worksheet_Activate()
    
    Dim c As String
    
    c = Range("B8").Value
    
    If Len(c) > 0 Then
        On Error Resume Next
        Set sh = Sheets(c)
        If sh Is Nothing Then
            Me.Name = c
        End If
    End If
    
    End Sub

  4. #4
    Board Regular
    Join Date
    Jul 2011
    Posts
    475
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheet Deactivate

    I'll second that! maybe even do this to be sure to be sure

    Code:
    Private Sub Worksheet_Activate()
        Dim c As String
    
        c = Range("B8").Value
        If Len(c) > 0 And StrComp(c, "history", vbTextCompare) <> 0 Then
            On Error Resume Next
            Set sh = Sheets(c)
            If sh Is Nothing Then
                Me.Name = c
            End If
        End If
    End Sub
    Last edited by trunten; Jul 23rd, 2019 at 08:19 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •