Posts by TICS

    I've never used the querySelector before, but learning from your code it seems the following line at the end would do the trick idoc.querySelector("input[type*=submit]").Click

    Hi.

    I've been trying to click into the save button of a rich-text editor without luck. Does anyone have an idea on how to do it?


    the HTML of the form in which the button is:


    there's also this javascript that is relate to the button


    I can't use the Element's ID since the number often change, so the command I got to that I believe should be working is:

    IE2.document.getElementsByTagName("body")(0).Children(1).Children(0).Children(2).Children(0).Children(0).Children(1).getElementsByTagName("a")(0).Click


    But when I run it, nothing happens.;(

    I've managed to make it work, probably not the best written code, but it works.
    Thank you for the help.


    Working Code:



    [VBA]Private Sub Worksheet_Change(ByVal Target As Range)
    Dim thisrow As Long
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    If Target.Value = "Distribuir" Then
    thisrow = Target.Row
    If Cells(thisrow, 5).Value <> "" Then
    Worksheets("Gráfico").Range("M22").Copy
    Else
    Worksheets("Gráfico").Range("M20").Copy
    End If
    ActiveCell.PasteSpecial xlPasteValues
    End If
    End If
    End Sub[/VBA]

    Thank you for the help Carim. I wasn't aware of that.
    I merged it with the other code and did a few changes to it, since it still wasn't working.


    now I have the following code:


    [VBA]Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s1 As Worksheet, s2 As Worksheet, lr As Long, s3 As Worksheet, thiscolumn As Long
    Set s1 = Sheets("Controle")
    Set s2 = Sheets("Distribuição")
    Set s3 = Sheets("Gráfico")
    Dim thisrow As Long
    If Target.Column = 6 Then
    thisrow = Target.Row
    If Target.Value = "Atribuído" Then
    lr = s2.Range("A" & Rows.Count).End(xlUp).Row
    s1.Range(Cells(thisrow, 1), Cells(thisrow, 2)).Copy s2.Range("A" & lr + 1)
    s2.Range("C" & lr + 1) = Date
    s2.Range("A:B").ClearFormats
    End If
    End If
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    thiscolumn = Target.Column
    If Target.Value = "Distribuir" Then
    s3.Range(Cells(20, 13)).Copy s1.Range(Cells(thisrow, thiscolumn)
    End If
    End If
    End Sub[/VBA]


    The first part is working fine (the code I already had), but in the second part I'm getting an error when trying to copy the values in
    [VBA]s3.Range(Cells(20, 13)).Copy s1.Range(Cells(thisrow, thiscolumn)[/VBA]


    Ps: I changed the names of the values and worksheets to portuguese to avoid mistakes when importing in the original file

    Hi, so what I've been trying to do without success is to make excell automatically replace the content of a cell if it's in column "B" and its value has just been changed to "Distribute".
    Then, if the content of the cell in the same row but in column E is empty the value to replace "Distribute" will be copied from cell M22 in another worksheet.
    Or, If the content of the cell in the same row but in column E is NOT empty the value to replace "Distribute" will be copied from cell M24 in another worksheet.


    How should I proceed to make it happen?
    Thanks in advance!



    Ps: I did'nt get to the part where I would copy different values after checking the column E.
    The code I was trying is the following:


    [VBA]
    Private Sub Worksheet_Change2(ByVal Target As Range)
    Dim s1 As Worksheet, s2 As Worksheet, thiscolumn As Long, thisrow As Long
    Set s1 = Sheets("Control")
    Set s2 = Sheets("Graph")
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    thisrow = Target.Row
    thiscolumn = Target.Column
    If InStr(1, Range(Cells(thisrow, thiscolumn)), "Distribute") Then
    s2.Range(Cells(20, 13)).Copy
    s1.Range(Cells(thisrow, thiscolumn)).PasteSpecial x1PasteValues
    End If
    End If
    End Sub
    [/VBA]

    Hi, so what I've been trying to do without success is to make excell automatically replace the content of a cell if it's in column "B" and its value has just been changed to "Distribute".
    Then, if the content of the cell in the same row but in column E is empty the value to replace "Distribute" will be copied from cell M22 in another worksheet.
    Or, If the content of the cell in the same row but in column E is NOT empty the value to replace "Distribute" will be copied from cell M24 in another worksheet.


    How should I proceed to make it happen?
    Thanks in advance!

    Hi, so what I've been trying to do without success is to make excell automatically replace the content of a cell if it's in column "B" and its value has just been changed to "Distribute".
    Then, if the content of the cell in the same row but in column E is empty the value to replace "Distribute" will be copied from cell M22 in another worksheet.
    Or, If the content of the cell in the same row but in column E is NOT empty the value to replace "Distribute" will be copied from cell M24 in another worksheet.


    How should I proceed to make it happen?
    Thanks in advance!

    Thank you for the reply, but I'm not sure on how to make it work.


    Does your sugestion mean that I should replace the code in the module with

    Code
    1. Public Function SENEGRITO(MyCell As Range) As Variant
    2. Set rngData = MyCell
    3. For Each TBItem In rngData
    4. SENEGRITO = TBItem.Font.Bold
    5. Next
    6. End Function

    ?


    Because I got the same results doing so.

    Hi


    So, in order to calculate the amount of cells not in bold in a column (that are set manually), I've created the formula "SENEGRITO" (IFBOLD in portuguese) using the module:


    Code
    1. Public Function SENEGRITO(MyCell As Range) As Variant
    2. SENEGRITO = MyCell.Font.Bold
    3. End Function


    I then added a new column with the formula:

    Code
    1. =SENEGRITO([@Início])

    and so I could count the amount of "FALSE" there.


    And it worked great. The thing is, whenever I filter the table using

    Code
    1. Private Sub Filtrar_int_Click()
    2. ActiveSheet.ListObjects("Intimacoes").Range.AutoFilter Field:=6, Criteria1:=""
    3. End Sub


    all the results from the formula SENEGRITO return #Value.


    If I then run

    Code
    1. Private Sub Mostrar_int_Click()
    2. ActiveSheet.ListObjects("Intimacoes").AutoFilter.ShowAllData
    3. End Sub


    It all works again.


    If instead of filtering using the VBA code, I apply the same filter manually, there's no error.
    If I edit a cell with the error mesagem without changing anything in it, this cell (and only it) shows the correct value.


    Does anyone now why this is happening and what I could do to fix it?


    I know that changing the format from a cell to bold is not ideal as a way of marking it, but the table is already complex, and is also used by people with no experience in excel, so I'm trying to make it as simple as possible for the end user.

    Thank you, AlanSidman.
    Your code is much more elegant then the others I've been trying.
    Yet, nothing happens when changing the [Status] to Called.
    I Tried both copying the Code to my own Worksheet and directly in the sample.xlsm you provided.
    Do you know what could it be?
    If it makes a difference, I'm using Excel in Portuguese, and although have to translate the formulas in order for them to work, I haven't seen a similiar issue with the codes.

    Hi, I've been searching online and doing a lot of trial and error but I haven't been able to make it word. If someone could kindly help me, I'll apreciate very much.


    On Worksheet "Original" there's Table1 with columns [Name] [Number] [Info] [Status]. There are no rows with the same [Name], as Table1 works as a Contact List.
    On Worksheet "History" there's Table2 with columns [Name] [Number] [Date]. There can be duplicates for [Name] as Table2 works as a Log.


    What I'm trying to do is that everytime I chance the value of [Status] to "Called", the values of [Name] and [Number] from that specific row in Table1 will be copied to a new row in Table2, and in that new row, under [Date], will be added today's date.


    If you would like I can add some of the code Ive been trying without success.


    Thanks in advance!