ComboBox Auto-Calculating

[jennsandoval]

Hello!

This is the first time I have posted to a forum so bear with me!

I have a project that I am working on that contains three comboboxes. One combobox is used to select a user while the other two comboboxes are used as a date range. The comboboxes are coded right now to hide specific rows in a table to show the date range selected from the comboboxes.

I am trying to figure out a way in VBA to prevent the auto-calculating from occurring with the date range comboboxes. I have in my code already the application.calculation = xlCalculationManual and a macro assigned to a button for "refresh" that changes when the button is pressed. However, the date range comboxes are still auto-calculating when I select them in the list. Can anyone help me with my code to prevent the auto-calculating from occurring or is there a better way with the date ranges for comboboxes to work better than what I have? Thank you for help!

My code is posted below:

Private Sub Workbook_Open()

Application.Calculation = xlCalculationManual

End Sub
Sub Macro1()

Calculate
End Sub
Private Sub ComboBox3_Change()

ComboBox3.Value = Format(ComboBox3.Value, "m/d/yy")
Select Case ComboBox3.Value

Case "5/10/15"
Rows("40:52").Hidden = False

Case "5/17/15"
Rows("40:52").Hidden = False
Rows("40").Hidden = True


Case "5/24/15"
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True


Case "5/31/15"
Rows("40:52").Hidden = False
Rows("40:42").Hidden = True


Case "6/7/15"
Rows("40:52").Hidden = False
Rows("40:43").Hidden = True


Case "6/14/15"
Rows("40:52").Hidden = False
Rows("40:44").Hidden = True


Case "6/21/15"
Rows("40:52").Hidden = False
Rows("40:45").Hidden = True


Case "6/28/15"
Rows("40:52").Hidden = False
Rows("40:46").Hidden = True


Case "7/5/15"
Rows("40:52").Hidden = False
Rows("40:47").Hidden = True


Case "7/12/15"
Rows("40:52").Hidden = False
Rows("40:48").Hidden = True


Case "7/19/15"
Rows("40:52").Hidden = False
Rows("40:49").Hidden = True


Case "7/26/15"
Rows("40:52").Hidden = False
Rows("40:50").Hidden = True


Case "8/2/15"
Rows("40:52").Hidden = False
Rows("40:51").Hidden = True
End Select
End Sub
Private Sub ComboBox2_Change()
ComboBox2.Value = Format(ComboBox2.Value, "m/d/yy")

If ComboBox2.Value = "5/10/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("41:52").Hidden = True
End If


ElseIf ComboBox2.Value = "5/17/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("42:52").Hidden = True

ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True
Rows("42:52").Hidden = True

End If

ElseIf ComboBox2.Value = "5/24/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("43:52").Hidden = True

ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True
Rows("43:52").Hidden = True

ElseIf ComboBox3.Value = "5/24/15" Then
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True
Rows("43:52").Hidden = True

End If

ElseIf ComboBox2.Value = "5/31/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("44:52").Hidden = True

ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True
Rows("44:52").Hidden = True

ElseIf ComboBox3.Value = "5/24/15" Then
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True
Rows("44:52").Hidden = True

ElseIf ComboBox3.Value = "5/31/15" Then
Rows("40:52").Hidden = False
Rows("40:42").Hidden = True
Rows("44:52").Hidden = True

End If

ElseIf ComboBox2.Value = "6/7/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("45:52").Hidden = True

ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True
Rows("45:52").Hidden = True

ElseIf ComboBox3.Value = "5/24/15" Then
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True
Rows("45:52").Hidden = True

ElseIf ComboBox3.Value = "5/31/15" Then
Rows("40:52").Hidden = False
Rows("40:42").Hidden = True
Rows("45:52").Hidden = True

ElseIf ComboBox3.Value = "6/7/15" Then
Rows("40:52").Hidden = False
Rows("40:43").Hidden = True
Rows("45:52").Hidden = True

End If


ElseIf ComboBox2.Value = "6/14/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("46:52").Hidden = True

ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True
Rows("46:52").Hidden = True

