Hi!

I have on a userform 8 spreadsheet activeX controls, and I want all of them
to do the same thing. Do I have to write the same code 8 times or there is a
way to do it only once. The names a spreadsheet1, 2......8.

Thanks in advance

Eli

Try the below piece of code...which will filter down to the same type of
controls..

Dim Ctrl As MSForms.Control
For Each Ctrl In UserForm1.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
' do something for all checkboxes
End If
Next Ctrl

--
Jacob


"�לי" wrote:

Hi!

I have on a userform 8 spreadsheet activeX controls, and I want all of them
to do the same thing. Do I have to write the same code 8 times or there is a
way to do it only once. The names a spreadsheet1, 2......8.

Thanks in advance

Eli

Thanks for your fast response Jacob.

But since I handling with activeX controls, it want be different?

Eli

"Jacob Skaria" wrote:

Try the below piece of code...which will filter down to the same type of
controls..

Dim Ctrl As MSForms.Control
For Each Ctrl In UserForm1.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
' do something for all checkboxes
End If
Next Ctrl

--
Jacob


"�לי" wrote:

Hi!

I have on a userform 8 spreadsheet activeX controls, and I want all of them
to do the same thing. Do I have to write the same code 8 times or there is a
way to do it only once. The names a spreadsheet1, 2......8.

Thanks in advance

Eli

You should apply a structure like this:

Private Sub CommandButton1_Click()
Call sameprocess("spreadsheet1")
End Sub


Private Sub CommandButton2_Click()
Call sameprocess("spreadsheet2")
End Sub
.... to spreadsheet8

Sub sameprocess(currsheetname As String)
Worksheets(currsheetname).Select
'...
End Sub

Give more details for a more specific answer!
--
Regards!
Stefi



€ž×�לי€� ezt Ã*rta:

Hi!

I have on a userform 8 spreadsheet activeX controls, and I want all of them
to do the same thing. Do I have to write the same code 8 times or there is a
way to do it only once. The names a spreadsheet1, 2......8.

Thanks in advance

Eli

Thanks Stefi.

The code is:

' Step 1 - go over spreadsheets and verify if it is visible. if not - go on.
If Me.Spreadsheet1.Visible = True Then
x = 2
Do Until Me.Spreadsheet1.Cells(x, 1).Value = ""
Y = 2
Do Until Me.Spreadsheet1.Cells(1, Y).Value = ""
Me.Spreadsheet1.Range("A" & x).Value = Me.Spreadsheet1.Range("A"
& x).Value & Me.Spreadsheet1.Cells(x, Y).Value
Y = Y + 1
Loop
x = x + 1
Loop
End If
' Step 2 - If spreadsheet is visible verify if changes were done. if not -
go on.
x = 3
Do Until Me.Spreadsheet1.Range("A" & x).Value = ""
If Me.Spreadsheet1.Range("A2").Value = Me.Spreadsheet1.Range("A" & x).Value
Then
Me.Spreadsheet1.Range("A2").Value = "No change"
GoTo Step3
Else
Me.Spreadsheet1.Range("A2").Value = "New"
x = x + 1

End If
Loop
Step3:

This code should be duplicated for the rest spreadsheet2-7 unless it could
be modified.

Eli
"Stefi" wrote:

You should apply a structure like this:

Private Sub CommandButton1_Click()
Call sameprocess("spreadsheet1")
End Sub


Private Sub CommandButton2_Click()
Call sameprocess("spreadsheet2")
End Sub
... to spreadsheet8

Sub sameprocess(currsheetname As String)
Worksheets(currsheetname).Select
'...
End Sub

Give more details for a more specific answer!
--
Regards!
Stefi



€ž×�לי€� ezt Ã*rta:

Hi!

I have on a userform 8 spreadsheet activeX controls, and I want all of them
to do the same thing. Do I have to write the same code 8 times or there is a
way to do it only once. The names a spreadsheet1, 2......8.

