Consulting

Results 1 to 8 of 8

Thread: Calling a Worksheet_SelectionChange Subroutine

  1. #1

    Question Calling a Worksheet_SelectionChange Subroutine

    i would like to call this sub from another macro to have it work in a specific sheet in the workbook. this is the code i have that works in whatever sheet i put it in (highlights active row+col), but when i try to call it from another macro, i don't know what argument to pass to get it to work in the active sheet (or whatever sheet i want). the sheet i want it to run in is user generated, so the name will change depending on "i"

    [vba]Call Worksheet_SelectionChange "number " & i & " sheet"[/vba]

    this call method doesn't seem to work....is this the "target" parameter or is it something else? i've tried passing "ActiveSheet" as well to no avail.


    here's the main code...

    [vba]Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim RngRow As Range
    Dim RngCol As Range
    Dim RngFinal As Range
    Dim Row As Long
    Dim Col As Long

    Cells.Interior.ColorIndex = xlNone

    Row = Target.Row
    Col = Target.Column

    Set RngRow = Range("A" & Row, Target)
    Set RngCol = Range(Cells(7, Col), Target)
    Set RngFinal = Union(RngRow, RngCol)

    RngFinal.Interior.ColorIndex = 6

    End Sub[/vba]

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Put your code in a sub in a standard module, (modified to work with worksheet parameter), and call this sub from each worksheet or elsewhere, by passing the Worksheet and Range

    [vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call DoStuff(ActiveSheet, Target)
    End Sub

    'or to run for all sheets

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call DoStuff(Sh, Target)
    End Sub



    Sub DoStuff(ws As Worksheet, Target As Range)
    Dim RngRow As Range
    Dim RngCol As Range
    Dim RngFinal As Range
    Dim Row As Long
    Dim Col As Long

    ws.Cells.Interior.ColorIndex = xlNone

    Row = Target.Row
    Col = Target.Column

    Set RngRow = ws.Range("A" & Row, Target)
    Set RngCol = ws.Range(Cells(7, Col), Target)
    Set RngFinal = Union(RngRow, RngCol)

    RngFinal.Interior.ColorIndex = 6
    End Sub
    [/vba]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    sorry for still being a noob at this. but it's not working.

    basically i have a userform where they input the name of a worksheet they like to pull up from the HDD. on clicking the "ok" button, the requested worksheet comes up and as they click through cells, the highlighted area will stay with the cursor.

    i basically need everything to execute inside the sub cmd_clickOK() which resides in a user form. as far as i know, the subroutine

    [vba]Sub Worksheet_SelectionChange(ByVal Target As Range)[/vba]

    only works if it's executed within a worksheet as the "target range" is the worksheet itself. and if i just call sub DoStuff(ActiveSheet, Target) within the cmd_clickOK() subroutine "ala"

    [vba] Sub cmdOK_Click()

    ....some code.... 'open worksheet

    ....some code.... 'worksheet now open is the active worksheet

    Call DoStuff(ActiveSheet, Target)

    end sub

    Sub DoStuff(ws As Worksheet, Target As Range)
    Dim RngRow As Range
    Dim RngCol As Range
    Dim RngFinal As Range
    Dim Row As Long
    Dim Col As Long

    ws.Cells.Interior.ColorIndex = xlNone

    Row = Target.Row
    Col = Target.Column

    Set RngRow = ws.Range("A" & Row, Target)
    Set RngCol = ws.Range(Cells(7, Col), Target)
    Set RngFinal = Union(RngRow, RngCol)

    RngFinal.Interior.ColorIndex = 6
    End Sub

    [/vba]

    "target range" doesn't get passed. instead, i have to the declare a range, which will mess everything up because it will just highlight that range and not highlight where the cursor goes.

    there's obviously no way i can go around placing that
    Worksheet_SelectionChange(ByVal Target As Range) subroutine into every possible worksheet the user might pull up.

    am i making sense? lol

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I think the only way to do this is to write my code from Post 2 into the opened workbook. There are some items in the KB on how to do this. If you need help going down this route, let us know.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    Quote Originally Posted by mdmackillop
    I think the only way to do this is to write my code from Post 2 into the opened workbook. There are some items in the KB on how to do this. If you need help going down this route, let us know.
    yea, is there a way to "programatically" write vba code into a new worksheet/workbook's module (NOT cell formulas) and then run it? i mean, it's easy to "programatically" fill cells with formulas in a new ws/wb but i don't know if one can then write code into the new wb's modules...

    is it possible to run a macro from workbook "1", have it open a new workbook "2" then insert a module into "2" and then AUTO write some pretyped code into that module or worksheet and then let it run? that would def. take care of the highlighting.

  6. #6
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by vassili
    yea, is there a way to "programatically" write vba code into a new worksheet/workbook's module (NOT cell formulas) and then run it? i mean, it's easy to "programatically" fill cells with formulas in a new ws/wb but i don't know if one can then write code into the new wb's modules...

    is it possible to run a macro from workbook "1", have it open a new workbook "2" then insert a module into "2" and then AUTO write some pretyped code into that module or worksheet and then let it run? that would def. take care of the highlighting.
    Yes, easily done but I wouldn't recommend it except as a last resort - more trouble than it's worth. Post your workbook so we can better see what you're trying to do...
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  7. #7
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    From what I think you're trying to do, try this and see if it does what you want...

    In any standard code module (up the top)[vba]
    Option Explicit

    Public OK As Boolean
    [/vba]

    In the ThisWorkbook code module[vba]
    Option Explicit

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If OK Then
    With Sh
    .Cells.Interior.ColorIndex = xlNone
    Union(.Range("A" & Target.Row, Target), _
    .Range(Cells(7, Target.Column), Target)) _
    .Interior.ColorIndex = 6
    End With
    End If
    End Sub
    [/vba]

    In the UserForm code module[vba]
    Option Explicit

    Private Sub cmdOK_Click()

    OK = True

    '....some code.... 'open worksheet
    '....some code.... 'worksheet now open is the active worksheet

    End Sub

    Private Sub UserForm_Terminate()
    OK = False
    End Sub
    [/vba]
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  8. #8
    been busy at work recently but i'm gonna try this ASAP. thanks/.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •