2

I've been asked at work to create an unified quotation request in Excel so I've set a list of checkboxes. The customer has to check whatever service he/she needs and click the button to update so the sheets related to de checked fields show up. The problem is that when there is more than one check, Excel throws the error 438, but every single checkbox works alone just fine.

And which is more strange, if I run the macro through the vba editor, it works great no matter how many checked fields there are. So basically the problem is when I run the macro with the update button. Here is the code:

Sub updateSheets()

Application.ScreenUpdating = False

'Hide all the sheets except for the menu (DATOS GENERALES)
Call hideAll

'If IFS is checked, one of the radio buttos in D20 must be checked
If ActiveSheet.cb1.Value = True And (ActiveSheet.ob1 = True Or ActiveSheet.ob2 = True Or ActiveSheet.ob3 = True) Then
    Worksheets("IFA - OPCIÓN 1").Visible = True
ElseIf ActiveSheet.cb1.Value = True And ActiveSheet.ob4.Value = True Then
    Worksheets("IFA - OPCIÓN 2").Visible = True
ElseIf ActiveSheet.cb1.Value = True And ActiveSheet.ob1 = False And ActiveSheet.ob2 = False And ActiveSheet.ob3 = False And ActiveSheet.ob4 = False Then
'Throws and error if IFA is checked but no IFA sub option is
    MsgBox ("ERROR: Ha marcado IFA por lo que debe elegir una de las siguientes opciones:" & vbCrLf & "- Opción 1. Productor Individual" & vbCrLf & "- Opción 1. Productor Individual Multisitio sin SGC" & vbCrLf & "- Opción 1. Productor Individual Multisitio con SGC" & vbCrLf & "- Opción 2. Grupo de Productores")
End If

'Makes visible all checked sheets
If ActiveSheet.cb2 = True Then Worksheets("ADD-ON TESCO NURTURE").Visible = True
If ActiveSheet.cb3 = True Then Worksheets("ADD-ON GRASP").Visible = True
If ActiveSheet.cb4 = True Then Worksheets("ADD-ON FSMA-PSR").Visible = True
If ActiveSheet.cb5 = True Then Worksheets("ADD-ON GGFSA").Visible = True
If ActiveSheet.cb6 = True Then Worksheets("ADD-ON COSTCO PRODUCE").Visible = True
If ActiveSheet.cb7 = True Then Worksheets("ADD-ON SPRING").Visible = True
If ActiveSheet.cb8 = True Then Worksheets("ADD-ON GLOBALGAP PLUS").Visible = True
If ActiveSheet.cb9 = True Then Worksheets("ADD-ON AH-DLL GROW").Visible = True
If ActiveSheet.cb10 = True Then Worksheets("LEAF Marque").Visible = True
If ActiveSheet.ob5 = True Then Worksheets("IFA - PHU").Visible = True

Worksheets("DATOS GENERALES").Activate

Application.ScreenUpdating = True

End Sub

Here is an image of the excel sheet: enter image description here

(ob1 ... ob4) are not used in this function

Thanks in advance!

matsrom
  • 55
  • 6
  • 1
    Start off by getting rid of `ActiveSheet` and use the actual worksheet. Read [How to avoid using select in VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). (It also helps with avoiding `ActiveSheet` – Ron Rosenfeld Nov 05 '20 at 11:40

0 Answers0