Excel Mudar nome da planilha em função de uma célula

[daviddatal]

Preciso que o excel mude o nome da planilha (plan1, plan2, plna3 etc) de acordo com o que estiver em uma célula, por exemplo, C3.

[Edson Luiz Branco]

@daviddatal , bom dia.

 

Célula C3 de qualquer planilha? Se for isso, insira no evento Change da pasta de trabalho:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Not Intersect(Target, Sh.Range("C3")) Is Nothing Then
    On Error Resume Next
      Sh.Name = Sh.Range("C3").Value
    On Error GoTo 0
  End If
End Sub

Obs.: lembre-se de habilitar as macros.

Dúvida David.zip

[daviddatal]

Preciso que mude só na primeira planilha.

 

Testei inicialmente apenas colocando em EstaPasta_de_Trabalho:

Sh.Name = Sh.Range("C3").Value

 

Depois tentei o mesmo código apenas em Plan1(teste), mas não funcionou. O que preciso mudar se for apenas para a planilha 1?

[Edson Luiz Branco]

 

Nesse caso insira em Plan1(teste):

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Me.Range("C3")) Is Nothing Then
    On Error Resume Next
      Me.Name = Me.Range("C3").Value
    On Error GoTo 0
  End If
End Sub

 

[daviddatal]

Tenho 2 perguntas:

 

1) Qual a diferença entre "Sh" e "Me" ? São parâmetros internos do VBA?

 

2) Como faço para indicar a célula C3 de outra planilha?

[CasaDoHardware]

Declare uma variavel.

 

Dim Sh as worksheet

 

Set Sh = Sheets("Sua Planilha")

 

Me.name =Sh.range("C3").value

[daviddatal]

E o restante do código?

 

Até tentei fazendo assim:

 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Sh As Worksheet
    Set Sh = Sheets("Plan1")

  If Not Intersect(Target, Sh.Range("C3")) Is Nothing Then
    On Error Resume Next
      Me.Name = Sh.Range("C3").Value
    On Error GoTo 0
  End If
 
End Sub

 

 

Mas não funcionou.

[Edson Luiz Branco]

11 horas atrás, daviddatal disse:

1) Qual a diferença entre "Sh" e "Me" ? São parâmetros internos do VBA?

São internos do VBA sim. Veja:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Quando você insere o código para detectar mudanças em qualquer célula de qualquer planilha usando a armadilha de evento Workbook SheetChange no módulo EstaPasta_de_Trabalho, o Excel te dá a oportunidade de identificar em qual Sheet (Sh) a mudança ocorreu e também qual foi o range (Target) em que isso ocorreu. Não foram variáveis inseridas pelo usuário, são automáticas desse evento mesmo.

    Já a palavra reservada Me refere-se à classe em que está o código. Se está no módulo EstaPasta_de_Trabalho, Me é um objeto Workbook representando a própria pasta de trabalho. Se está num módulo de planilha, Me é um objeto Worksheet, Se dentro de um Userform, Me é o Userform. Digamos que é uma forma de "referir-se a si próprio".

Então, Sh é um argumento criado para esse e outros eventos no Excel, enquanto Me está presente também no VBA de outros aplicativos.

 

11 horas atrás, daviddatal disse:

2) Como faço para indicar a célula C3 de outra planilha?

 

Worksheets("OutraPlanilha").Range("C3")

 

______

David, se me permite, acho que você deveria descrever bem detalhadamente tudo o que você deseja fazer, toda a situação, senão ficaremos longo período nessa tentativa-e-erro. Seu problema parece ser simples de resolver, mas ainda está confuso para mim, não está suficientemente claro.

[daviddatal]

12 horas atrás, Edson Luiz Branco disse:

______

David, se me permite, acho que você deveria descrever bem detalhadamente tudo o que você deseja fazer, toda a situação, senão ficaremos longo período nessa tentativa-e-erro. Seu problema parece ser simples de resolver, mas ainda está confuso para mim, não está suficientemente claro.

 

Tenho um arquivo do Excel com 2 planilhas (Plan1 e Plan2).

Preciso que o nome da segunda planilha mude automaticamente de acordo com o que estiver digitado na célula C3 da Plan1.

 

 

 

[Edson Luiz Branco]

Nesse caso no módulo da Planilha 1 use o CodeName da Plan2 e não o seu nome real, já que este vai mudando a toda hora. Ou use seu índice:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Me.Range("C3")) Is Nothing Then
    On Error Resume Next
      Plan2.Name = Me.Range("C3").Value  'ou:
      'Worksheets(2).Name = Me.Range("C3").Value
    On Error GoTo 0
  End If