Thanks in advance

Eli

This code is under the click event of a command button.

"�לי" wrote:

Thanks Stefi.

The code is:

' Step 1 - go over spreadsheets and verify if it is visible. if not - go on.
If Me.Spreadsheet1.Visible = True Then
x = 2
Do Until Me.Spreadsheet1.Cells(x, 1).Value = ""
Y = 2
Do Until Me.Spreadsheet1.Cells(1, Y).Value = ""
Me.Spreadsheet1.Range("A" & x).Value = Me.Spreadsheet1.Range("A"
& x).Value & Me.Spreadsheet1.Cells(x, Y).Value
Y = Y + 1
Loop
x = x + 1
Loop
End If
' Step 2 - If spreadsheet is visible verify if changes were done. if not -
go on.
x = 3
Do Until Me.Spreadsheet1.Range("A" & x).Value = ""
If Me.Spreadsheet1.Range("A2").Value = Me.Spreadsheet1.Range("A" & x).Value
Then
Me.Spreadsheet1.Range("A2").Value = "No change"
GoTo Step3
Else
Me.Spreadsheet1.Range("A2").Value = "New"
x = x + 1

End If
Loop
Step3:

This code should be duplicated for the rest spreadsheet2-7 unless it could
be modified.

Eli
"Stefi" wrote:

You should apply a structure like this:

Private Sub CommandButton1_Click()
Call sameprocess("spreadsheet1")
End Sub


Private Sub CommandButton2_Click()
Call sameprocess("spreadsheet2")
End Sub
... to spreadsheet8

Sub sameprocess(currsheetname As String)
Worksheets(currsheetname).Select
'...
End Sub

Give more details for a more specific answer!
--
Regards!
Stefi



€ž×�לי€� ezt Ã*rta:

Hi!

I have on a userform 8 spreadsheet activeX controls, and I want all of them
to do the same thing. Do I have to write the same code 8 times or there is a
way to do it only once. The names a spreadsheet1, 2......8.

Thanks in advance

Eli

Hi Eli

This is how to do it. Pass the spreadsheet in question into the shared sub:

Private Sub CommandButton1_Click()
Call CommonCode(Me.Spreadsheet1) '<send sheet1 for treatment
End Sub

Private Sub CommandButton2_Click()
Call CommonCode(Me.Spreadsheet2) '<send sheet2 for treatment
End Sub

Sub CommonCode(SPR As Spreadsheet) '<here it receives the right sheet
If SPR.Visible = True Then
x = 2
Do Until SPR.Cells(x, 1).Value = ""
y = 2
Do Until SPR.Cells(1, y).Value = ""
'and so on and so on.

HTH. Best wishes Harald

"�לי" wrote in message
...
Thanks Stefi.

The code is:

' Step 1 - go over spreadsheets and verify if it is visible. if not - go
on.
If Me.Spreadsheet1.Visible = True Then
x = 2
Do Until Me.Spreadsheet1.Cells(x, 1).Value = ""
Y = 2
Do Until Me.Spreadsheet1.Cells(1, Y).Value = ""
Me.Spreadsheet1.Range("A" & x).Value =
Me.Spreadsheet1.Range("A"
& x).Value & Me.Spreadsheet1.Cells(x, Y).Value
Y = Y + 1
Loop
x = x + 1
Loop
End If
' Step 2 - If spreadsheet is visible verify if changes were done. if not -
go on.
x = 3
Do Until Me.Spreadsheet1.Range("A" & x).Value = ""
If Me.Spreadsheet1.Range("A2").Value = Me.Spreadsheet1.Range("A" &
x).Value
Then
Me.Spreadsheet1.Range("A2").Value = "No change"
GoTo Step3
Else
Me.Spreadsheet1.Range("A2").Value = "New"
x = x + 1

End If
Loop
Step3:

This code should be duplicated for the rest spreadsheet2-7 unless it could
be modified.

Eli
"Stefi" wrote:

