Ola pessoal, tudo bem?
esto tentando finalizar uma planilha, mais o codigo que estou colocando não esta funcionando corretmente.
na aba treinamento no departamento "laboratorio" e "Garantia da qualidade", tem a guia de treinamento, ondes os funcionarios que finalizaram o terinamento dve aparecer a datado terinamento finalizado, e embaixo desa celula. aparecer quando funcionario for autorizado, e aparecer a data do mesmo. vou deixar a planilha e o codigo.. se alguem puder me ajudar, agradeço..pois ja tentei de tudo, mais nada adianta.
Obrigada.
Private Sub
Workbook_SheetChange(ByVal Sh [/font][/size]<b>As</b> Object, ByVal Target <b>As</b>[/color] <span style="color:#444444"><b>Range</b>
Dim k <b>As</b> Long, s1 <b>As</b> Shape, s2 <b>As</b> Shape, s3 <b>As</b> Shape, r1 <b>As</b></span> <span style="color:#444444"><b>Range</b>, r2 <b>As</b></span>
<span style="color:#444444"><b>Range</b>, ss1 <b>As</b> String
If Target.Count </span><span style="color:#ab5656">></span> <span style="color:#880000">1</span> <span style="color:#444444"><b>Then</b> Exit Sub
If Target.Value </span><span style="color:#ab5656">=</span><span style="color:#444444"> "Autorizado(a)" <b>Then</b> GoTo Autoriz
If Target.Value </span><span style="color:#ab5656"><></span><span style="color:#444444"> "Trein. Finalizado" <b>Then</b> Exit Sub
<b>Select</b></span> <span style="color:#444444"><b>Case</b> Sh.Name
<b>Case</b> "Geral": k </span><span style="color:#ab5656">=</span> <span style="color:#880000">34</span><span style="color:#444444">
<b>Case</b> "hplc": k </span><span style="color:#ab5656">=</span> <span style="color:#880000">16</span><span style="color:#444444">
<b>Case</b> "Espectro", "Karl fischer",
"Agua", "Titulação", "Peso Medio": k </span><span style="color:#ab5656">=</span> <span style="color:#880000">8</span><span style="color:#444444">
<b>Case</b> "Validação": k </span><span style="color:#ab5656">=</span> <span style="color:#880000">12</span><span style="color:#444444">
<b>Case</b> "Teor A.": k </span><span style="color:#ab5656">=</span> <span style="color:#880000">6</span><span style="color:#444444">
<b>Case</b> "Perfil", "Perda S.": k </span><span style="color:#ab5656">=</span> <span style="color:#880000">10</span><span style="color:#444444">
<b>Case</b></span> <span style="color:#444444"><b>Else</b>: Exit Sub
<b>End</b></span> <span style="color:#444444"><b>Select</b>
If Application.CountIf(Cells(ActiveCell.Row, </span><span style="color:#880000">6</span><span style="color:#444444">.Resize(, k),
"Trein. Finalizado" </span><span style="color:#ab5656"><</span><span style="color:#444444"> k </span><span style="color:#ab5656">/</span> <span style="color:#880000">2</span><span style="color:#444444"> _
<b>Or</b></span> <span style="color:#444444"><b>Intersect</b>(ActiveCell, Cells(ActiveCell.Row, </span><span style="color:#880000">6</span><span style="color:#444444">.Resize(,
k)) <b>Is</b> Nothing <b>Then</b> Exit Sub
Autoriz:
<b>Set</b> r1 </span><span style="color:#ab5656">=</span><span style="color:#444444"> Cells(ActiveCell.Row </span><span style="color:#ab5656">-</span> <span style="color:#880000">1</span><span style="color:#444444">, </span><span style="color:#880000">3</span><span style="color:#444444">
<b>For</b></span> <span style="color:#444444"><b>Each</b> s1 <b>In</b> ActiveSheet.Shapes
If <b>Not</b></span> <span style="color:#444444"><b>Intersect</b>(s1.TopLeftCell, r1) <b>Is</b> Nothing <b>Then</b>
ss1 </span><span style="color:#ab5656">=</span><span style="color:#444444"> s1.TextFrame.Characters.Text: Exit <b>For</b>
<b>End</b> If
Next s1
<b>With</b> Sheets("Treinamentos"
<b>For</b></span> <span style="color:#444444"><b>Each</b> s2 <b>In</b> .Shapes
If <b>Not</b></span> <span style="color:#444444"><b>Intersect</b>(s2.TopLeftCell, .<b>Rows</b>(</span><span style="color:#880000">7</span><span style="color:#444444">) <b>Is</b> Nothing
<b>Then</b>
<b>On</b> Error GoTo jump
If UCase(<b>Left</b>(s2.TextFrame.Characters.Text, </span><span style="color:#880000">4</span><span style="color:#444444">)
</span><span style="color:#ab5656">=</span><span style="color:#444444"> UCase(<b>Left</b>(Sh.Name, </span><span style="color:#880000">4</span><span style="color:#444444">) <b>Then</b>
<b>Set</b> r2 </span><span style="color:#ab5656">=</span><span style="color:#444444"> .Cells(</span><span style="color:#880000">13</span><span style="color:#444444">,
s2.TopLeftCell.Column).Resize(</span><span style="color:#880000">200</span><span style="color:#444444">, </span><span style="color:#880000">4</span><span style="color:#444444">
<b>For</b></span> <span style="color:#444444"><b>Each</b> s3 <b>In</b> .Shapes
If <b>Not</b></span> <span style="color:#444444"><b>Intersect</b>(s3.TopLeftCell, r2)
<b>Is</b> Nothing <b>Then</b>
If
s3.TextFrame.Characters.Text </span><span style="color:#ab5656">=</span><span style="color:#444444"> ss1 <b>Then</b>
If Target.Value </span><span style="color:#ab5656">=</span><span style="color:#444444">
"Trein. Finalizado" <b>Then</b>
.Cells(s3.TopLeftCell.Row </span><span style="color:#ab5656">+</span> <span style="color:#880000">3</span><span style="color:#444444">, s3.TopLeftCell.Column) </span><span style="color:#ab5656">=</span> <span style="color:#880000">Date</span><span style="color:#444444">
MsgBox "A
DATA FOI INSERIDA NA CÉLULA " </span><span style="color:#ab5656">&</span><span style="color:#444444"> _
.Cells(s3.TopLeftCell.Row </span><span style="color:#ab5656">+</span> <span style="color:#880000">3</span><span style="color:#444444">, s3.TopLeftCell.Column).Address(</span><span style="color:#880000">0</span><span style="color:#444444">, </span><span style="color:#880000">0</span><span style="color:#444444"> _
</span><span style="color:#ab5656">&</span><span style="color:#444444"> vbLf </span><span style="color:#ab5656">&</span><span style="color:#444444">
"DA PLANILHA Treinamentos.": Exit Sub
<b>Else</b>:
.Cells(s3.TopLeftCell.Row </span><span style="color:#ab5656">+</span> <span style="color:#880000">5</span><span style="color:#444444">, s3.TopLeftCell.Column) </span><span style="color:#ab5656">=</span> <span style="color:#880000">Date</span><span style="color:#444444">
MsgBox "A
DATA FOI INSERIDA NA CÉLULA " </span><span style="color:#ab5656">&</span><span style="color:#444444"> _
.Cells(s3.TopLeftCell.Row </span><span style="color:#ab5656">+</span> <span style="color:#880000">5</span><span style="color:#444444">, s3.TopLeftCell.Column).Address(</span><span style="color:#880000">0</span><span style="color:#444444">, </span><span style="color:#880000">0</span><span style="color:#444444"> _
</span><span style="color:#ab5656">&</span><span style="color:#444444"> vbLf </span><span style="color:#ab5656">&</span><span style="color:#444444">
"DA PLANILHA Treinamentos.": Exit Sub
<b>End</b> If
<b>End</b> If
<b>End</b> If
Next s3
<b>End</b> If
<b>End</b> If
jump:
<b>On</b> Error GoTo </span><span style="color:#880000">0</span><span style="color:#444444">
Next
<b>End</b></span> <span style="color:#444444"><b>With</b></span>
[color=#444444]<b>End</b>[size=3][font=ui-monospace, monospace] Sub