End Sub

 

DúvidaDavidDatal.zip

[daviddatal]

Perfeito.

 

Dessa forma o código foi colocado na plan1, cujo valor da célula C3 vai definir o nome da outra plan2.

Mas e se o código fosse colocado na plan2, ou seja, na planilha que quero renomear?

 

 

[Edson Luiz Branco]

2 horas atrás, daviddatal disse:

Mas e se o código fosse colocado na plan2, ou seja, na planilha que quero renomear?

 

Aí o evento Change não teria sido disparado, pois quem mudou (Change) foi a célula C3 da Plan1 e não da Plan2.

Nesse caso, se você não quiser usar o código no módulo da Plan1, tem que usar no módulo da pasta de trabalho inteira (EstaPasta_de_trabalho) para ele poder detectar mudanças. O efeito colateral é que o código vai rodar sempre que alguma célula em qualquer planilha for alterada, podendo causar lentidão:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Sh.CodeName = "Plan1" Then            'ou If Sh.Name = "Plan1", se Plan1 não mudar de nome
    If Not Intersect(Target, Sh.Range("C3")) Is Nothing Then
      On Error Resume Next
        Plan2.Name = Sh.Range("C3").Value  'ou Worksheets(2).Name = Sh.Range("C3").Value
      On Error GoTo 0
    End If
  End If
End Sub

 

[daviddatal]

Criei uma planilha que inicialmente se chama CÁLCULO.

Em gerenciador de nomes eu criei um nome para a célula A1, que é NOME1.

Adicionei um código para que o nome dessa planilha mude em função de NOME1.

 

 

Estou tentando automatizar essa planilha, para que tudo fique em função de NOME1, e não do nome fixo inicial da planilha (CÁLCULO).

 

Em três partes do código preciso chamar  NOME1 e não o nome fixo (CÁLCULO) da planilha:

 

