Hi Gang,

Here's what I've got so far.....I created a test wb and form with three
commandbuttons.....I wish to rename these buttons periodically (only the
caption, not the underlying code), using the contents of cells in Col A. The
msgbox indicates it's stepping through code correctly but, so far, I can only
get it to rename CommandButton1. I've tried all I can think of to get the
code to step through the commandbuttons but so far, nothing works for me.

Private Sub UserForm_Activate()
Dim M As String
Dim A As String

For N = 1 To 3
M = "CommandButton" & N
A = Range("a" & N).Text
MsgBox M
' Me.M.Caption = A<<this didn't work as well as various tries at &
statements.
Me.CommandButton1.Caption = A '<<< this of course only renamed CB1
Next N

End Sub

And and all help here would be greatly appreciated.

TIA,

Don

How about:

Option Explicit
Private Sub UserForm_Activate()
Dim M As String
Dim A As String
Dim N As Long

For N = 1 To 3
M = "CommandButton" & N
A = Worksheets("sheet1").Range("a" & N).Text
Me.Controls(M).Caption = A
Next N

End Sub



Don wrote:

Hi Gang,

Here's what I've got so far.....I created a test wb and form with three
commandbuttons.....I wish to rename these buttons periodically (only the
caption, not the underlying code), using the contents of cells in Col A. The
msgbox indicates it's stepping through code correctly but, so far, I can only
get it to rename CommandButton1. I've tried all I can think of to get the
code to step through the commandbuttons but so far, nothing works for me.

Private Sub UserForm_Activate()
Dim M As String
Dim A As String

For N = 1 To 3
M = "CommandButton" & N
A = Range("a" & N).Text
MsgBox M
' Me.M.Caption = A<<this didn't work as well as various tries at &
statements.
Me.CommandButton1.Caption = A '<<< this of course only renamed CB1
Next N

End Sub

And and all help here would be greatly appreciated.

TIA,

Don

--

Dave Peterson

Dave.......perfect....thank a lot. And although this works, I did notice
that the "Caption" in the properties window for these buttons does not
change. I am I right that this will have to run each time that Form is
called?

Don

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub UserForm_Activate()
Dim M As String
Dim A As String
Dim N As Long

For N = 1 To 3
M = "CommandButton" & N
A = Worksheets("sheet1").Range("a" & N).Text
Me.Controls(M).Caption = A
Next N

End Sub



Don wrote:

Hi Gang,

Here's what I've got so far.....I created a test wb and form with three
commandbuttons.....I wish to rename these buttons periodically (only the
caption, not the underlying code), using the contents of cells in Col A. The
msgbox indicates it's stepping through code correctly but, so far, I can only
get it to rename CommandButton1. I've tried all I can think of to get the
code to step through the commandbuttons but so far, nothing works for me.

Private Sub UserForm_Activate()
Dim M As String
Dim A As String

For N = 1 To 3
M = "CommandButton" & N
A = Range("a" & N).Text
MsgBox M
' Me.M.Caption = A<<this didn't work as well as various tries at &
statements.
Me.CommandButton1.Caption = A '<<< this of course only renamed CB1
Next N

End Sub

And and all help here would be greatly appreciated.

TIA,

Don

--

Dave Peterson

You can either put the caption in the properties window manually (while you're
designing the form).

Or you could use code. If the caption never changes, I don't see why you
wouldn't just type it in that properties window manually.

Or use the Userform_Initialize procedure and do it in code.

It's kind of 6 of one and half dozen of the other (in my opinion).

Don wrote:

Dave.......perfect....thank a lot. And although this works, I did notice
that the "Caption" in the properties window for these buttons does not
change. I am I right that this will have to run each time that Form is
called?

Don

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub UserForm_Activate()
Dim M As String
Dim A As String
Dim N As Long

For N = 1 To 3
M = "CommandButton" & N
A = Worksheets("sheet1").Range("a" & N).Text
Me.Controls(M).Caption = A
Next N

End Sub



Don wrote:

Hi Gang,

Here's what I've got so far.....I created a test wb and form with three
commandbuttons.....I wish to rename these buttons periodically (only the
caption, not the underlying code), using the contents of cells in Col A. The
msgbox indicates it's stepping through code correctly but, so far, I can only
get it to rename CommandButton1. I've tried all I can think of to get the
code to step through the commandbuttons but so far, nothing works for me.