ElseIf ComboBox3.Value = "5/24/15" Then
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True
Rows("46:52").Hidden = True

ElseIf ComboBox3.Value = "5/31/15" Then
Rows("40:52").Hidden = False
Rows("40:42").Hidden = True
Rows("46:52").Hidden = True

ElseIf ComboBox3.Value = "6/7/15" Then
Rows("40:52").Hidden = False
Rows("40:43").Hidden = True
Rows("46:52").Hidden = True

ElseIf ComboBox3.Value = "6/14/15" Then
Rows("40:52").Hidden = False
Rows("40:44").Hidden = True
Rows("46:52").Hidden = True

End If

ElseIf ComboBox2.Value = "6/21/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("47:52").Hidden = True

ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True
Rows("47:52").Hidden = True

ElseIf ComboBox3.Value = "5/24/15" Then
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True
Rows("47:52").Hidden = True

ElseIf ComboBox3.Value = "5/31/15" Then
Rows("40:52").Hidden = False
Rows("40:42").Hidden = True
Rows("47:52").Hidden = True

ElseIf ComboBox3.Value = "6/7/15" Then
Rows("40:52").Hidden = False
Rows("40:43").Hidden = True
Rows("47:52").Hidden = True

ElseIf ComboBox3.Value = "6/14/15" Then
Rows("40:52").Hidden = False
Rows("40:44").Hidden = True
Rows("47:52").Hidden = True

ElseIf ComboBox3.Value = "6/21/15" Then
Rows("40:52").Hidden = False
Rows("40:45").Hidden = True
Rows("47:52").Hidden = True

End If


ElseIf ComboBox2.Value = "6/28/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("48:52").Hidden = True
ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True
Rows("48:52").Hidden = True

ElseIf ComboBox3.Value = "5/24/15" Then
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True
Rows("48:52").Hidden = True

ElseIf ComboBox3.Value = "5/31/15" Then
Rows("40:52").Hidden = False
Rows("40:42").Hidden = True
Rows("48:52").Hidden = True

ElseIf ComboBox3.Value = "6/7/15" Then
Rows("40:52").Hidden = False
Rows("40:43").Hidden = True
Rows("48:52").Hidden = True

ElseIf ComboBox3.Value = "6/14/15" Then
Rows("40:52").Hidden = False
Rows("40:44").Hidden = True
Rows("48:52").Hidden = True

ElseIf ComboBox3.Value = "6/21/15" Then
Rows("40:52").Hidden = False
Rows("40:45").Hidden = True
Rows("48:52").Hidden = True

ElseIf ComboBox3.Value = "6/28/15" Then
Rows("40:52").Hidden = False
Rows("40:46").Hidden = True
Rows("48:52").Hidden = True

End If


ElseIf ComboBox2.Value = "7/5/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("49:52").Hidden = True

ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True
Rows("49:52").Hidden = True

ElseIf ComboBox3.Value = "5/24/15" Then
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True
Rows("49:52").Hidden = True

ElseIf ComboBox3.Value = "5/31/15" Then
Rows("40:52").Hidden = False
Rows("40:42").Hidden = True
Rows("49:52").Hidden = True

ElseIf ComboBox3.Value = "6/7/15" Then
Rows("40:52").Hidden = False
Rows("40:43").Hidden = True
Rows("49:52").Hidden = True

ElseIf ComboBox3.Value = "6/14/15" Then
Rows("40:52").Hidden = False
Rows("40:44").Hidden = True
Rows("49:52").Hidden = True

ElseIf ComboBox3.Value = "6/21/15" Then
Rows("40:52").Hidden = False
Rows("40:45").Hidden = True
Rows("49:52").Hidden = True

ElseIf ComboBox3.Value = "6/28/15" Then
Rows("40:52").Hidden = False
Rows("40:46").Hidden = True
Rows("49:52").Hidden = True

ElseIf ComboBox3.Value = "7/5/15" Then
Rows("40:52").Hidden = False
Rows("40:47").Hidden = True
Rows("49:52").Hidden = True

