In VBA, How can I do this with FOR() ?

[denrew]

Hi I'm working with these kind of repeated codes.

---

Private Sub Cmd1_Click()


With Sheets("sheet1")
.Activate
.Visible = True
End With
End Sub

---

Private Sub Cmd2_Click()


With Sheets("sheet2")
.Activate
.Visible = True
End With
End Sub

---

...

Private Sub Cmd200_Click()


With Sheets("sheet200")
.Activate
.Visible = True
End With
End Sub

---

I'm doing this from Cmd1 to Cmd200 on my own!
Now I'm trying to seek a FANCY way to contract these codes.

I think stuff like "For i = 1 to 200" would be effective for this.
But i don't know how...

Could you give me a solution?
Thank you in advance!

 

[Rick Rothstein]

Hi I'm working with these kind of repeated codes.

---

Private Sub Cmd1_Click()

With Sheets("sheet1")
.Activate
.Visible = True
End With
End Sub

---

etc.

Where are your Button located... on a UserForm or a worksheet? If on a worksheet, what kind of buttons are they... Form controls or ActiveX controls?

 

[denrew]

Oh, I omitted so much information.

Buttons are in my worksheet.
And the buttons are ActiveX controls.

 

[Rick Rothstein]

I think the following will work for you. Make a copy of you workbook and then do all of the following in that copy so you do not lose what you now have if this does not work for you for some reason. What we are going to do is create a "control array" of your all the buttons whose names start with the letters "Cmd". First, delete all 200 of your CommandButtton's Click event code procedures... we are going to shorten the code you need dramatically. Next, place all of the following code in the indicated locations and, after you have done that, Save your workbook and close it. Now whenever you open that workbook, the CommandButtons whose names start with "Cmd" will make visible and then activate the worksheet whose name is "Sheet" followed by the number after the letters "Cmd" for the button you clicked.

' Place in a Class module
' ========================
Public WithEvents ButtonGroup As CommandButton

Private Sub ButtonGroup_Click()
'  Sheets(ButtonGroup.Parent.Name).Cells(ButtonGroup.TopLeftCell.Row, ButtonGroup.TopLeftCell.Column) = "Done"
  With Sheets("Sheet" & Mid(ButtonGroup.Name, 4))
    .Visible = True
    .Activate
  End With
End Sub

' Place the following in normal code Module
' =========================================
Dim Buttons() As New Class1

Public Sub Class_Init()
    Dim Sh As Worksheet
    Dim Obj As OLEObject
    Dim ButtonCount As Integer
    For Each Sh In ThisWorkbook.Worksheets
        For Each Obj In Sh.OLEObjects
            If Left(Obj.Name, 3) = "Cmd" Then
                ButtonCount = ButtonCount + 1
                ReDim Preserve Buttons(1 To ButtonCount)
                Set Buttons(ButtonCount).ButtonGroup = Obj.Object
            End If
        Next Obj
    Next Sh
End Sub

' Place the following code in the ThisWorkbook code Module
'=========================================================
Private Sub Workbook_Open()
  Class_Init
End Sub

That's it... that is all the code you should need do what you indicated you wanted your 200 CommandButton to do.

 

[denrew]

It works perfectly!!
I thought that a concept is easy and simple, more complicated than what I expected, though.

Thank you for your help!!
I want to be a person like you come up with these kind of things in no time

 

[Udaya Raj]

I have Same Kind of Problem so please help me

I have nearly 200 toggle buttons in a user form and I want half of them "Togglebutton1" to "Togglebutton100" will toggle between "Wall" and "Slab" and half of them "Togglebutton101" to "Togglebutton200" will toggle between "Unit-1" and "Unit-2". so for each buttons click from 1 to 100 will have a same code and 101 to 200 will have a same click action. I was just wondering if i have to assign individual click action for each toggle button or is there any way to loop between them.

code is like this

'for togglebuttons 1 to 100 
 Private Sub ToggleButton1_Click() 
    If ToggleButton1.Value = False Then     
ToggleButton1.Caption = "Wall"
Else     ToggleButton1.Caption = "Slab"     
End If 
End Sub 

 'for togglebuttons 101 to 200
  Private Sub ToggleButton101_Click()
     If ToggleButton101.Value = False Then 
    ToggleButton101.Caption = "Unit-1"
     Else     ToggleButton101.Caption = "Unit-2" 
    End If 
End Sub