You should apply a structure like this:

Private Sub CommandButton1_Click()
Call sameprocess("spreadsheet1")
End Sub


Private Sub CommandButton2_Click()
Call sameprocess("spreadsheet2")
End Sub
... to spreadsheet8

Sub sameprocess(currsheetname As String)
Worksheets(currsheetname).Select
'...
End Sub

Give more details for a more specific answer!
--
Regards!
Stefi



€ž×�לי€� ezt Ã*rta:

Hi!

I have on a userform 8 spreadsheet activeX controls, and I want all of
them
to do the same thing. Do I have to write the same code 8 times or there
is a
way to do it only once. The names a spreadsheet1, 2......8.

Thanks in advance

Eli

Thank you very much!

"Harald Staff" wrote:

Hi Eli

This is how to do it. Pass the spreadsheet in question into the shared sub:

Private Sub CommandButton1_Click()
Call CommonCode(Me.Spreadsheet1) '<send sheet1 for treatment
End Sub

Private Sub CommandButton2_Click()
Call CommonCode(Me.Spreadsheet2) '<send sheet2 for treatment
End Sub

Sub CommonCode(SPR As Spreadsheet) '<here it receives the right sheet
If SPR.Visible = True Then
x = 2
Do Until SPR.Cells(x, 1).Value = ""
y = 2
Do Until SPR.Cells(1, y).Value = ""
'and so on and so on.

HTH. Best wishes Harald

"�לי" wrote in message
...
Thanks Stefi.

The code is:

' Step 1 - go over spreadsheets and verify if it is visible. if not - go
on.
If Me.Spreadsheet1.Visible = True Then
x = 2
Do Until Me.Spreadsheet1.Cells(x, 1).Value = ""
Y = 2
Do Until Me.Spreadsheet1.Cells(1, Y).Value = ""
Me.Spreadsheet1.Range("A" & x).Value =
Me.Spreadsheet1.Range("A"
& x).Value & Me.Spreadsheet1.Cells(x, Y).Value
Y = Y + 1
Loop
x = x + 1
Loop
End If
' Step 2 - If spreadsheet is visible verify if changes were done. if not -
go on.
x = 3
Do Until Me.Spreadsheet1.Range("A" & x).Value = ""
If Me.Spreadsheet1.Range("A2").Value = Me.Spreadsheet1.Range("A" &
x).Value
Then
Me.Spreadsheet1.Range("A2").Value = "No change"
GoTo Step3
Else
Me.Spreadsheet1.Range("A2").Value = "New"
x = x + 1

End If
Loop
Step3:

This code should be duplicated for the rest spreadsheet2-7 unless it could
be modified.

Eli
"Stefi" wrote:

You should apply a structure like this:

Private Sub CommandButton1_Click()
Call sameprocess("spreadsheet1")
End Sub


Private Sub CommandButton2_Click()
Call sameprocess("spreadsheet2")
End Sub
... to spreadsheet8

Sub sameprocess(currsheetname As String)
Worksheets(currsheetname).Select
'...
End Sub

Give more details for a more specific answer!
--
Regards!
Stefi



€ž×�לי€� ezt Ã*rta:

Hi!

I have on a userform 8 spreadsheet activeX controls, and I want all of
them
to do the same thing. Do I have to write the same code 8 times or there
is a
way to do it only once. The names a spreadsheet1, 2......8.

Thanks in advance

Eli

.

You could use 8 separate events that call a common procedure--just pass the
control to the common procedure.

Or maybe you could use this class technique from John Walkenbach:
http://spreadsheetpage.com/index.php...one_procedure/

He uses Commandbuttons in his example. Not all controls have all events exposed
this way.

??? wrote:

Hi!

I have on a userform 8 spreadsheet activeX controls, and I want all of them
to do the same thing. Do I have to write the same code 8 times or there is a
way to do it only once. The names a spreadsheet1, 2......8.

Thanks in advance

Eli

--

Dave Peterson