[RESOLVED] Chang caption of checkbox

[Monitron]

Hello,

I was wondering if it is possible that the caption of a checkbox can be changed in a loop.

The problem is that i have 12 checkboxes on a form. These checkboxes only can be shown at the moment that other cell on a certain sheet "standaard" are filled. In our example i want to create a loop for range B8:B19. The reference cell where the future name of the checkbox.caption is stored is R8:R19.

Now is there some code that i automaticly can loop the checkbox in reference with the activecell out of range B8:B19
All the checkboxes have the name Chk1, ..2, ..3, until 12
In the future there will be manymore checkboxes added, so for the code it's much easier when it's short and understandable then copy paste these lines over and over.

The code i have is as follows and is be done in de initilize routine.

    1:  Set std = Worksheets("standaard")
    2:  std.Visible = xlSheetVisible
    3:  std.Select
    4:  Range("b8:b19").Select
    5:  ActiveCell.Offset(0, 0).Select
    6:  For I = 1 To 12
    7:  If ActiveCell <> "" Then
    8:  ActiveCell.Offset(0, 16).Select
    9:  Chk1.Caption = ActiveCell.Value
    10: Chk1.Enabled = True
    11: ActiveCell.Offset(1, -16).Select
    12: Else
    13: Chk1.Caption = ""
    14: Chk1.Enabled = False
    15: ActiveCell.Offset(1, 0).Select
    16: End If
    17: Next

[akhileshbc]

First create a checkbox. Then copy it and paste to create a control array.
Then you can use your code like this....

Set std = Worksheets("standaard")
    2:  std.Visible = xlSheetVisible
    3:  std.Select
    4:  Range("b8:b19").Select
    5:  ActiveCell.Offset(0, 0).Select
    6:  For I = 1 To 12
    7:  If ActiveCell <> "" Then
    8:  ActiveCell.Offset(0, 16).Select
    9:  Chk1(I).Caption = ActiveCell.Value 'changed
    10: Chk1(I).Enabled = True 'changed
    11: ActiveCell.Offset(1, -16).Select
    12: Else
    13: Chk1(I).Caption = "" 'changed
    14: Chk1(I).Enabled = False 'changed
    15: ActiveCell.Offset(1, 0).Select
    16: End If
    17: Next

[akhileshbc]

BTW... Welcome to VBForums

[Hack]

Moved to Office Development

[Monitron]

Thanks, but i'm new with vba. I got a goal to make an excell sheet more users friendly. I want to accomplish my goal and maybe i'll get a good change to follow a cursus of VBA for office. It's quit expensive here and i want the company to pay for it.

Can you help m e further with a control array..

Thx

[Fizziii]

VBA does not support control arrays.

Assuming that your checkboxes are on a form, you could do the following:

Dim TempName as string
For i = 1 to 12
   TempName = "Chk" & i
   If ActiveCell <> "" Then
      ActiveCell.Offset(0, 16).Select
      Me.Controls(TempName).Caption = ActiveCell.Value
      Me.Controls(TempName).Enabled = True
   Else
      'More code
   End If
Next

This works for code on a form, where the checkboxes are on the form. ("Me" refers to the form)

[Monitron]

It works fine, thx all.

[akhileshbc]

If your problem is solved, then mark the thread as RESOLVED...
And if you find someone's post as helpful, then try to rate him...

-Best wishes