Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Excel VBA - Solver conflict?
 
 
LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Excel VBA - Solver conflict?
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


---
Message posted from http://www.ExcelForum.com/

Reply With QuoteReply With Quote
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Threaded Mode Threaded Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Urgent: Excel 2003 - 2007 conflict! Burak Excel Discussion (Misc queries) 4 July 14th 09 01:22 PM
Conflict between Excel and Outlook Richard Excel Discussion (Misc queries) 3 July 6th 09 05:29 PM
Excel 2007 and Name Conflict Issue rykkim Setting up and Configuration of Excel 5 August 5th 08 04:29 AM
Excel 2007 Name Conflict Reeza Excel Discussion (Misc queries) 5 June 20th 07 04:33 PM
Excel 2000/2003 conflict nyvirus1 Excel Discussion (Misc queries) 0 July 22nd 05 01:45 PM


All times are GMT +1. The time now is 03:08 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement