none
runig a macro in work sheet RRS feed

  • Question

  • i created this macro in sheet1

    cell G1 is where a putting the query data so every time a new date is presented to G1 then the macro in sheet 1 automatically run calling another micro to run.

    This is the code in sheet1

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Sheets.Cells(1, "G") = ActiveCell Then Call company_address End If End Sub

    the problem is if any other cells in the sheet are changing or touched i am get this error

    what i need is the routine to work only if the date in G1 change only.

     

    When I call the other macro I need to know if the request came becouse G1 was change becouse I have another cell (AB2) say in sheet 1 that will call a different macro

     


    Vforum

    Sunday, October 27, 2019 6:12 PM

Answers

  • It has been a while since I have worked with Excel VBA.

    Note that you are using Cells as a property of Sheets, plural. Sheets do not have cells; only a singular sheet has cells. That makes sense, correct? So you need to get a specific worksheet as in something like:

    Set ws = wb.Sheets("Sheet1")

    Then you can use the Cells property for the ws worksheet.



    Sam Hobbs
    SimpleSamples.Info


    • Edited by Simple Samples Sunday, October 27, 2019 7:11 PM
    • Marked as answer by Kosta si Monday, October 28, 2019 11:39 AM
    Sunday, October 27, 2019 7:10 PM

All replies

  • When posting code here, please put it in a code block; use the icon with the "<>" in it.

    Please copy and paste the text of the error message into the post. I cannot read the error message shown in the image. Or at least copy the text into the post and try to copy it exactly. Or at least copy and paste only the message box with the error message so that it is readable.



    Sam Hobbs
    SimpleSamples.Info

    Sunday, October 27, 2019 6:35 PM
  • compile error

    method or data member not found 

    i also include a larger more clear image

    Kosta


    Vforum

    Sunday, October 27, 2019 6:45 PM
  • It has been a while since I have worked with Excel VBA.

    Note that you are using Cells as a property of Sheets, plural. Sheets do not have cells; only a singular sheet has cells. That makes sense, correct? So you need to get a specific worksheet as in something like:

    Set ws = wb.Sheets("Sheet1")

    Then you can use the Cells property for the ws worksheet.



    Sam Hobbs
    SimpleSamples.Info


    • Edited by Simple Samples Sunday, October 27, 2019 7:11 PM
    • Marked as answer by Kosta si Monday, October 28, 2019 11:39 AM
    Sunday, October 27, 2019 7:10 PM
  • that worked thank you

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set wksWB = Sheets("work sheet") 'assigning sheet13 to Info Sheet
    If wksWB.Cells(1, "G") = ActiveCell Then
    Call company_address
    End If

    End Sub

    i though becouse i was calling this in a specific worksheet that will be ok 

    now the second part of my question

    once i go to company_address macro i need to know who send me to this macro the date that change In Cell G1 or in cell AB2 knowing that i will run the specific subroutine in company_address 

    can you help me on this.

    thank you,

    Kosta


    Vforum

    Sunday, October 27, 2019 7:29 PM