View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
[email protected] Spencer.Sadkin@gmail.com is offline
external usenet poster
 
Posts: 42
Default Weird Happenings

Sorry, should have read:

Adding the following code to the dropdown2_change fixes the issue but
its sloppy sloppy programming

Sub dropdown2_change()
'code happens
sheets("anothersheetinworkbook").activate
sheets("mainsheet").actiavte
End Sub

This is the one that produces the weird thing:

Sub dropdown2_change()
'code happens
sheets("mainsheet").actiavte
End Sub

and you are right, i shouldnt have to activate it, but i could not
figure out why it got deactivated (sorta)...

basicly the second sub listed calls the first when it says
cmblocation.value="New"
it is a lot of code but here it is :

Private Sub cmblocation_Change()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
lscreenupdate = lscreenupdate + 1

Sheets("io").Range("u101") = Format(Trim(Left(cmblocation.Value,
2)), "###")
If cmblocation.Value = "New" Then
Range("expdate") = "=date(year(effdate)
+1,month(effdate),day(effdate))"
Range("quotedate") = "=today()"

On Error Resume Next
rmvbutton.Enabled = False
On Error GoTo 0
Else

On Error Resume Next
rmvbutton.Enabled = True
On Error GoTo 0
Range("expdate") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 1) '
expiration date
Range("effdate") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 2)
'effective date
Range("quotedate") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 3) '
quote date
Sheets("quick rate").Range("address").Cells(1) =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 56)
'street address
Sheets("quick rate").Range("city").Cells(1) =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 57)
'city
Sheets("quick rate").Range("state") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 58)
'state
Sheets("quick rate").Range("zip") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 59) 'zip
Sheets("quick rate").Range("personalproperty") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 7)
'Personal Property
Sheets("quick rate").Range("buildingamount") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 8)
'building
cmbform.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 9) 'form
type
cmbexexp.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 10)
'extra expense
Sheets("io").Range("e7") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 11)
'business income amount
cmbbusincco.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 12)
'business income coinsurance
cmbtheft.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 13)
'including theft
cmbownerocc.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 14)
'owner occupied
cmbexwind.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 15) '
Ex. Wind
cmbconstruction.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 16)
'construction type
cmbprotclass.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 17)
'protection class
ComboBox16.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 18)
'territory
cmbwindhail.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 19)
'wind/hail ded.
ComboBox18.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 20)
'building ded.
'ComboBox19.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 21)
'Personal Property ded.
ComboBox14.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 22)
'coinsurance
ComboBox20.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 23) 'bg2
territory
ComboBox12.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 24) 'bg2
symbol
ComboBox6.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 25)
'class
Sheets("io").Range("a38") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 26)
'class code
ComboBox11.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 27)
'risk type
cmbdedbasis.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 28)
'ded. basis
ComboBox7.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 29)
'class type
ComboBox10.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 30)
'county
ToggleButton1.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 62) '
toggle1 value
ToggleButton2.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 63) '
toggle1 value
ToggleButton3.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 64) '
toggle1 value
ToggleButton4.Value =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 65) '
toggle1 value
If ToggleButton1.Value = False Then _
Range("bg1blc") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 31) 'bg1
building lc
If ToggleButton2.Value = False Then _
Range("bg1clc") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 32) 'bg1
Personal Property lc
If ToggleButton3.Value = False Then _
Range("bg2blc") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 33) '
bg2 building lc
If ToggleButton4.Value = False Then _
Range("bg2clc") =
Sheets("records").Cells(Sheets("io").Range("u101") .Value + 1, 34) '
bg2 Personal Property lc

End If
Sheets("quote").Activate
Sheets("quick rate").Activate
lscreenupdate = lscreenupdate - 1
If lscreenupdate = 0 Then Application.ScreenUpdating = True
End Sub



Private Sub cmbinsured_Change()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
lscreenupdate = lscreenupdate + 1
If noevents = True Then
lscreenupdate = lscreenupdate - 1
If lscreenupdate = 0 Then Application.ScreenUpdating = True
Exit Sub
End If

If updatecnt < 0 Then
ans = MsgBox("Changes to insureds quote have been made." &
vbNewLine & "Do you want to save changes?", vbYesNo + vbQuestion,
"Save Notice")
updatecnt = 0
If ans = vbYes Then
Call svquote_Click
End If
End If
If cmbinsured.Value = "New" Then
Range("name") = " "
Else
Range("name") = cmbinsured.Value
End If
If noevents = True Then
lscreenupdate = lscreenupdate - 1
If lscreenupdate = 0 Then Application.ScreenUpdating = True
Exit Sub
End If



If cmbinsured.Value = "New" Then

Application.ScreenUpdating = False
Range("name").Locked = False
Set rng = Sheets("quick rate").Range("name")
Call bgcolorred
Call property
If noevents = True Then
lscreenupdate = lscreenupdate - 1
If lscreenupdate = 0 Then Application.ScreenUpdating =
True
Exit Sub
End If
cmbinsured.Value = "New"
Call quotebuttons
cmblocation.Value = "New"
cmbinsured.Value = "New"
With Sheets("quick rate")
.Range("personalproperty").ClearContents
.Range("buildingamount").ClearContents
.Range("bincamount").ClearContents
.Range("name").ClearContents
.Range("address").ClearContents
.Range("city").ClearContents
.Range("state") = "FL"
.Range("zip").ClearContents
End With
ToggleButton1.Value = True
ToggleButton2.Value = True
ToggleButton3.Value = True
ToggleButton4.Value = True

Else
Application.ScreenUpdating = False
Set rng = Sheets("quick rate").Range("name")
Call bgcolorbgreen
Range("address").Select
Range("name").Locked = True

Call ImportInsuredData
Select Case Sheets("records").Cells(2, 61)
Case "P"
policybuttons
Case "Q"
quotebuttons
End Select
End If

cnt = WorksheetFunction.CountA(Sheets("records").Range(" D:D"))
Sheets("io").Range("u4:u100").ClearContents
For i = 2 To cnt
Sheets("io").Cells(i + 2, 21) = i - 1 & " - " &
Sheets("records").Cells(i, 56) & ", " & _
Sheets("records").Cells(i, 57) & ", " &
Sheets("records").Cells(i, 58) & _
" " & Sheets("records").Cells(i, 59)
Next

cmblocation.ListIndex = 0
Sheets("quick rate").Activate
lscreenupdate = lscreenupdate - 1
If lscreenupdate = 0 Then Application.ScreenUpdating = True
End Sub