Looping Private Sub

[Crash1hd]

Is there a way of shortening this code

Private Sub Command1_Click()

Does something in here doesnt really matter dont have the code as its at work

End Sub

Private Sub Command2_Click()

Does something in here doesnt really matter dont have the code as its at work

End Sub

Private Sub Command3_Click()

Does something in here doesnt really matter dont have the code as its at work

End Sub

Private Sub Command4_Click()

Does something in here doesnt really matter dont have the code as its at work

End Sub

Private Sub Command5_Click()

Does something in here doesnt really matter dont have the code as its at work

End Sub

to something that uses a do loop where I = I + 1 and then the # in Command#_Click could be dynamically replaced?

 

[Mile-O]

Of course it's possible to condense all that into one procedure.

A quick example. I have three textboxes on my form and, when the cursor enters them I want to change the font to Tahoma from Verdana and the BackColor to Red from White. I want it to revert back when the focus is lost.

The way you have it:

Private Sub Text1_GotFocus()
    Me.Text1.Font = "Tahoma"
    Me.Text1.BackColor = vbRed
End Sub

Private Sub Text2_GotFocus()
    Me.Text2.Font = "Tahoma"
    Me.Text2.BackColor = vbRed
End Sub

Private Sub Text3_GotFocus()
    Me.Text3.Font = "Tahoma"
    Me.Text3.BackColor = vbRed
End Sub

Private Sub Text1_LostFocus()
    Me.Text1.Font = "Verdana"
    Me.Text1.BackColor = vbWhite
End Sub

Private Sub Text2_LostFocus()
    Me.Text2.Font = "Verdana"
    Me.Text2.BackColor = vbWhite
End Sub

Private Sub Text3_LostFocus()
    Me.Text3.Font = "Verdana"
    Me.Text3.BackColor = vbWhite
End Sub

Solution:

Private Sub ChangeTextbox(ByRef ctl As Control, ByRef Toggle As Boolean)
    If Toggle Then
        ctl.Font = "Tahoma"
        ctl.BackColor = vbRed
    Else
        ctl.Font = "Verdana"
        ctl.BackColor = vbWhite
    End If
End Sub

And:

Private Sub Text1_GotFocus()
    Call ChangeTextbox(Me.Text1, True)
End Sub

Private Sub Text2_GotFocus()
    Call ChangeTextbox(Me.Text2, True)
End Sub

Private Sub Text3_GotFocus()
    Call ChangeTextbox(Me.Text3, True)
End Sub

Private Sub Text1_LostFocus()
    Call ChangeTextbox(Me.Text1, False)
End Sub

Private Sub Text2_LostFocus()
    Call ChangeTextbox(Me.Text2, False)
End Sub

Private Sub Text3_LostFocus()
    Call ChangeTextbox(Me.Text3, False)
End Sub

Now, if you want to change part of the process (e.g. change the ForeColor too) then you only have to go to the one sub and make the edit there.

 

[Pauldohert]

I am not sure but this may be useful

http://www.access-programmers.co.uk/forums/showthread.php?t=66574&highlight=paste

Paul