Multi controls code

[×לי]

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 are: spreadsheet1, 2......8.

Thanks in advance

Eli

 

[Jacob Skaria]

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

 

[×לי]

Thanks for your fast response Jacob.

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

Eli

 

[Stefi]

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:

 

[×לי]

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

 

[×לי]

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

 

[Harald Staff]

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

 

[×לי]

Thank you very much!

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



.

 

[Dave Peterson]

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/file/multiple_userform_buttons_with_one_procedure/

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