Use strings for object names in VBA

Question

I got a user form with six checkboxes, named chk1, chk2, ..., chk6, which i need to go through and list the marked ones. I would like them to be listed right after each other, such that if there are four marked boxes, the caption of those boxes will be listed in A1:A4 (even if it is not the four first boxes which are marked).

I have tried this code:

Dim i As Integer, n, As Integer
  n = 0
  For i = 1 To 6
    strChkName = "chk" & i
    If strChkName.Value Then
    Cells(1+n, 2) = strChkName.Caption
    n = n + 1
  End If
Next i

This however, does not work. Likely because of the combination of a string and the .Value thing. I cannot seem to find anything regarding this. Is it even possible to do?

...controls("name")...

Nathan_Sav
– Nathan_Sav

2016-01-11 13:10:07 +00:00
Commented Jan 11, 2016 at 13:10 — Nathan_Sav
– Nathan_Sav, Commented Jan 11, 2016 at 13:10
more to the point If Controls("chk" & i).Value Then

Scott Holtzman
– Scott Holtzman

2016-01-11 13:28:23 +00:00
Commented Jan 11, 2016 at 13:28 — Scott Holtzman
– Scott Holtzman, Commented Jan 11, 2016 at 13:28
Thank you very much, that did the trick...

Jakob Busk Sørensen
– Jakob Busk Sørensen

2016-01-11 13:31:03 +00:00
Commented Jan 11, 2016 at 13:31 — Jakob Busk Sørensen
– Jakob Busk Sørensen, Commented Jan 11, 2016 at 13:31

Jakob Busk Sørensen · Accepted Answer · 2016-01-11 13:37:15Z

2

Using comments from Nathan and Scott, I managed to solve the issue using the Controls() function in VBA. Used in my code, it looks like this:

Dim i As Integer, n, As Integer
  n = 0
  For i = 1 To 
    If Controls("chk" & i).Value Then
      Cells(1+n, 2) = Controls("chk" & i).Caption
      n = n + 1
    End If
Next i

answered Jan 11, 2016 at 13:37

Jakob Busk Sørensen

6,20110 gold badges59 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use strings for object names in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related