How to enumerate buttons on user form

A

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
 
C

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top