Trouble returning to original active cell
 

The following is some code that I have written in Excel 2000 using
Windows 98. It works fine except that it does not return to the
original active sheet and cell from before the routine was called. Any
ideas? Thanks!

Private Sub TextBox1_Change()

' This macro is called when the value in TextBox1 is changed.
' Textbox1 is a textbox in sheet 2 which is linked to a cell
' in sheet 2 whose value changes as certain cell values
' in sheet1 are changed by the user.

Dim OriginalCell As Range
Dim OriginalSheet As Worksheet

' Record the original active sheet and cell from before the macro
' was started so as to be able to return there when the macro
' has finished.

Set OriginalCell = ActiveCell
Set OriginalSheet = ActiveSheet

If Sheet2.Range("q10").Value 1 / 6 _
And (Sheet2.Range("q8").Value < 0.25 _
Or Sheet2.Range("q9").Value < 0.25) _
And Sheet2.Range("q8").Value 0 _
And Sheet2.Range("q9").Value 0 Then

' Call the subroutine which evaluates soil bearing pressures.

ResolveSoilPressures

' If the solver did not find valid results, let the user
' know about it.

If Abs(1 - Sheet2.Range("H16").Value / _
Sheet2.Range("K5").Value) 0.00001 _
Or Abs(1 - Sheet2.Range("h17").Value / _
Sheet2.Range("K6").Value) 0.00001 Then
MsgBox ("The solver failed to find an exact solution for" _
& Chr$(13) & _
"this footing. Please review to confirm the
results" _
& Chr$(13) & _
"of this design. If the results are invalid," _
& Chr$(13) & _
"please change footing parameters and rerun
design.")

End If

' Go back to the original active sheet and cell from before the
macro was called.

OriginalSheet.Activate
OriginalCell.Select

End Sub

Public Sub ResolveSoilPressures()

SolverReset
SolverLoad LoadArea:="Sheet2!$A$1:$A$9"
SolverOptions MaxTime:=100, Iterations:=100,
Precision:=0.0000000001, _
AssumeLinear:=False, StepThru:=False, Estimates:=1,
Derivatives:=1, _
SearchOption:=1, IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, _
AssumeNonNeg:=False
SolverOk SetCell:="Sheet2!$H$19", MaxMinVal:=1, ValueOf:="0",
ByChange:= _
"Sheet2!$H$5,Sheet2!$H$6,Sheet2!$H$11,Sheet2!$H$12 "
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

End Sub

If sheet 1 is the active sheet when the textbox_change event is called,
then the routine fails at "OriginalCell.Select" with the error, "Select
method of Range class failed". If sheet 2 is the active sheet when the
textbox_change event is called, it works fine returning the pointer to
the original cell from before the routine was called.

Thank you for any help.
Giz

Trouble returning to original active cell
 

If you replace this:

OriginalSheet.Activate

with

OriginalSheet.Select

does it help?

--
Jim
"Giznawz" wrote in message
oups.com...
The following is some code that I have written in Excel 2000 using
Windows 98. It works fine except that it does not return to the
original active sheet and cell from before the routine was called. Any
ideas? Thanks!

Private Sub TextBox1_Change()

' This macro is called when the value in TextBox1 is changed.
' Textbox1 is a textbox in sheet 2 which is linked to a cell
' in sheet 2 whose value changes as certain cell values
' in sheet1 are changed by the user.

Dim OriginalCell As Range
Dim OriginalSheet As Worksheet

' Record the original active sheet and cell from before the macro
' was started so as to be able to return there when the macro
' has finished.

Set OriginalCell = ActiveCell
Set OriginalSheet = ActiveSheet

If Sheet2.Range("q10").Value 1 / 6 _
And (Sheet2.Range("q8").Value < 0.25 _
Or Sheet2.Range("q9").Value < 0.25) _
And Sheet2.Range("q8").Value 0 _
And Sheet2.Range("q9").Value 0 Then

' Call the subroutine which evaluates soil bearing pressures.

ResolveSoilPressures

' If the solver did not find valid results, let the user
' know about it.

If Abs(1 - Sheet2.Range("H16").Value / _
Sheet2.Range("K5").Value) 0.00001 _
Or Abs(1 - Sheet2.Range("h17").Value / _
Sheet2.Range("K6").Value) 0.00001 Then
MsgBox ("The solver failed to find an exact solution for" _
& Chr$(13) & _
"this footing. Please review to confirm the
results" _
& Chr$(13) & _
"of this design. If the results are invalid," _
& Chr$(13) & _
"please change footing parameters and rerun
design.")

End If

' Go back to the original active sheet and cell from before the
macro was called.

OriginalSheet.Activate
OriginalCell.Select

End Sub

Public Sub ResolveSoilPressures()

SolverReset
SolverLoad LoadArea:="Sheet2!$A$1:$A$9"
SolverOptions MaxTime:=100, Iterations:=100,
Precision:=0.0000000001, _
AssumeLinear:=False, StepThru:=False, Estimates:=1,
Derivatives:=1, _
SearchOption:=1, IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, _
AssumeNonNeg:=False
SolverOk SetCell:="Sheet2!$H$19", MaxMinVal:=1, ValueOf:="0",
ByChange:= _
"Sheet2!$H$5,Sheet2!$H$6,Sheet2!$H$11,Sheet2!$H$12 "
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

End Sub

If sheet 1 is the active sheet when the textbox_change event is called,
then the routine fails at "OriginalCell.Select" with the error, "Select
method of Range class failed". If sheet 2 is the active sheet when the
textbox_change event is called, it works fine returning the pointer to
the original cell from before the routine was called.

Thank you for any help.
Giz

Trouble returning to original active cell
 

My spreadsheet file must have gotten corrupted somehow. When I opened
it to try your suggestion, excel crashed upon loading the file. I
tried reloading an earlier version of the file and retyped the code.
Now it works fine. Both "OriginalSheet.Select" and
"OriginalSheet.Activate" work in bringing the pointer back to its
original position. Wierd.

Do you have any ideas about how to make solver run in the back ground?
See my repost.

Thanks for your help!
Giz

Trouble returning to original active cell
 

I've never used Solver, sorry.

--
Jim
"Giznawz" wrote in message
oups.com...
| My spreadsheet file must have gotten corrupted somehow. When I opened
| it to try your suggestion, excel crashed upon loading the file. I
| tried reloading an earlier version of the file and retyped the code.
| Now it works fine. Both "OriginalSheet.Select" and
| "OriginalSheet.Activate" work in bringing the pointer back to its
| original position. Wierd.
|
| Do you have any ideas about how to make solver run in the back ground?
| See my repost.
|
| Thanks for your help!
| Giz
|