Hi.
I have a template spreadsheet with quarterly calculations
(formulas). I want to create a userform for the person to
click which quarter is the most current and then hit
enter. Based on the user's response, I want the macro to
replace the formulas for quarters that haven't happened
yet with zero values thus turning off the calculation.

I have made a userform with 5 radio buttons to click for
which quarter is the most recent. Choices are either
None, 1, 2, 3, or 4. However, the macro is replacing
every quarter's formula with a zero no matter which radio
button the user clicks. Can you help me figure out what
is wrong?

'Here is the procedure for when the enter button is hit on
the userform:

Public Sub Sales_User_Form_Enter_Button_Click()
If NoneButton Then CurrentQuarterlyKicker = None
If FirstQuarterButton Then CurrentQuarterlyKicker = 1
If SecondQuarterButton Then CurrentQuarterlyKicker = 2
If ThirdQuarterButton Then CurrentQuarterlyKicker = 3
If FourthQuarterButton Then CurrentQuarterlyKicker = 4
Unload Sales_Compensation_UserForm
End Sub

'Here is the Select case structure I used to turn off
formulas based on the choices made on the userform:

Range("A1").Select
Select Case CurrentQuarterlyKicker
Case 3: Range("J31, K31, L31, M31") = 0
Range("A1").Select
Case 2: Range("J30, J31, K30, K31, L30, L31, M30, M31") = 0
Range("A1").Select
Case 1: Range("J29, J30, J31, K29, K30, K31, L29, L30,
L31, M29, M30, M31") = 0
Range("A1").Select
Case None: Range("J28, J29, J30, J31, K28, K29, K30, K31,
L28, L29, L30, L31, M28, M29, M30, M31") = 0
Range("A1").Select
End Select

I thought if the choice of 4 was chosen then nothing would
happen. If choice 3 was chosen then the cells calculating
a 4Q amount would be turned to zero, etc. However, every
choice on the userform results in all formulas turned to
zero which should only happen for the choice of None.

Thank you.

Hi

If I read this correctly:
When you unload a form then all its settings and variables go down with it.
So either declare CurrentQuarterlyKicker public and outside the form, or
pass the variable before unloading, og simply hide the form instead of
unloading it.

Sales_Compensation_UserForm.Hide
' do stuff
Unload Sales_Compensation_UserForm

--
HTH. Best wishes Harald
Followup to newsgroup only please

"jrh" skrev i melding
...
Hi.
I have a template spreadsheet with quarterly calculations
(formulas). I want to create a userform for the person to
click which quarter is the most current and then hit
enter. Based on the user's response, I want the macro to
replace the formulas for quarters that haven't happened
yet with zero values thus turning off the calculation.

I have made a userform with 5 radio buttons to click for
which quarter is the most recent. Choices are either
None, 1, 2, 3, or 4. However, the macro is replacing
every quarter's formula with a zero no matter which radio
button the user clicks. Can you help me figure out what
is wrong?

'Here is the procedure for when the enter button is hit on
the userform:

Public Sub Sales_User_Form_Enter_Button_Click()
If NoneButton Then CurrentQuarterlyKicker = None
If FirstQuarterButton Then CurrentQuarterlyKicker = 1
If SecondQuarterButton Then CurrentQuarterlyKicker = 2
If ThirdQuarterButton Then CurrentQuarterlyKicker = 3
If FourthQuarterButton Then CurrentQuarterlyKicker = 4
Unload Sales_Compensation_UserForm
End Sub

'Here is the Select case structure I used to turn off
formulas based on the choices made on the userform:

Range("A1").Select
Select Case CurrentQuarterlyKicker
Case 3: Range("J31, K31, L31, M31") = 0
Range("A1").Select
Case 2: Range("J30, J31, K30, K31, L30, L31, M30, M31") = 0
Range("A1").Select
Case 1: Range("J29, J30, J31, K29, K30, K31, L29, L30,
L31, M29, M30, M31") = 0
Range("A1").Select
Case None: Range("J28, J29, J30, J31, K28, K29, K30, K31,
L28, L29, L30, L31, M28, M29, M30, M31") = 0
Range("A1").Select
End Select

I thought if the choice of 4 was chosen then nothing would
happen. If choice 3 was chosen then the cells calculating
a 4Q amount would be turned to zero, etc. However, every
choice on the userform results in all formulas turned to
zero which should only happen for the choice of None.

Thank you.