Passing a range between two subroutines.

[Philosophaie]

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

 

[D_Rennie]

Hello

I dont know of a way to run a worksheet change event like this, thoug
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

 

[Philosophaie]

I would like to know how to get "Target as Range" to the Worksheet_Change
subroutine from the Workbook_Open.

 

[D_Rennie]

Ahh ok maby i dint understand what you where doing.

Though with the worksheet change event the TARGET is the range valu
that is going to be changed, And to the best of my knoledge this canno
be changed.

Maby you would be better off decribing why you think you need to d
this. and from there there may be a solution.,

cheers/

 

[joel]

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
change event then create a new subroutine in the module and call the ne
routine from both the open and change events. You can pass a range t
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 su