Home > Enterprise >  Simplify vba code with lots of repetitive operations
Simplify vba code with lots of repetitive operations

Time:09-19

I've created a userform with a large number of textboxes and tick boxes. When the userform is initialised, data is loaded into the textboxes and the tick box values are defined by values specified in one of the workbook tabs.

Being a beginner VBA coder, I have written the code with lots of repetition. For example, I include the code below for every textbox that is populated by a number.

Controls("ll_f_m_prior").Value = Format(Range("ll_f_m_prior_p"), "standard")

If Controls("ll_f_m_prior").Value = "n/a" Or Controls("ll_f_m_prior").Value = "" Then

Controls("ll_f_m_prior").Enabled = False

cb1.Enabled = False

End If

` I have 25 textboxes where the above code is repeated. I was looking for a way to call a function using two dynamic variables. In the above example, the two variables are ll_f_m_prior and cb1.

I tried this code but it doesn't work

Sub error1(var1 As String, var2 As String)

Controls(var1).Value = Format(Range(var1), "standard")

If Controls(var1).Value = "n/a" Or Controls(var1).Value = "" Then

Controls(var1).Enabled = False

x = var2 & ".Enabled"

x = False

End If

End Sub

and calling the subroutine using the function:

Call error1("ll_f_m_prior_p", cb1)

CodePudding user response:

When you call the sub with Call error1("ll_f_m_prior_p", cb1) cb1isn't a string. Yet the sub expects a string. So if you change the second variable to act as an object instead, that should make things easier.

Sub error1(var1 As String, var2 As Object)

Controls(var1).Value = Format(Range(var1), "standard")

If Controls(var1).Value = "n/a" Or Controls(var1).Value = "" Then
   Controls(var1).Enabled = False
   var2.Enabled = False
End If

End Sub
  • Related