End If


ElseIf ComboBox2.Value = "7/12/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("50:52").Hidden = True

ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True
Rows("50:52").Hidden = True

ElseIf ComboBox3.Value = "5/24/15" Then
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True
Rows("50:52").Hidden = True

ElseIf ComboBox3.Value = "5/31/15" Then
Rows("40:52").Hidden = False
Rows("40:42").Hidden = True
Rows("50:52").Hidden = True

ElseIf ComboBox3.Value = "6/7/15" Then
Rows("40:52").Hidden = False
Rows("40:43").Hidden = True
Rows("50:52").Hidden = True

ElseIf ComboBox3.Value = "6/14/15" Then
Rows("40:52").Hidden = False
Rows("40:44").Hidden = True
Rows("50:52").Hidden = True

ElseIf ComboBox3.Value = "6/21/15" Then
Rows("40:52").Hidden = False
Rows("40:45").Hidden = True
Rows("50:52").Hidden = True

ElseIf ComboBox3.Value = "6/28/15" Then
Rows("40:52").Hidden = False
Rows("40:46").Hidden = True
Rows("50:52").Hidden = True

ElseIf ComboBox3.Value = "7/5/15" Then
Rows("40:52").Hidden = False
Rows("40:47").Hidden = True
Rows("50:52").Hidden = True

ElseIf ComboBox3.Value = "7/12/15" Then
Rows("40:52").Hidden = False
Rows("40:48").Hidden = True
Rows("50:52").Hidden = True

End If


ElseIf ComboBox2.Value = "7/19/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("51:52").Hidden = True

ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True
Rows("51:52").Hidden = True

ElseIf ComboBox3.Value = "5/24/15" Then
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True
Rows("51:52").Hidden = True

ElseIf ComboBox3.Value = "5/31/15" Then
Rows("40:52").Hidden = False
Rows("40:42").Hidden = True
Rows("51:52").Hidden = True

ElseIf ComboBox3.Value = "6/7/15" Then
Rows("40:52").Hidden = False
Rows("40:43").Hidden = True
Rows("51:52").Hidden = True

ElseIf ComboBox3.Value = "6/14/15" Then
Rows("40:52").Hidden = False
Rows("40:44").Hidden = True
Rows("51:52").Hidden = True

ElseIf ComboBox3.Value = "6/21/15" Then
Rows("40:52").Hidden = False
Rows("40:45").Hidden = True
Rows("51:52").Hidden = True

ElseIf ComboBox3.Value = "6/28/15" Then
Rows("40:52").Hidden = False
Rows("40:46").Hidden = True
Rows("51:52").Hidden = True

ElseIf ComboBox3.Value = "7/5/15" Then
Rows("40:52").Hidden = False
Rows("40:47").Hidden = True
Rows("51:52").Hidden = True

ElseIf ComboBox3.Value = "7/12/15" Then
Rows("40:52").Hidden = False
Rows("40:48").Hidden = True
Rows("51:52").Hidden = True

ElseIf ComboBox3.Value = "7/19/15" Then
Rows("40:52").Hidden = False
Rows("40:49").Hidden = True
Rows("51:52").Hidden = True

End If


ElseIf ComboBox2.Value = "7/26/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False
Rows("52").Hidden = True

ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True
Rows("52").Hidden = True

ElseIf ComboBox3.Value = "5/24/15" Then
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True
Rows("52").Hidden = True

ElseIf ComboBox3.Value = "5/31/15" Then
Rows("40:52").Hidden = False
Rows("40:42").Hidden = True
Rows("52").Hidden = True

ElseIf ComboBox3.Value = "6/7/15" Then
Rows("40:52").Hidden = False
Rows("40:43").Hidden = True
Rows("52").Hidden = True

ElseIf ComboBox3.Value = "6/14/15" Then
Rows("40:52").Hidden = False
Rows("40:44").Hidden = True
Rows("52").Hidden = True

