G
Giznawz
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, and
screen updating does not seem to be turning off.
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
Application.ScreenUpdating = False
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 _
this footing." & 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
Application.ScreenUpdating = True
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.
Also, "Application.ScreenUpdating = False" does not seem to work. The
screen still blinks and blips as the calculations are run. I would
like solver to run without the user seeing the shift from screen 1 to
screen 2 and back again.
Thank you for any help.
Giz
Windows 98. It works fine except that it does not return to the
original active sheet and cell from before the routine was called, and
screen updating does not seem to be turning off.
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
Application.ScreenUpdating = False
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 _
this footing." & 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
Application.ScreenUpdating = True
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.
Also, "Application.ScreenUpdating = False" does not seem to work. The
screen still blinks and blips as the calculations are run. I would
like solver to run without the user seeing the shift from screen 1 to
screen 2 and back again.
Thank you for any help.
Giz