View Full Version : Calling a Worksheet_SelectionChange Subroutine
vassili
07-03-2007, 11:22 PM
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
07-04-2007, 12:08 AM
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
07-04-2007, 02:14 AM
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
07-04-2007, 05:27 AM
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
07-04-2007, 11:34 PM
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
07-05-2007, 12:20 AM
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
07-05-2007, 01:14 AM
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
07-11-2007, 06:23 PM
been busy at work recently but i'm gonna try this ASAP. thanks/.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.