Code was working in earlier version of Excel

[akom]

I needed to move to Excel 2013 with this form, which broke it. Now I get an error that does not like the ".ControlFormat.Value" part of the code that checks the state of the checkbox. Can someone direct me to a different way to accomplish the same type of task. I have been trying for a while now without much success. Here is my code:

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim Fee1, Fee2, Fee3, Fee4 As Double
  
    Set Target = Range("F35")

    If Not Intersect(Target, Range("F35")) Is Nothing Then
'**********************************
'IF FEES CHANGE - MODIFY THE FEE VARIABLES BELOW, ACCORDINGLY.
Fee1 = 40          'Fee for <= 60lbs.
Fee2 = 79          'Fee for > 60 and < 125lbs.
Fee3 = 118          'Fee for > 125 and < 187lbs.
Fee4 = 158          'Fee for > 187lbs.
'***********************************
'**********************************
'IF THE $40 MUNICIPAL ASSISTANCE BOX IS CHECKED, THESE ARE FEES APPLIED
'IF FEES CHANGE - MODIFY THE FEE VARIABLES BELOW, ACCORDINGLY.
Fee5 = 0          'Fee for <= 60lbs.
Fee6 = 39          'Fee for > 60 and < 125lbs.
Fee7 = 78          'Fee for > 125 and < 187lbs.
Fee8 = 118          'Fee for > 187lbs.
'***********************************
If ActiveSheet.Shapes("Check Box 3").ControlFormat.Value <> 1 Then

    Select Case Target
    Case 0 To 60
    ActiveSheet.Range("F36").Value = Fee1
    Case 60 To 125
    ActiveSheet.Range("F36").Value = Fee2
    Case 125 To 187
    ActiveSheet.Range("F36").Value = Fee3
    Case Is > 187
    ActiveSheet.Range("F36").Value = Fee4
End Select
End If

If ActiveSheet.Shapes("Check Box 3").ControlFormat.Value = 1 Then

    Select Case Target
    Case 0 To 60
    ActiveSheet.Range("F36").Value = Fee5
    Case 60 To 125
    ActiveSheet.Range("F36").Value = Fee6
    Case 125 To 187
    ActiveSheet.Range("F36").Value = Fee7
    Case Is > 187
    ActiveSheet.Range("F36").Value = Fee8
End Select
End If
End If

End Sub

Thank you,
Al

POST MOVED BY MOD

 

[Chihiro]

Try checking like below.

If ActiveSheet.Shapes("Check Box 3").OLEFormat.Object.Value <> 1 Then
"Some operation"
End If

 

[akom]

Thank you for your response.
I just tried your suggestion and got a Run-time error '1004': Unable to get the Value property of the Checkbox class.

 

[Hui]

That is working in Excel 2016?



Can you post the whole file ?

 

[Hui]

Is it a Form Control or an Active X Control ?

 

[NARAYANK991]

Hi ,

What does Intellisense display when you type the following :

ActiveSheet.Shapes("Check Box 3").

If nothing is displayed , try with the following :

Me.Shapes("Check Box 3").

Narayan

 

[Chihiro]

Hmm, may be check your tool references as well. In general codes written in older version should work in newer version... But, there may be missing reference depending on your set up.

 

[akom]

Thank you all for the great suggestions... Hui, the Checkbox is an ActiveX Control, not a Form Control. If it's working in 2016, it should work in 2013.

Narayan, I tried the "Me.Shapes("Check Box 3")..." but received the same error as before.

Chihiro, I also am wondering if I am missing some reference and played around a little bit with that by adding a few, trying them, and if they didn't work disabling them again. Just not sure which ones I might need to get it to work.

I have attached both the older file that I made a copy of and the one I have converted to an .xlsm.

 

[Hui]

I used a form control in 2016

 

[Chihiro]

Here, see attached.

I think the culprit here was "ActiveSheet" and "Set Target =" line.
No need to set these (I think) since, these are pre-defined in WorkSheet_Change event.

 

[akom]

I believe it was a combination of the two... I changed the control to a Form control and I commented out the "Set Target =" line, and it started working.

Thank you all so very much for helping me get this thing back on track, I really appreciate your help. Have a wonderful day!

Al

 

[NARAYANK991]

Hi ,

The following line is the culprit :

ActiveSheet.Range("F36").Value = Fee1

The Worksheet_Change procedure is an event procedure , which means it is automatically executed when ever a worksheet cell is changed by user action or by code.

Thus , when ever a worksheet range is to be changed within code , it should always be done only if the line of code is surrounded by the following two lines of code :

Application.EnableEvents = False

Application.EnableEvents = True

What this does is ensure that the Worksheet_Change event is not triggered by the procedure itself , since this can create an infinite loop.

Narayan