How to enumerate buttons on user form

[Andrew]

Hello,
I have a userform with up to 20 buttons. Upon opening I want the userform to read the name of each sheet and assign each sheet name to the caption ofa button. Then I want the buttons to be used for navigation. I have a code which works, but I had to write out 20 cases for 20 possible buttons. Below is an example of my code for naming button 1.

Private Sub UserForm_Initialize()
If Worksheets.Count >= 1 Then
CommandButton1.Visible = True
CommandButton1.Caption = Worksheets(1).Name
Else
CommandButton1.Caption = ""
CommandButton1.Visible = False
End If

I'd like to be able to do this without using the button names such as CommandButton1. I'd like to have all of the buttons in an array and then write this out as a for loop. Such as:

For k=1 to worksheets.count
CommandButton(k).Visible = True
CommandButton(k).Caption = Worksheets(k).Name
Next

How can this be done? Can I make an array of userform objects and index them?

thanks

 

[Claus Busch]

Hi Andrew,

Am Tue, 19 Jun 2012 05:23:59 -0700 (PDT) schrieb Andrew:

I'd like to be able to do this without using the button names such as CommandButton1. I'd like to have all of the buttons in an array and then write this out as a for loop. Such as:

For k=1 to worksheets.count
CommandButton(k).Visible = True
CommandButton(k).Caption = Worksheets(k).Name
Next

try:
Private Sub UserForm_Initialize()
Dim cmd As Control
Dim i As Integer

i = 1
For Each cmd In Me.Controls
If Left(cmd.Name, 4) = "Comm" _
And i <= Sheets.Count Then
cmd.Caption = Sheets(i).Name
cmd.Visible = True
End If
i = i + 1
Next cmd

End Sub


Regards
Claus Busch

 

[Andrew]

Hello,
I have a userform with up to 20 buttons. Upon opening I want the userform to read the name of each sheet and assign each sheet name to the caption of a button. Then I want the buttons to be used for navigation. I have a code which works, but I had to write out 20 cases for 20 possible buttons. Below is an example of my code for naming button 1.

Private Sub UserForm_Initialize()
If Worksheets.Count >= 1 Then
CommandButton1.Visible = True
CommandButton1.Caption = Worksheets(1).Name
Else
CommandButton1.Caption = ""
CommandButton1.Visible = False
End If

I'd like to be able to do this without using the button names such as CommandButton1. I'd like to have all of the buttons in an array and then write this out as a for loop. Such as:

For k=1 to worksheets.count
CommandButton(k).Visible = True
CommandButton(k).Caption = Worksheets(k).Name
Next

How can this be done? Can I make an array of userform objects and index them?

thanks

Sorry I took so long to reply. Thank you very much for this nice concise code.

I appreciate it.

AG