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