ActiveWorkbook.Worksheets(CÁLCULO).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(CÁLCULO).Sort.SortFields.Add Key:=Range("B1:B10"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

 

'...

ActiveCell.FormulaR1C1 = "=IF(5>=COUNTIF(CÁLCULO!R3C4:R10C4,"teste");""CORRETO"";""ERRADO"")"

 

 

Qual a sintaxe correta para trocar nos códigos o nome fixo (CÁLCULO) da planilha pelo nome criado em gerenciador de nomes (NOME1)?

Eu tentei algo do tipo:

ActiveWorkbook.Worksheets(NOME1).Sort.SortFields.Clear ....... não funcionou

ActiveCell.FormulaR1C1 = "=IF(5>=COUNTIF(NOME1!R3C4:......... não funcionou

ActiveWorkbook.Worksheets(Plan1).Sort.SortFields.Clear ....... não funcionou

ActiveCell.FormulaR1C1 = "=IF(5>=COUNTIF(Plan1!R3C4:......... não funcionou

[CasaDoHardware]

Vamos la o que você pretende fazer?

 

Classificar todas as planilhas?

 

Se sim pode testar isso

 

 

Dim Ws as worksheet

 

for each ws in worksheets

 

codigo para classificar

 

next ws

 

end sub

 

 

 

 

[Edson Luiz Branco]

É continuação do problema do seu outro tópico? Agora tem elementos novos que não haviam antes.

 

Supondo uma variável String, digamos N:

Dim N As String

 

Para retornar o conteúdo de uma célula nomeada:

       N = Range("NOME1").Value

 

No seu caso, então:

 

ActiveWorkbook.Worksheets(N).Sort.SortFields.Clear .......

ActiveCell.FormulaR1C1 = "=IF(5>=COUNTIF(" & N & "!R3C4:.........

 

 

 

Para retornar o nome da planilha em que a célula nomeada está, dá pra usar

 

      N = Range("NOME1").Parent.Name

 

 

[daviddatal]

Esse " & N & " é bem estranho!

 

Pronto, ao invés de usar um nome do gerenciador de nomes foi usada uma variável.

Mas como seria para colocar o nome do gerenciador de nomes?

[Edson Luiz Branco]

1 hora atrás, daviddatal disse:

Pronto, ao invés de usar um nome do gerenciador de nomes foi usada uma variável.

 

É mais cômodo colocar numa variável para não ficar muito longa a cadeia. Também pode ficar mais rápido, pois o VBA não precisaria avaliar a expressão cada vez que ela surge, principalmente se tiver muitas ocorrências dela.

 

1 hora atrás, daviddatal disse:

Mas como seria para colocar o nome do gerenciador de nomes?

Para usar na forma original:

ActiveWorkbook.Worksheets(Range("NOME1").Value).Sort.SortFields.Clear .......

 

1 hora atrás, daviddatal disse:

Esse " & N & " é bem estranho!

 

É estranho mas necessário, já que você está utilizando a propriedade FormulaR1C1 que é do tipo String. Portanto tudo que você colocou entre as aspas iniciais e finais é usado do jeito que está, nada que está ali dentro é avaliado como uma expressão durante o processamento da macro.

Por isso foi necessário quebrar a cadeia na parte inicial, fixa ("=IF(5>=COUNTIF("), na parte intermediária, avaliada em tempo de execução (N ou se preferir Range("NOME1").Value),  e na parte final, novamente fixa ("!R3C4:.........") e concatenar essas 3 partes com o & (ou +) para que a string total tenha a parte "interna" variável.

 

O que foi feito foi simplesmente dizer:

a = "=IF(5>=COUNTIF("

b = Range("NOME1").Value

c = "!R3C4:........."

chegando a

ActiveCell.FormulaR1C1 = a & b & c  (ou a + b + c)

Um detalhe. você pode desprezar a propriedade .Value e usar só Range("NOME1") se achar melhor, já que é a propriedade padrão. Preferi deixar qualificado para ilustrar que o que retorna é o valor da célula nomeada (mais clareza), mas é opcional.

[daviddatal]

Excelente, @Edson Luiz Branco !!

 

Eu tentei de todo jeito e não conseguia chamar NOME1 para o local "=IF(5>=COUNTIF(" ....

 

" & N & " na verdade é bem simples, mas para que está iniciando no VBA como eu as vezes não percebe.

[daviddatal]

Agora me surgiu um problema:

Quando o texto da célula nomeada (A1) contém apenas uma palavra, o código VBA que o utiliza funciona normalmente.

 

Porém, se o texto da célula (A1) tiver espaço, ou seja, mais de uma palavra, aí o código apresenta erro.

Na verdade não é bem um erro, durante a execução da macro é aberta uma caixa de diálogo perguntando onde está a planilha com o nome referenciado em " & N & ", e esse nome é a última palavra contida na célula (A1).

 

Portanto, preciso de algo que faça com que o Excel procure por uma planilha presente na pasta de trabalho ativa que tenha o texto completo da célula (A1), e não apenas a última palavra escrita lá.

A variável N até guarda o texto completo, mas na parte do código que tem " & N & " só está reconhecendo a última palavra do valor dessa variável.

 

[CasaDoHardware]

Disponibiliza a planilha com o codigo completo.

[daviddatal]

Basta colocar esse código abaixo em uma nova planilha:

 

 

sub testando ()

 

N = Range("C3").Value

ActiveCell.FormulaR1C1 = "=IF(5>=COUNTIF("  & N & "!R3C4:R10C4,"teste");""CORRETO"";""ERRADO"")"

 

end sub

[Edson Luiz Branco]

O @CasaDoHardware  tem toda a razão. É muito ruim ter que imaginar como estão suas planilhas e tentar adivinhar só por um fragmento mínimo de código o que pode estar causando o mal funcionamento. 

Se anexar, vai facilitar...

[daviddatal]

Nesse caso, anexei uma planilha teste:

 

 

Teste macro.rar

[CasaDoHardware]

Cara de uma vez por todas:

 

O que você quer fazer?

 

Eu nao entendi o que você pretende!!

 

Ta difícil!

 

Aparentemente é mais simples que somar duas celulas.

 

 

Esquece tudo que você fez e diz o que você pretende.

[daviddatal]

A macro, como está escrita no código, faz uma edição na célula selecionada.

Nessa edição, existe um IF e um COUNTIF.

O problema está no COUNTIF, pois ao indicar a planilha em que será feita a contagem, por meio da variável N, o Excel não localiza uma planilha na pasta de trabalho atual, nos casos em que a variável N armazenar duas ou mais palavras.

Neses casos ( N armazenar duas ou mais palavras) o Excel procura uma planilha que tenha apenas a última palavra armazenada na variável N, por exemplo, se N = SÃO PAULO, o COUNTIF só está procurando por uma planilha que tenha o nome PAULO.

adicionado 27 minutos depois

 

==================================

Atualização:

 

 

Consegui!

 

Onde tem:

N = Range("C3").Value

 

Mudei para:

N = "'" & Range("C3").Value & "'"

 

Testando, eu gravei uma macro com o mesmo código em uma planilha que tinha 2 palavras, com isso notei que foi inserido apóstrofe antes e depois do nome dessa planilha. Apenas isso que faltava! rsrs