Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a range between two subroutines.
Could someone show me where I am going wrong.
[In "ThisWorkbook"] Private Sub Workbook_Open() Dim RowsEnd As Double Dim Target As Range RowsEnd = Cells(.Rows.Count, "E").End(xlUp).Row 'Not certain if correct Target = "D27:F" & (RowsEnd + 25) 'Unsure if this will call Worksheet_Change Worksheet_Change(Target as Range). End Sub Private Sub Worksheet_Change(Target as Range) With Sheets("Sheet4") For n = 2 To RowsEnd .Cells(n, 5) = .cell((n - 1), 5) + .cell(n, 6) - .cell(n, 4) Next n End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a range between two subroutines.
Hello I dont know of a way to run a worksheet change event like this, though there shouldnt erealy be a need just create a sub and call that. hope this makes sence. Code: -------------------- Option Explicit 'Dim will keep it private (only within the module of sheet level) Dim Target Dim RowsEnd As Long 'public will allow you to use the in other workbooks) 'Public Target 'Public RowsEnd As Long '#####[In "ThisWorkbook"] 'Private Sub Workbook_Open():'changed out for testing Sub Test() With Sheets(1) RowsEnd = Cells(.Rows.Count, "E").End(xlUp).Row Target = "D27:F" & (RowsEnd + 25) End With 'dont run the worksheet event run the sub (i dont think you can call a worksheet event, good chance i could be wrong) SubOne 'or if the sub is located within a sheet (note will not work with native events) Sheet1.SubTwo End Sub Sub SubOne() Dim n As Long With Sheets("Sheet1") For n = 2 To RowsEnd .Cells(n, 5) = .cell((n - 1), 5) + .cell(n, 6) - .cell(n, 4) Next n End With End Sub '### WorkSheet Code Private Sub Worksheet_Change(Target As Range) SubTwo End Sub Sub SubTwo() Cells(1, 2).Value = "New Value" End Sub -------------------- cheers -- D_Rennie ------------------------------------------------------------------------ D_Rennie's Profile: 1412 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=172761 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a range between two subroutines.
I would like to know how to get "Target as Range" to the Worksheet_Change
subroutine from the Workbook_Open. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a range between two subroutines.
Ahh ok maby i dint understand what you where doing.
Though with the worksheet change event the TARGET is the range value that is going to be changed, And to the best of my knoledge this cannon be changed. Maby you would be better off decribing why you think you need to do this. and from there there may be a solution., cheers/. -- D_Rennie ------------------------------------------------------------------------ D_Rennie's Profile: 1412 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=172761 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a range between two subroutines.
The workbook open occurs before the worksheet change will take affect. If you want to use the same code for both the worksheet open and a change event then create a new subroutine in the module and call the new routine from both the open and change events. You can pass a range to the new subroutine sub wokbook open set MyRange = Sheet("sheet1").range("A1") call common_sub(MyRange) end sub --------------------------------------- sub change event (target) call common_sub(target) end sub ----------------------------------------- new sub in a module sub common_sub(MyRange as Range) put common code here end sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=172761 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA - passing Variables to subroutines | Excel Discussion (Misc queries) | |||
Calling subroutines and passing variables | Excel Programming | |||
Calling subroutines and passing variables | Excel Programming | |||
Passing a Range | Excel Programming | |||
passing range to c# | Excel Programming |