Private Sub UserForm_Activate()
Dim M As String
Dim A As String

For N = 1 To 3
M = "CommandButton" & N
A = Range("a" & N).Text
MsgBox M
' Me.M.Caption = A<<this didn't work as well as various tries at &
statements.
Me.CommandButton1.Caption = A '<<< this of course only renamed CB1
Next N

End Sub

And and all help here would be greatly appreciated.

TIA,

Don

--

Dave Peterson


--

Dave Peterson

Hi again Dave,

This form has 52 buttons, one for each week of a fiscal year. I posted as
only three buttons for this forumn. The dates can be easily entered in
A1:A52 by typing a starting date and dragging down. Then I had hoped to run
code to rename the buttons. I'd like to run this without the code having to
run each time the form is called if that's possible. I'll give the Userform
Initialize method a try and see if I can make it work.

Thanks again for your input here and have a great weekend,

Don

"Dave Peterson" wrote:

You can either put the caption in the properties window manually (while you're
designing the form).

Or you could use code. If the caption never changes, I don't see why you
wouldn't just type it in that properties window manually.

Or use the Userform_Initialize procedure and do it in code.

It's kind of 6 of one and half dozen of the other (in my opinion).

Don wrote:

Dave.......perfect....thank a lot. And although this works, I did notice
that the "Caption" in the properties window for these buttons does not
change. I am I right that this will have to run each time that Form is
called?

Don

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub UserForm_Activate()
Dim M As String
Dim A As String
Dim N As Long

For N = 1 To 3
M = "CommandButton" & N
A = Worksheets("sheet1").Range("a" & N).Text
Me.Controls(M).Caption = A
Next N

End Sub



Don wrote:

Hi Gang,

Here's what I've got so far.....I created a test wb and form with three
commandbuttons.....I wish to rename these buttons periodically (only the
caption, not the underlying code), using the contents of cells in Col A. The
msgbox indicates it's stepping through code correctly but, so far, I can only
get it to rename CommandButton1. I've tried all I can think of to get the
code to step through the commandbuttons but so far, nothing works for me.

Private Sub UserForm_Activate()
Dim M As String
Dim A As String

For N = 1 To 3
M = "CommandButton" & N
A = Range("a" & N).Text
MsgBox M
' Me.M.Caption = A<<this didn't work as well as various tries at &
statements.
Me.CommandButton1.Caption = A '<<< this of course only renamed CB1
Next N

End Sub

And and all help here would be greatly appreciated.

TIA,

Don

--

Dave Peterson


--

Dave Peterson

So your just looking for a utility to change the names once:

Sub abc()
Dim vc As Object
Dim rng As Range, cell As Range
Dim i As Long
Set vc = ThisWorkbook.VBProject.VBComponents("UserForm1")
Set rng = Worksheets("Sheet1").Range("A1:A52")
i = 0
For Each cell In rng
i = i + 1
vc.Designer.Controls("CommandButton" & i) _
.Caption = Format(cell.Value, "mm_dd_yyyy")
Next
End Sub

--
Regards,
Tom Ogilvy



"Don" wrote in message
...
Hi again Dave,

This form has 52 buttons, one for each week of a fiscal year. I posted as
only three buttons for this forumn. The dates can be easily entered in
A1:A52 by typing a starting date and dragging down. Then I had hoped to
run
code to rename the buttons. I'd like to run this without the code having
to
run each time the form is called if that's possible. I'll give the
Userform
Initialize method a try and see if I can make it work.

Thanks again for your input here and have a great weekend,

Don

"Dave Peterson" wrote:

You can either put the caption in the properties window manually (while
you're
designing the form).

Or you could use code. If the caption never changes, I don't see why you
wouldn't just type it in that properties window manually.

Or use the Userform_Initialize procedure and do it in code.

It's kind of 6 of one and half dozen of the other (in my opinion).

Don wrote:

Dave.......perfect....thank a lot. And although this works, I did
notice
that the "Caption" in the properties window for these buttons does not
change. I am I right that this will have to run each time that Form is
called?

Don

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub UserForm_Activate()
Dim M As String
Dim A As String
Dim N As Long

