And here I am again...this time with some good news. I was able to get Tom's
code to work. By going into the tools menu and checking the option you
indicated, his code worked fine. Then I was able to delete that macro,
delete Sheet1, and will be able to delete CommandButton53, if necessary.
Then saved and closed the workbook, reopened that file and the ButtonCaptions
were all the way I wanted and no chance of anyone messing with them. I'll
keep the master copy of this WB, with Tom's code in it, and present the club
with a new WB each year they request it, with the deletions mentioned above.

I bet Tom's been sitting watching and laughing at me all this
time....lol....can't really blame him...(vbg)

I do like the way you made the 53 button invisible though....might play
around with that, putting it in Tom's code.....if I can't make it work, no
big deal.

Anyway.....I really do appreciate all your time and Tom's too. I'm slowly
picking up on things in Excel but, as you can tell, I have a long way to go.

Thanks again and Thanks again to you Tom<< I know he's watching...:)

Hope you both have a great day,

Don

"Dave Peterson" wrote:

Ps. When you add 7 to your date (myStartDate = myStartDate + 7), that's
equivalent to me using: myDate = myStartDate + (7 * (iCtr - 1))

(discounting the variable names, though.)



Dave Peterson wrote:

You reversed the purposes of my variables <bg!

I wanted myStartDate to be the first date used--it would never change. myDate
would change for each caption.

But that doesn't matter.

Don wrote:

Hello again Dave,

I think I solved the problem....myDate was not defined....using the below
code I believe it's working correctly now.

Option Explicit
Private Sub UserForm_Initialize()

Dim iCtr As Long
Dim myStartDate As Date
Dim myDate As Date

myDate = FirstDOWinMonth(DateSerial(Year(Date) + 1, 7, 1), vbWednesday)

myStartDate = FirstDOWinMonth(DateSerial(Year(Date), 7, 1), vbWednesday)
MsgBox myStartDate
For iCtr = 1 To 53
Me.Controls("commandbutton" & iCtr).Caption = Format(myStartDate, _
"ddd-mm/dd/yy")
myStartDate = myStartDate + 7
If myDate < DateSerial(Year(myStartDate), Month(myStartDate), Day
_(myStartDate)) Then
Me.Controls("commandbutton" & iCtr).Visible = False
End If
Next iCtr

End Sub

Thank you for your patience here, I do really appreciate your help, you
folks are really great on here,

Don

"Dave Peterson" wrote:

First, the easy portion:

myStartDate = FirstDOWinMonth(DateSerial(Year(Date), 7, 1), vbWednesday)

Dateserial() looks for a year, month, and a day. I just changed the month to 7
(July).

Second, why bother changing the caption if the caption would change next year.

But if you wanted to, then you're going to have to find out why the code Tom
suggested didn't work for you. It worked fine for me.

If I wanted to change those captions, I'd use captions like Week01, Week02, ...,
Week52 (or live with Commandbutton1 through Commandbutton52). I guess it could
make work designing the form a little easier.

ps.

There was a mistake in the first version of the code I gave you.

And sometimes years can have 53 "weeks" in it based on the starting date.


Option Explicit
Private Sub UserForm_Initialize()

Dim iCtr As Long
Dim myStartDate As Date
Dim myDate As Date

myStartDate = FirstDOWinMonth(DateSerial(Year(Date), 7, 1), vbWednesday)

For iCtr = 1 To 53 '52 ???????
myDate = myStartDate + (7 * (iCtr - 1))

'not sure if you'd use this
If myDate DateSerial(Year(myStartDate) + 1, _
Month(myStartDate), Day(myStartDate)) Then
Me.Controls("commandbutton" & iCtr).Visible = False
End If

Me.Controls("commandbutton" & iCtr).Caption _
= Format(myDate, "ddd-mm/dd/yyyy")
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



Don wrote:

G'morning Dave,

Wow, the function code eliminates the need for reading a value from a cell,
that's neat. I've changed a couple of things in the code to reflect
Wednesday as the DOW and also the formatting to reflect the formatting I have
already started with and everything works great up to that point. I'm at a
loss as to what to change to start this on the first Wednesday in July. This
WB is based on a fiscal year.

Also, as we've seen from the above trials, the actual Caption in the VBE
properties window does not change, so this code will have to run everytime
the Form is called. I don't think that's a problem, but it does raise the
question.....Can the actual Caption property in the VBE properties window of
a CommandButton be change permanently via code? None of my tries at doing
this have been successful.

I really thank you and Tom both very much for helping me here and one more
bit of help (starting this in July) and this thing will work perfectly.

Thanks again,

Don

"Dave Peterson" wrote:

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 Peterson


--

Dave Peterson

--

Dave Peterson