Excel VBA - Solver conflict?

D

Dick

I have written several VBA codes combined with a Macro using Solver. I
all seems to work for a while and then either freezes up or i get a
error message re the Solver code.

Any suggestions for correcting my problem?

Codes are as follows:
Sub Solver3()

'Solver3 Macro
' Macro recorded 4/24/2004 by xxx
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
ActiveSheet.Unprotect
SolverOk SetCell:="$BR$61", MaxMinVal:=3, ValueOf:="0", ByChange:
_
"$R$60,$AE$60,$AR$60,$BE$60,$BR$60"
SolverSolve
ActiveSheet.Protect DrawingObjects:=True, Contents:=True
Scenarios:=True
End Sub

Sub unpro()
Dim w As Worksheet
MsgBox ("Unprotect All Worksheets was called")
For Each w In Worksheets
w.Unprotect
Next
End Sub

Sub Protectz()
MsgBox ("Protect All Worksheets was called")
Dim w As Worksheet
For Each w In Worksheets
w.Protect
Next
End Sub

Private Sub cbActivateMonths_Click()
Range("R17").Select
Selection.AutoFill Destination:=Range("R17:R28")
Type:=xlFillDefault
Range("R17:R28").Select
Range("c1:E1").Select
End Sub
Sub ChangeBusinessEntity()

If Sheets("Index").Range("L16").Value = 1 Then
HideRows_Sole
Else
If Sheets("Index").Range("L16").Value = 2 Then
HideRows_Partner
Else
If Sheets("Index").Range("L16").Value = 3 Then
HideRows_LLC
Else
If Sheets("Index").Range("L16").Value = 4 Then
HideRows_SCorp
Else
If Sheets("Index").Range("L16").Value = 5 Then
HideRows_CCorp
End If
End If
End If
End If
End If

End Sub
Sub HideRows_Sole()

MsgBox ("You selected Sole Proprietor")

Sheets("B-Existing Business Info").Unprotect
Sheets("B-Existing Business Info").Rows.Hidden = False
Sheets("B-Existing Business Info").Rows("99:145").Hidden = True
Sheets("B-Existing Business Info").Protect DrawingObjects:=True
Contents:=True, Scenarios:=True

Sheets("K-Ownership").Unprotect
Sheets("K-Ownership").Rows.Hidden = False
Sheets("K-Ownership").Rows("19:498").Hidden = True
Sheets("K-Ownership").Protect DrawingObjects:=True, Contents:=True
Scenarios:=True

Sheets("M-Income & SET Tax").Unprotect
Sheets("M-Income & SET Tax").Rows.Hidden = False
Sheets("M-Income & SET Tax").Rows("31:96").Hidden = True
Sheets("M-Income & SET Tax").Protect DrawingObjects:=True
Contents:=True, Scenarios:=True

Sheets("R-Summary").Unprotect
Sheets("R-Summary").Rows.Hidden = False
Sheets("R-Summary").Rows("12:14").Hidden = True
Sheets("R-Summary").Rows("29:30").Hidden = True
Sheets("R-Summary").Protect DrawingObjects:=True, Contents:=True
Scenarios:=True

Sheets("S-Ratios").Unprotect
Sheets("S-Ratios").Rows.Hidden = False
Sheets("S-Ratios").Rows("35:49").Hidden = True
Sheets("S-Ratios").Protect DrawingObjects:=True, Contents:=True
Scenarios:=True

End Sub


Sub HideRows_Partner()

MsgBox ("You selected Partnership")

Sheets("B-Existing Business Info").Unprotect
Sheets("B-Existing Business Info").Rows.Hidden = False
Sheets("B-Existing Business Info").Rows("96:98").Hidden = True
Sheets("B-Existing Business Info").Rows("110:145").Hidden = True
Sheets("B-Existing Business Info").Protect DrawingObjects:=True
Contents:=True, Scenarios:=True

Sheets("K-Ownership").Unprotect
Sheets("K-Ownership").Rows.Hidden = False
Sheets("K-Ownership").Rows("5:20").Hidden = True
Sheets("K-Ownership").Rows("135:498").Hidden = True
Sheets("K-Ownership").Protect DrawingObjects:=True, Contents:=True
Scenarios:=True

Sheets("M-Income & SET Tax").Unprotect
Sheets("M-Income & SET Tax").Rows.Hidden = False
Sheets("M-Income & SET Tax").Rows("31:96").Hidden = True
Sheets("M-Income & SET Tax").Protect DrawingObjects:=True
Contents:=True, Scenarios:=True

