Need help with Array

[Guest]

Hi fellows
I got a Userform with 6 Checkbox'es
and 1 Commandbutton

What i wont is check out som of the 6's checboxes
when i click on commandutton the sheets/checkbox
i checked out is selected

here is what i got - what do i do ?


Private Sub CommandButton1_Click()

If UserForm1.CheckBox1 = True Then pr1 = "Ark1"
If UserForm1.CheckBox2 = True Then pr2 = "Ark2"
If UserForm1.CheckBox3 = True Then pr3 = "Ark3"
If UserForm1.CheckBox4 = True Then pr4 = "Ark4"
If UserForm1.CheckBox5 = True Then pr5 = "Ark5"
If UserForm1.CheckBox6 = True Then pr6 = "Ark6"
'MsgBox ("") & pr1 & "," & pr2 & "," & pr3 & "," & pr4 & "," & pr5 & "," & pr6
x = pr1 & "," & pr2 & "," & pr3 & "," & pr4 & "," & pr5 & "," & pr6

Sheets(Array(x)).Select
'Sheets(Array(pr1, pr2, pr3, pr4, pr5)).Select

End Sub

thanks in advance

 

[Dave Peterson]

Since the names of the checkboxes and the names of the sheets are nice
(checkbox1 controls Ark1, ..., checkbox6 controls Ark6), you could use something
like:

Option Explicit
Private Sub CommandButton1_Click()

Dim iCtr As Long
Dim myNames As Variant
Dim myArr() As String
Dim sCtr As Long

ReDim myArr(1 To 6)

sCtr = 0
For iCtr = 1 To 6
If Me.Controls("Checkbox" & iCtr) = True Then
sCtr = sCtr + 1
myArr(sCtr) = "Ark" & iCtr
End If
Next iCtr

If sCtr = 0 Then
'no checkboxes selected
Beep
Else
ReDim Preserve myArr(1 To sCtr)
Sheets(myArr).Select
End If

End Sub

 

[Mike Fogleman]

Just a word of warning that VBA does not support multiple worksheets. Now
that you have them selected, you cannot do things with a common range on all
sheets at once, ie.
With Selection.Range("A1:A10").Value = "Hello"
will only happen on the Active sheet of the group, not all of them. You will
still need to loop through the array and activate each sheet in turn.

Mike F

 

[Guest]

Dave ur the man tanks alot

and Mike ill try remember that tanks


"Mike Fogleman" skrev:

Just a word of warning that VBA does not support multiple worksheets. Now
that you have them selected, you cannot do things with a common range on all
sheets at once, ie.
With Selection.Range("A1:A10").Value = "Hello"
will only happen on the Active sheet of the group, not all of them. You will
still need to loop through the array and activate each sheet in turn.

Mike F