Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 a spreadsheet1, 2......8. Thanks in advance Eli |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi controls code
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi controls code
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi controls code
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi controls code
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi controls code
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi controls code
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi controls code
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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi controls code
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Controls With VBA Code | Excel Programming | |||
code to parse a multi-colum multi-select listbox | Excel Programming | |||
creating controls using code | Excel Programming | |||
Multi-conditional sum with VBA code ?? | Excel Programming | |||
Using same code for Multiple cmd Controls | Excel Programming |