How to rename from button with macro in Microsoft Excel

[Kanshin]

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.

[GaganjyotTechie]

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

[Essien]

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.

[Shurod]

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).

[Gania]

This form has 52 buttons, one for each week of a fiscal year. I posted as only three buttons for this forum. 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 will 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.

[Alfons]

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

[JustineBB]

Thanks for looking at this, and yes, that is what I am trying to do. Create a method that I can run once a year that will only redo the caption on the buttons but not disturb the underlying code. I tried your code and get the error msg below: Run-time error '1004': Method 'VBProject' of object '_Workbook' failed. Then clicking on Debug I get the following line highlighted: Set vc = ThisWorkbook.VBProject.VBComponents("UserForm1"). I entered your code in a standard module and just ran it from VBE for now. I would like to run it from a button on Sheet1, but that is not absolutely necessary. BTW, I'm running Excel2003 and Windows XP Media Center Edition, Service Pack 2, if that matters. What am I doing wrong here.

[-Deven-]

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 am thinking now that this code may just have to run every time 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 have stated in my reply to him.

[JyotiT]

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.

[Go!pee]

The Form is/was named UserForm1. And yes, I did put the code in a module (inserted a module from VBE) in the same workbook. As I stated above, I didn't link the code to a control button or a key stroke combination, but ran it directly from VBE.

[Panop]

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 every time 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.

[Shurod]

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 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. 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

[AnitG]

Works great. And I did catch the "7" in your original posting....I just made a form with seven buttons to test it with and it worked out fine. As to the 53 week thing, I guess I could add another button to anticipate this, but for right now the 52 week year works out fine. I'll test it out and find out what year this becomes a problem. There's a couple of reasons why I was trying to change the caption first, I thought I had to change the actual caption in order to accomplish my goals it was my first try and your first post in this thread that showed me that the text in the button could change without the actual caption in VBE changing. Second....it became a challenge to me, as so many things in Excel do. Third I think and I think this is still true, it would be less code running when the Form was called...not a significant issue in this case, but still true. I will try code again and see what comes of that, but right now I'm still getting the same error msg from it that I posted. I'm probably doing something very simple wrong posting it in the wrong place or something like that, but I'll continue to test it. Again, my thanks to you for your time and effort.

[Koman]

Sorry to keep pestering you with this, but you introduced another potential problem and I've been playing with that last bit of code you posted. I created a test form with 53 buttons on it and ran this code:

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)
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) + 1, Month(myStartDate),
Day(myStartDate)) Then
Me.Controls("commandbutton" & iCtr).Visible = False
End If
Next iCtr
End Sub

I used the msgbox to verify that the code started on the correct day/date...it did. I did have to revise one line to..."myStartDate = myStartDate + 7" This worked out fine. But the 53rd button still gets captioned and this is with a 52 week year. I removed the If and End If statements, leaving the code between them and the form initialized showing no buttons, which I suspected it should. Something is wrong with the If statement in determining when to change the visible property of button 53. I'll play with it a bit more but I doubt I have the knowledge to correctly fix it.

[Leanne]

Each time through the loop, you're changing the myStartDate value. That was the same problem I had with my original code. Instead, I used another variable (myDate) to add 7 days for each counter. And I used myDate in the caption and to compare with the original myStartDate. And the code may do more things, but I don't see it as a problem. In fact, I'd see the problem more like what happens if you're no longer in that same position when the new fiscal year comes around. Will the users have to seek you out or make the changes themselves. Computers are fast; humans are slow <vbg>.