Calculation

[Robert]

I have the following code in Worksheet SelectionChange
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets(2).Calculate
End Sub"

This for some reason disallows Paste, PasteSpecial and some other features.
Macros having copy and paste fail. I am now trying to have the calculation
applicapable to rows 1 to 341. I amended the code to the following, but the
recalculation is effective only on row 341.
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets(2).Rows(341).Calculate
End Sub"

Any assistance. Thank you

Robert

[Dr. Stephan Kassanke]

"Robert" <[email protected]> schrieb im Newsbeitrag
news:[email protected]...
>I have the following code in Worksheet SelectionChange
> "Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Worksheets(2).Calculate
> End Sub"
>
> This for some reason disallows Paste, PasteSpecial and some other
> features.
> Macros having copy and paste fail. I am now trying to have the calculation
> applicapable to rows 1 to 341. I amended the code to the following, but
> the
> recalculation is effective only on row 341.
> "Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Worksheets(2).Rows(341).Calculate
> End Sub"
>
> Any assistance. Thank you
>
> Robert

Robert,

try Worksheets(2).Rows("1:341").calculate.

cheers,
Stephan

[Norman Jones]

Hi Robert,

> This for some reason disallows Paste, PasteSpecial and some other
> features.

Much macro activity clears the clipboard.

> Macros having copy and paste fail.

Rewrite the code to ensure that the paste operation follows the copy
operation without any intervening operations. As noted above, such
intervening operations may clear the clipboard and cause the problems you
report.

---
Regards,
Norman

"Robert" <[email protected]> wrote in message
news:[email protected]...
>I have the following code in Worksheet SelectionChange
> "Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Worksheets(2).Calculate
> End Sub"
>
> This for some reason disallows Paste, PasteSpecial and some other
> features.
> Macros having copy and paste fail. I am now trying to have the calculation
> applicapable to rows 1 to 341. I amended the code to the following, but
> the
> recalculation is effective only on row 341.
> "Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Worksheets(2).Rows(341).Calculate
> End Sub"
>
> Any assistance. Thank you
>
> Robert

[Robert]

Thank you Norman, Everthing now works well.
Dr. Stephan, thank you for your input. Points noted.
--
Robert

[Robert]

In my earlier message, the addressees have been transposed.
Sorry Dr. Stephan, in my haste got mixed.
--
Robert

[Dr. Stephan Kassanke]

"Robert" <[email protected]> schrieb im Newsbeitrag
news:[email protected]...
> In my earlier message, the addressees have been transposed.
> Sorry Dr. Stephan, in my haste got mixed.
> --
> Robert
>
>
>
Robert,

no problem, it's still early (at least for me ;-)

Just out of curiosity - is there a particular reason you trigger the
calculation each time the selection changes? This is a higly inefficient
approach as no values change by selecting another cell.

Stephan

[Robert]

Dr. I am embarrased to say the file I have with a lot of VLOOKUPs is 48Mb. My
sheet(2) in reference is actually a Data Entry Screen which does
calculations and more than 40 different values values are then posted to
other sheets. The data in these sheets need not be processed real-time. When
calculation is set to auto it took an intolerably long time before the "entry
screen" is ready for the next entry. I will later check for what may be
causing the delay.


Robert
PS. You would have guessed, I do not know any VBA.

[Dr. Stephan Kassanke]

"Robert" <[email protected]> schrieb im Newsbeitrag
news:[email protected]...
> Dr. I am embarrased to say the file I have with a lot of VLOOKUPs is 48Mb.
> My
> sheet(2) in reference is actually a Data Entry Screen which does
> calculations and more than 40 different values values are then posted to
> other sheets. The data in these sheets need not be processed real-time.
> When
> calculation is set to auto it took an intolerably long time before the
> "entry
> screen" is ready for the next entry. I will later check for what may be
> causing the delay.
>
>
> Robert
> PS. You would have guessed, I do not know any VBA.
>
>

Dear Robert,

just my 2cent. You are currently using the SelectionChange event which is
fired each time you change the selection. You can recalculate your sheet
each time a *change* occurs by using the Change event like

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Further speed can be achieved by testing whether Target (the cell where the
change occurs) intersects with your data entry cells thus irrelevant changes
do not force a recalculation.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Range("A1:A5"), Target) Is Nothing) Then
MsgBox "Change in cell A1-A5"
Exit Sub ' or recalculate
' else do nothing
End If
End Sub

cheers,
Stephan

[Robert]

Doc, Thank you for your advice and lessons. I really appreciate them.
The Worksheet_Change you suggested is already used to capture the time of
entry (all of these have been copied from the ng). How many Worksheet_Change
can be incorporated andhow can your code be incorporated. My existing code-

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:

Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Sequence")) Is Nothing Then
With Target
.Offset(25, 0).Value = Format(Now, "dd mmm yy hh:mm:ss")
.Offset(25, 1).Value = Environ("UserName")
End With
End If
‘THERE ARE ANOTHER 6 SUCH CODES HERE
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Amt")) Is Nothing Then
With Target
.Offset(12, 0).Value = Format(Now, "dd mmm yy hh:mm:ss")
.Offset(12, 1).Value = Environ("UserName")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
Thanks again,
Robert

[Dr. Stephan Kassanke]

"Robert" <[email protected]> schrieb im Newsbeitrag
news:[email protected]...
> Doc, Thank you for your advice and lessons. I really appreciate them.
> The Worksheet_Change you suggested is already used to capture the time of
> entry (all of these have been copied from the ng). How many
> Worksheet_Change
> can be incorporated andhow can your code be incorporated. My existing
> code-
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ws_exit:
>
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range("Sequence")) Is Nothing Then
> With Target
> .Offset(25, 0).Value = Format(Now, "dd mmm yy hh:mm:ss")
> .Offset(25, 1).Value = Environ("UserName")
> End With
> End If
> 'THERE ARE ANOTHER 6 SUCH CODES HERE
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range("Amt")) Is Nothing Then
> With Target
> .Offset(12, 0).Value = Format(Now, "dd mmm yy hh:mm:ss")
> .Offset(12, 1).Value = Environ("UserName")
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> --
> Thanks again,
> Robert
>

Hi Robert,

there is only one change event for a worksheet but you can integrate your
calculation routine in the change event.

.........
'THERE ARE ANOTHER 6 SUCH CODES HERE
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Amt")) Is Nothing Then
With Target
.Offset(12, 0).Value = Format(Now, "dd mmm yy hh:mm:ss")
.Offset(12, 1).Value = Environ("UserName")
End With
End If

' PUT CALCULATION STUFF HERE

ws_exit:
Application.EnableEvents = True
end sub

Stephan

[Robert]

Thanks Doc, I am going to give it a shot over the weekend.
Will definately let you know.
--
Robert


"