ElseIf ComboBox3.Value = "6/21/15" Then
Rows("40:52").Hidden = False
Rows("40:45").Hidden = True
Rows("52").Hidden = True

ElseIf ComboBox3.Value = "6/28/15" Then
Rows("40:52").Hidden = False
Rows("40:46").Hidden = True
Rows("52").Hidden = True

ElseIf ComboBox3.Value = "7/5/15" Then
Rows("40:52").Hidden = False
Rows("40:47").Hidden = True
Rows("52").Hidden = True

ElseIf ComboBox3.Value = "7/12/15" Then
Rows("40:52").Hidden = False
Rows("40:48").Hidden = True
Rows("52").Hidden = True

ElseIf ComboBox3.Value = "7/19/15" Then
Rows("40:52").Hidden = False
Rows("40:49").Hidden = True
Rows("52").Hidden = True

ElseIf ComboBox3.Value = "7/26/15" Then
Rows("40:52").Hidden = False
Rows("40:50").Hidden = True
Rows("52").Hidden = True

End If


ElseIf ComboBox2.Value = "8/2/15" Then
If ComboBox3.Value = "5/10/15" Then
Rows("40:52").Hidden = False

ElseIf ComboBox3.Value = "5/17/15" Then
Rows("40:52").Hidden = False
Rows("40").Hidden = True

ElseIf ComboBox3.Value = "5/24/15" Then
Rows("40:52").Hidden = False
Rows("40:41").Hidden = True

ElseIf ComboBox3.Value = "5/31/15" Then
Rows("40:52").Hidden = False
Rows("40:42").Hidden = True

ElseIf ComboBox3.Value = "6/7/15" Then
Rows("40:52").Hidden = False
Rows("40:43").Hidden = True

ElseIf ComboBox3.Value = "6/14/15" Then
Rows("40:52").Hidden = False
Rows("40:44").Hidden = True

ElseIf ComboBox3.Value = "6/21/15" Then
Rows("40:52").Hidden = False
Rows("40:45").Hidden = True

ElseIf ComboBox3.Value = "6/28/15" Then
Rows("40:52").Hidden = False
Rows("40:46").Hidden = True

ElseIf ComboBox3.Value = "7/5/15" Then
Rows("40:52").Hidden = False
Rows("40:47").Hidden = True

ElseIf ComboBox3.Value = "7/12/15" Then
Rows("40:52").Hidden = False
Rows("40:48").Hidden = True

ElseIf ComboBox3.Value = "7/19/15" Then
Rows("40:52").Hidden = False
Rows("40:49").Hidden = True

ElseIf ComboBox3.Value = "7/26/15" Then
Rows("40:52").Hidden = False
Rows("40:50").Hidden = True

ElseIf ComboBox3.Value = "8/2/15" Then
Rows("40:52").Hidden = False
Rows("40:51").Hidden = True

End If
Application.ScreenUpdating = False

End If
End Sub

 

[Kuljack]

Try adding Application.Enableevents = False/True at the beginning/end of each macro. I think it's occuring because you have the events within your code.

 

[jennsandoval]

Try adding Application.Enableevents = False/True at the beginning/end of each macro. I think it's occuring because you have the events within your code.

Thank you for your reply!

I tried adding that line of code in the macro, as well as the combobox3_change and combobox2_change section but that did not seem to work.

Any other suggestions?

 

[Kuljack]

Hmm, the only other thing I can think of... I am limited in my wisdom...

Is adding a blank statement at the beginning of each combo box that directs it to the end, skipping all date logic in the process.

ComboBox3.Value = Format(ComboBox3.Value, "m/d/yy")
Select Case ComboBox3.Value

Case ""
goto end

Case "5/10/15"
Rows("40:52").Hidden = False

bottom of code

end

 

[jennsandoval]

Thank you again for the response! I did try adding your suggestion to the code and unfortunately it did not work again. I might need to re-do the combobox codes at this point without the sub being sub combobox3_change(). If you happen to think of anything else, I greatly appreciate it!