For N = 1 To 3
M = "CommandButton" & N
A = Worksheets("sheet1").Range("a" & N).Text
Me.Controls(M).Caption = A
Next N

End Sub



Don wrote:

Hi Gang,

Here's what I've got so far.....I created a test wb and form with
three
commandbuttons.....I wish to rename these buttons periodically
(only the
caption, not the underlying code), using the contents of cells in
Col A. The
msgbox indicates it's stepping through code correctly but, so far,
I can only
get it to rename CommandButton1. I've tried all I can think of to
get the
code to step through the commandbuttons but so far, nothing works
for me.

Private Sub UserForm_Activate()
Dim M As String
Dim A As String

For N = 1 To 3
M = "CommandButton" & N
A = Range("a" & N).Text
MsgBox M
' Me.M.Caption = A<<this didn't work as well as various
tries at &
statements.
Me.CommandButton1.Caption = A '<<< this of course only
renamed CB1
Next N

End Sub

And and all help here would be greatly appreciated.

TIA,

Don

--

Dave Peterson


--

Dave Peterson

It sounds like you could drop the worksheet and just recaption the buttons based
on the current year when the userform gets initialized.

If the dates aren't completely arbitrary, it might work.

I used the first Friday of the year for the caption for my first button. Then
added 7.

Option Explicit
Private Sub UserForm_Initialize()

Dim iCtr As Long
Dim myStartDate As Date

myStartDate = FirstDOWinMonth(DateSerial(Year(Date), 1, 1), vbFriday)

For iCtr = 1 To 7 '52
Me.Controls("commandbutton" & iCtr).Caption _
= Format(myStartDate, "dddd-mm/dd/yyyy")
myStartDate = myStartDate + (7 * iCtr)
Next iCtr
End Sub

Function FirstDOWinMonth(TheDate As Date, DOW As Integer) As Date
FirstDOWinMonth = Int((DateSerial(Year(TheDate), _
Month(TheDate), 1) + 6 - DOW) / 7) * 7 + DOW
End Function

If you wanted some other start date, you could change vbFriday to
vbMonday...vbSunday.


Don wrote:

Hi again Dave,

This form has 52 buttons, one for each week of a fiscal year. I posted as
only three buttons for this forumn. The dates can be easily entered in
A1:A52 by typing a starting date and dragging down. Then I had hoped to run
code to rename the buttons. I'd like to run this without the code having to
run each time the form is called if that's possible. I'll give the Userform
Initialize method a try and see if I can make it work.

Thanks again for your input here and have a great weekend,

Don

"Dave Peterson" wrote:

You can either put the caption in the properties window manually (while you're
designing the form).

Or you could use code. If the caption never changes, I don't see why you
wouldn't just type it in that properties window manually.

Or use the Userform_Initialize procedure and do it in code.

It's kind of 6 of one and half dozen of the other (in my opinion).

Don wrote:

Dave.......perfect....thank a lot. And although this works, I did notice
that the "Caption" in the properties window for these buttons does not
change. I am I right that this will have to run each time that Form is
called?

Don

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub UserForm_Activate()
Dim M As String
Dim A As String
Dim N As Long

For N = 1 To 3
M = "CommandButton" & N
A = Worksheets("sheet1").Range("a" & N).Text
Me.Controls(M).Caption = A
Next N

End Sub



Don wrote:

Hi Gang,

Here's what I've got so far.....I created a test wb and form with three
commandbuttons.....I wish to rename these buttons periodically (only the
caption, not the underlying code), using the contents of cells in Col A. The
msgbox indicates it's stepping through code correctly but, so far, I can only
get it to rename CommandButton1. I've tried all I can think of to get the
code to step through the commandbuttons but so far, nothing works for me.

Private Sub UserForm_Activate()
Dim M As String
Dim A As String

For N = 1 To 3
M = "CommandButton" & N
A = Range("a" & N).Text
MsgBox M
' Me.M.Caption = A<<this didn't work as well as various tries at &
statements.
Me.CommandButton1.Caption = A '<<< this of course only renamed CB1
Next N

End Sub

And and all help here would be greatly appreciated.

TIA,

Don

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,

I tried the initialize method in the Form code and this does the job of
changing what the OP would see on the form buttons but still does not change
the actual caption property in the VBE properties window.

