referencing names of a checkbox in a sub or function

[greg2935]

Hi, Hoping someone can help here, Ive got quite a major hassle and been searching for an answer for some weeks, (it may be because I am using the wrong keywords so apologise if this is trivial)!

I have 40 tests that are done on a piece of equipment, but not every test is done each time. I want to check those tests I want to do using a checkbox on a form (and paste them one on top of the other in a single worksheet), but also have the ability to get rid of tests I may have ticked inadvertently. However I do not know of any way to reference the name of the checkbox in a subroutine. i.e. how do you address a particular checkbox using a function?

    Dim ButtonName As CheckBox
    ButtonName = "Checkbox1"
    Call Function(ButtonName)
 
Sub Function(ByRef ButtonName As CheckBox)
 
    If UserForm.ButtonName.Value = True Then
       do something        
 End Sub

in other words I would like to see whether the value in Checkbox1 is ticked or not by using one function and changing the relevant variables to check others. Obviously I have 40 checkboxes; I dont want 40 different version of the above function such as:

Sub CheckBox1_Click()
 
    If UserForm.CheckBox1.Value = True Then
       do something        
 End Sub
 
Sub CheckBox2_Click()
 
    If UserForm.CheckBox2.Value = True Then
       do something        
 End Sub

I hope Ive made the problem clear

Greg

 

[ChrisM]

It is possible to write common code for controls. Does the "Do Something" bit vary depending on which checkbox is checked?

 

[phxsportz]

Are you asking how to cycle through them all at once and check the values ? You could assign a button to process them all at once...

Sub ProcessButton_Click()
   For x = 1 to 40 ' Number of checkboxes
      if Controls("Checkbox" & x) Then
          Do Something
      End If
   Next x
End Sub

 

[greg2935]

Hi,

Thanks for answering, Im not trying to cycle through each checkbox in turn, I need to address specific checkboxes depending which checkbox I click. The "do Something" is the same for each checkbox but it acts on different parts of the workbook (these are defined using string variables and can be happily passed to the function).

Essentially, I have a load of tests, (basically data) each defined by a local name, taking up a specific area on the worksheet Tests. When you click a specific checkbox, a specific area in the worksheet; Tests (defined by a specific local name and corresponding to that specific test) is copied and pasted into the first blank cell of the "Master" worksheet. Another name is defined for the pasted material so if I uncheck the checkbox, I can select the particular test on the master worksheet and delete it. There are also a number of other checks made like whether text has changed for that particular test (so you dont wipe out a test by mistake etc).

The problem is that I need to be able to address that specific checkbox in a function by using some sort of variable. This is trivial in most programming languages but it has got me stumped in VBA as you do not seem to be able to simply do:

var1 as string

function(var1 as string)
{
    var1.value = "true";
   return 0;
}

Greg

 

[phxsportz]

So you just want to pass , say "1" to your Do Something Procedure when Checkbox1 is clicked...

How about..

Private Sub checkbox1_click()
   If checkbox1 Then Call CBProcess(1)
End Sub
 
Private Sub CBProcess(arg as variant)
  Dim MyControl as Control
  set mycontrol = Controls("Checkbox" & arg)
 
  'Do something with it..
End Sub