Erros de hiperlink da macro do Excel.Add

Question

Sou relativamente novo no código do VBA no Excel, portanto, por favor, me perdoe se a resposta for simples (espero que normalmente seja). Eu estou usando o Excel 2010, para referência. Estou tentando realizar o seguinte, com o script que segue:

Se uma cadeia numérica (qualquer valor) é inserida em qualquer célula dentro da coluna D, eu preciso ter isso automaticamente convertido em um Hyerlink que acrescentaria uma certa string de endereço antes do valor da célula de destino e cuspirei um nome amigável inclui o valor da célula.

Em outras palavras, aqui está o exemplo da função do Excel que estou tentando replicar com uma macro:

=HYPERLINK("http://stprs553/tickets/list/single_ticket/293136","Ticket #293136")

Ou, mais especificamente (deve pintar uma imagem mais clara):

=HYPERLINK(CONCATENATE("http://stprs553/tickets/list/single_ticket/","Target_Cell_Data"),"Ticket #Target_Cell_Data")

293136 é a string numérica de exemplo que eu quero que um usuário possa digitar em qualquer célula dentro da coluna D. Quando isso acontecer, eu quero ter esta célula convertida em um Hyperlink, usando a string numérica como os dados base, anexando o restante da URL à esquerda do valor da célula e, em seguida, criar um nome amigável que concatene "Ticket #" com a string numérica do valor da célula. Não quero que meus usuários tenham que aprender fórmulas do Excel para ter um link de referência ou perder tempo tendo que copiar ambas as extremidades da função para envolver os dados da célula.

Aqui está o que eu tenho até agora no VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rangeLimit As Range
Set rangeLimit = Range("D:D")
If Not Intersect(rangeLimit, Target) Is Nothing Then
    If Target <> "" Then
         Target.Hyperlinks.Add Anchor:=Target, Address:="http://stprs533/tickets/list/single_ticket/" & c.Value, TextToDisplay:="Ticket #" & c.Value, Target.Value
    End If
End If
End Sub

Aqui estão os problemas que estou encontrando:

1.) Se eu tentar executar a fórmula como está, recebo um erro de compilação. 2.) Se eu remover o , Target.Value end da string Hyperlink, recebo o erro 424, "Object Required".

O que estou fazendo de errado e / ou como posso criar uma macro para executar a função desejada? Uma boa e sólida hora de pesquisa no Google e dar uma olhada em algumas postagens do StackOverflow me levou ao que eu tenho agora, o que ainda não funciona.

    

Answer 1

Acho que a chamada de função deve ser:

Target.Hyperlinks.Add Anchor:=Target, Address:="http://stprs533/tickets/list/single_ticket/" & Target.Value, TextToDisplay:="Ticket #" & Target.Value

Acabei de alterar as ocorrências de c para Target , o que acho que fará o que você está descrevendo. O erro 'Object required' está sendo lançado porque c não foi instanciado para nada - você não pode recuperar um membro de algo que não é um objeto.

EDIT, para resolver o texto da célula que não está mudando:

Gostaria de saber se o texto da célula permaneceria apenas o número. A correção será algo como isso, eu acho:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rangeLimit As Range
    Set rangeLimit = Range("D:D")
    If Not Intersect(rangeLimit, Target) Is Nothing Then
        If IsNumeric(Target) And Not IsEmpty(Target) Then ' This condition should avoid munging of already-existing links, though if any tickets might be non-numeric it won't work properly.
             Application.EnableEvents = False  ' Must turn off event handling
             Target.Hyperlinks.Add Anchor:=Target, Address:="http://stprs533/tickets/list/single_ticket/" & Target.Value, TextToDisplay:="Ticket #" & Target.Value
             Target.Formula = "Ticket #" & Target.Value ' Should actually change the displayed text
             Application.EnableEvents = True
        End If
    End If
End Sub

É importante desativar o tratamento de eventos sempre que você usar o VBA para editar o conteúdo de uma célula dentro de um evento como esse, caso contrário, você corre o risco de um loop infinito. Eu deveria ter pensado nisso inicialmente; mas, parece que a aplicação de um hiperlink para a célula não aciona Worksheet_Change .

Observe que, se você excluir um hiperlink de uma célula, a célula permanecerá formatada como 'sublinhada' e 'cor da fonte azul'. Para seu caso de uso, onde a coluna sempre será links para tickets ou células em branco, isso provavelmente é aceitável?