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