Sheets("R-Summary").Unprotect
Sheets("R-Summary").Rows.Hidden = False
Sheets("R-Summary").Rows("12:14").Hidden = True
Sheets("R-Summary").Rows("29:30").Hidden = True
Sheets("R-Summary").Protect DrawingObjects:=True, Contents:=True
Scenarios:=True

Sheets("S-Ratios").Unprotect
Sheets("S-Ratios").Rows.Hidden = False
Sheets("S-Ratios").Rows("35:49").Hidden = True
Sheets("S-Ratios").Protect DrawingObjects:=True, Contents:=True
Scenarios:=True

End Sub

Sub HideRows_LLC()

MsgBox ("You selected Limited Liability Company")

Sheets("B-Existing Business Info").Unprotect
Sheets("B-Existing Business Info").Rows.Hidden = False
Sheets("B-Existing Business Info").Rows("96:109").Hidden = True
Sheets("B-Existing Business Info").Rows("121:145").Hidden = True
Sheets("B-Existing Business Info").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True

Sheets("K-Ownership").Unprotect
Sheets("K-Ownership").Rows.Hidden = False
Sheets("K-Ownership").Rows("5:136").Hidden = True
Sheets("K-Ownership").Rows("250:498").Hidden = True
Sheets("K-Ownership").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Sheets("M-Income & SET Tax").Unprotect
Sheets("M-Income & SET Tax").Rows.Hidden = False
Sheets("M-Income & SET Tax").Rows("31:96").Hidden = True
Sheets("M-Income & SET Tax").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True

Sheets("R-Summary").Unprotect
Sheets("R-Summary").Rows.Hidden = False
Sheets("R-Summary").Rows("12:14").Hidden = True
Sheets("R-Summary").Rows("29:30").Hidden = True
Sheets("R-Summary").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Sheets("S-Ratios").Unprotect
Sheets("S-Ratios").Rows.Hidden = False
Sheets("S-Ratios").Rows("35:49").Hidden = True
Sheets("S-Ratios").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

End Sub

Sub HideRows_SCorp()

MsgBox ("You selected S-Corporation")

Sheets("B-Existing Business Info").Unprotect
Sheets("B-Existing Business Info").Rows.Hidden = False
Sheets("B-Existing Business Info").Rows("96:120").Hidden = True
Sheets("B-Existing Business Info").Rows("134:145").Hidden = True
Sheets("B-Existing Business Info").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True

Sheets("K-Ownership").Unprotect
Sheets("K-Ownership").Rows.Hidden = False
Sheets("K-Ownership").Rows("5:251").Hidden = True
Sheets("K-Ownership").Rows("391:498").Hidden = True
Sheets("K-Ownership").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Sheets("M-Income & SET Tax").Unprotect
Sheets("M-Income & SET Tax").Rows.Hidden = False
Sheets("M-Income & SET Tax").Rows("5:31").Hidden = True
Sheets("M-Income & SET Tax").Rows("49:96").Hidden = True
Sheets("M-Income & SET Tax").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True

Sheets("R-Summary").Unprotect
Sheets("R-Summary").Rows.Hidden = False
Sheets("R-Summary").Rows("35").Hidden = True
Sheets("R-Summary").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Sheets("S-Ratios").Unprotect
Sheets("S-Ratios").Rows.Hidden = False
Sheets("S-Ratios").Rows("36:49").Hidden = True
Sheets("S-Ratios").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

End Sub


Sub HideRows_CCorp()

MsgBox ("You selected C-Corporation")

Sheets("B-Existing Business Info").Unprotect
Sheets("B-Existing Business Info").Rows.Hidden = False
Sheets("B-Existing Business Info").Rows("96:133").Hidden = True
Sheets("B-Existing Business Info").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True

Sheets("K-Ownership").Unprotect
Sheets("K-Ownership").Rows.Hidden = False
Sheets("K-Ownership").Rows("5:392").Hidden = True
Sheets("K-Ownership").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Sheets("M-Income & SET Tax").Unprotect
Sheets("M-Income & SET Tax").Rows.Hidden = False
Sheets("M-Income & SET Tax").Rows("5:49").Hidden = True
Sheets("M-Income & SET Tax").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True

Sheets("R-Summary").Unprotect
Sheets("R-Summary").Rows.Hidden = False
Sheets("R-Summary").Rows("33:38").Hidden = True
Sheets("R-Summary").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

End Sub

Private Sub cbSelectEntity_Change()
ChangeBusinessEntity
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Shared Workbooks & Code 1
Help with subscript out of range error. 3
Application.undo runtime error '1004' 1
Very Slow Worksheet Calculation 4
works in 2007 but not in 2010 0
worksheet change macro 3
Protect multiple selected sheets? 6
protect sheet with password 2

Top