Calling a Worksheet_SelectionChange Subroutine

[vassili]

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"

Call Worksheet_SelectionChange "number " & i & " sheet"

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...

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

[mdmackillop]

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

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

[vassili]

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

Sub Worksheet_SelectionChange(ByVal Target As Range)

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"

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



"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

[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.

[vassili]

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.

[johnske]

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...

[johnske]

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)
Option Explicit

Public OK As Boolean


In the ThisWorkbook code module
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


In the UserForm code module
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

[vassili]

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