mardi 20 septembre 2022

Need solution to avoid repeating very similar code 25 times

I have a useform with 6 questions where the user must select an option using a tickbox. The options available to users varies depending on the question so some questions have 5 options, others have less. Therefore the number of tickboxes varies. I've names the tickboxes CB1 to CB25 and currently I have a sub routine for every tickbox and I'm looking for a way of having one sub routine that covers all options. The existing code for each tickbox is as below.

Sub Cb1_Click()

If Range("d27").Value = 0 Then

If cb1 = True Then
cb2 = False
cb3 = False

Controls("F_selected").Value = Controls("F_prior").Value

    If Controls("F_selected").Value <> "" And Controls("F_selected").Value <> "n/a" And Controls("F_prior").Value <> "" And Controls("F_prior").Value <> 0 And Controls("F_prior").Value <> "0.00" And Controls("F_prior").Value <> "0.00%" Then
   
    Controls("F_change").Value = (Format(Controls("F_selected").Value, "standard") / Format(Controls("F_prior").Value, "standard") - 1)
    
    Else
    
    Controls("F_change").Value = ""

    End If

    Controls("F_change").Value = Format(Controls("F_change").Value, "percent")

    Range("L11") = Controls("F_selected").Value

update_form

End If

End If


End Sub

Note: the below example has 3 clickboxes but some of the questions have 5.

As a first port of call, I've tried to create a standard sub routine and a call to replicate the above code. This is what I have:

    Sub checkboxchg(var0 As Object, var1 As Object, var2 As Object, var3 As Object, var4 As String, var5 As String, var6 As String)
    
    Sub var1_Click()
    
    
    If Range("d27").Value = 0 Then
    
    If var1 = True Then
    var2 = False
    var3 = False
    
    Controls(var4).Value = Controls(var5).Value
    If Controls(var4).Value <> "" And Controls(var4).Value <> "n/a" And Controls(var5).Value <> "" And Controls(var5).Value <> 0 And Controls(var5).Value <> "0.00" And Controls(var5).Value <> "0.00%" Then
   
    Controls(var6).Value = (Format(Controls(var4).Value, "standard") / Format(Controls(var5).Value, "standard") - 1)
    
    Else
    
    Controls(var6).Value = ""

    End If

    Controls(var6).Value = Format(Controls(var6).Value, "percent")

    Range("L11") = Controls(var4).Value

update_form

End If

End If

End Sub

End Sub

I use the following Call to reproduce the original code but I'm getting errors.

Call checkboxchg(cb1, cb2, cb3, "ll_f_m_sel", "ll_f_m_prior", "ll_f_m_chg")

I think perhaps I'm defining the cb1_click() incorrectly but even when I change this I'm getting errors elsewhere so I'm not sure what's happing. Any help would be appreciated.




Aucun commentaire:

Enregistrer un commentaire