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

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



excelent wrote:

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

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
"Dave Peterson" wrote in message
...
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



excelent wrote:

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

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
"Dave Peterson" wrote in message
...
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



excelent wrote:

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