I'm thinking now that this code may just have to run everytime the Form is
called. It actually doesn't take that long to run, so I guess that's one
solution.

Tom posted a code below and I've tried that too, but have a problem with it
as I've stated in my reply to him.

Thanks for taking some time on this, I do appreciate it,

Have a good one,

Don

"Dave Peterson" wrote:

You can either put the caption in the properties window manually (while you're
designing the form).

Or you could use code. If the caption never changes, I don't see why you
wouldn't just type it in that properties window manually.

Or use the Userform_Initialize procedure and do it in code.

It's kind of 6 of one and half dozen of the other (in my opinion).

Don wrote:

Dave.......perfect....thank a lot. And although this works, I did notice
that the "Caption" in the properties window for these buttons does not
change. I am I right that this will have to run each time that Form is
called?

Don

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub UserForm_Activate()
Dim M As String
Dim A As String
Dim N As Long

For N = 1 To 3
M = "CommandButton" & N
A = Worksheets("sheet1").Range("a" & N).Text
Me.Controls(M).Caption = A
Next N

End Sub



Don wrote:

Hi Gang,

Here's what I've got so far.....I created a test wb and form with three
commandbuttons.....I wish to rename these buttons periodically (only the
caption, not the underlying code), using the contents of cells in Col A. The
msgbox indicates it's stepping through code correctly but, so far, I can only
get it to rename CommandButton1. I've tried all I can think of to get the
code to step through the commandbuttons but so far, nothing works for me.

Private Sub UserForm_Activate()
Dim M As String
Dim A As String

For N = 1 To 3
M = "CommandButton" & N
A = Range("a" & N).Text
MsgBox M
' Me.M.Caption = A<<this didn't work as well as various tries at &
statements.
Me.CommandButton1.Caption = A '<<< this of course only renamed CB1
Next N

End Sub

And and all help here would be greatly appreciated.

TIA,

Don

--

Dave Peterson


--

Dave Peterson

Ps. I was fiddling around with this statement:
DateForNewCaptions = DateSerial(Year(Date), 9, 1)

You may want:
DateForNewCaptions = DateSerial(Year(Date), 7, 1)
so it shifts over on July 1st. Not September 1st.



Don wrote:

Dave,

I tried the initialize method in the Form code and this does the job of
changing what the OP would see on the form buttons but still does not change
the actual caption property in the VBE properties window.

I'm thinking now that this code may just have to run everytime the Form is
called. It actually doesn't take that long to run, so I guess that's one
solution.

Tom posted a code below and I've tried that too, but have a problem with it
as I've stated in my reply to him.

Thanks for taking some time on this, I do appreciate it,

Have a good one,

Don

"Dave Peterson" wrote:

You can either put the caption in the properties window manually (while you're
designing the form).

Or you could use code. If the caption never changes, I don't see why you
wouldn't just type it in that properties window manually.

Or use the Userform_Initialize procedure and do it in code.

It's kind of 6 of one and half dozen of the other (in my opinion).

Don wrote:

Dave.......perfect....thank a lot. And although this works, I did notice
that the "Caption" in the properties window for these buttons does not
change. I am I right that this will have to run each time that Form is
called?

Don

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub UserForm_Activate()
Dim M As String
Dim A As String
Dim N As Long

For N = 1 To 3
M = "CommandButton" & N
A = Worksheets("sheet1").Range("a" & N).Text
Me.Controls(M).Caption = A
Next N

End Sub



Don wrote:

Hi Gang,

Here's what I've got so far.....I created a test wb and form with three
commandbuttons.....I wish to rename these buttons periodically (only the
caption, not the underlying code), using the contents of cells in Col A. The
msgbox indicates it's stepping through code correctly but, so far, I can only
get it to rename CommandButton1. I've tried all I can think of to get the
code to step through the commandbuttons but so far, nothing works for me.

Private Sub UserForm_Activate()
Dim M As String
Dim A As String

For N = 1 To 3
M = "CommandButton" & N
A = Range("a" & N).Text
MsgBox M
' Me.M.Caption = A<<this didn't work as well as various tries at &
statements.
Me.CommandButton1.Caption = A '<<< this of course only renamed CB1
Next N

End Sub

And and all help here would be greatly appreciated.

TIA,

Don

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson