|
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
Tom, Thanks for looking at this, and yes, that is what I'm 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'd 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? TIA, Don "Tom Ogilvy" wrote: 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 |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
What's the name of the userform? Tom used UserForm1. Change it if you have to.
Did you put the code in the same workbook as the userform? Don wrote: Tom, Thanks for looking at this, and yes, that is what I'm 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'd 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? TIA, Don "Tom Ogilvy" wrote: 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 -- Dave Peterson |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
Dave,
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. Don "Dave Peterson" wrote: What's the name of the userform? Tom used UserForm1. Change it if you have to. Did you put the code in the same workbook as the userform? Don wrote: Tom, Thanks for looking at this, and yes, that is what I'm 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'd 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? TIA, Don "Tom Ogilvy" wrote: 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 -- Dave Peterson |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
Hi Dave,
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'll try Tom's 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, 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 |
"Renaming Form Buttons From Macro"
Hi again Dave,
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. Thanks again, 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 |
"Renaming Form Buttons From Macro"
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. Don wrote: Hi again Dave, 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. Thanks again, 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 |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
Yep....Not if but when they change OP's, they'll have to seek me out....lol
This all started a little over a year ago when my wife accepted the job of keeping track of some data for a club she belongs to...I made up a small WB and did all the entry manually. I developed some Chart Sheets and manually controlled some printouts. They liked the output so well, they asked if I could pass the program on to the next person in line for that job. Knowing that the manual entry would confuse some people, I coded all the things I was doing manually and created the forms that I desired (Two forms involved). The WB is pretty user-friendly now and protected as much as I could so the OP really would have to work a bit to get at the code or even the worksheets. A thought just came to mind.....what's this new code going to do after the first of the year....when it initializes, won't it go to the first Wednesday of July of the new year? If so, could a cell reference be made to the current year (which could be entered as text manually, so that it would not change after the first of year) and called into the code you developed? Maybe I'm approaching this all wrong. I wish I could get Tom's code to work, and see if it actually changes the Caption in the VBE properties window. You said it ran fine for you.....did you happen to look at the Caption in the VBE properties window to see if it actually changed it there? Again, thanks a lot for your time here, Don "Dave Peterson" wrote: 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. Don wrote: Hi again Dave, 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. Thanks again, 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 |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
First, yep. I checked the properties of the captions after running Tom's code.
It worked fine. I could get a different error--not the one you described, though. If I turned off a security option in excel: tools|macro|security|trusted publishers tab uncheck "trust access to Visual Basic Project" But I got a 1004 error: Programmatic access to Visual Basic Project is not trusted. If you changed Tom's code, maybe you changed something you shouldn't have. You may want to post the code you used and indicate the line that caused the error. And yep. I'm not sure when the captions should change, but if you can decide on a date, you could put that into the code. But it still might be a problem... Say you want to change displayed years on (or about July 1st). If someone wants to get a headstart on June 30, they may want to see next year's captions. But if someone has slacked off, they may want to see last year's captions--so they can finish up the previous year's data entry. I think I'd ask... Option Explicit Private Sub CommandButton54_Click() Unload Me End Sub Private Sub UserForm_Initialize() Dim iCtr As Long Dim myStartDate As Date Dim myDate As Date Dim DateForNewCaptions As Date Dim myYear As Long DateForNewCaptions = DateSerial(Year(Date), 9, 1) If Date = DateForNewCaptions Then myYear = Year(DateForNewCaptions) Else myYear = Year(DateForNewCaptions) - 1 End If 'or just ask myYear = Application.InputBox("What year should I start with?", _ Default:=myYear, Type:=1) If myYear < 2000 _ Or myYear 2020 Then MsgBox "Please try again" Exit Sub End If myStartDate = FirstDOWinMonth(DateSerial(myYear, 7, 1), vbWednesday) For iCtr = 1 To 53 myDate = myStartDate + (7 * (iCtr - 1)) 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: Yep....Not if but when they change OP's, they'll have to seek me out....lol This all started a little over a year ago when my wife accepted the job of keeping track of some data for a club she belongs to...I made up a small WB and did all the entry manually. I developed some Chart Sheets and manually controlled some printouts. They liked the output so well, they asked if I could pass the program on to the next person in line for that job. Knowing that the manual entry would confuse some people, I coded all the things I was doing manually and created the forms that I desired (Two forms involved). The WB is pretty user-friendly now and protected as much as I could so the OP really would have to work a bit to get at the code or even the worksheets. A thought just came to mind.....what's this new code going to do after the first of the year....when it initializes, won't it go to the first Wednesday of July of the new year? If so, could a cell reference be made to the current year (which could be entered as text manually, so that it would not change after the first of year) and called into the code you developed? Maybe I'm approaching this all wrong. I wish I could get Tom's code to work, and see if it actually changes the Caption in the VBE properties window. You said it ran fine for you.....did you happen to look at the Caption in the VBE properties window to see if it actually changed it there? Again, thanks a lot for your time here, Don "Dave Peterson" wrote: 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. Don wrote: Hi again Dave, 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. Thanks again, 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 |
"Renaming Form Buttons From Macro"
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 |
"Renaming Form Buttons From Macro"
I should have followed up to this post (not that other one):
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. Dave Peterson wrote: First, yep. I checked the properties of the captions after running Tom's code. It worked fine. I could get a different error--not the one you described, though. If I turned off a security option in excel: tools|macro|security|trusted publishers tab uncheck "trust access to Visual Basic Project" But I got a 1004 error: Programmatic access to Visual Basic Project is not trusted. If you changed Tom's code, maybe you changed something you shouldn't have. You may want to post the code you used and indicate the line that caused the error. And yep. I'm not sure when the captions should change, but if you can decide on a date, you could put that into the code. But it still might be a problem... Say you want to change displayed years on (or about July 1st). If someone wants to get a headstart on June 30, they may want to see next year's captions. But if someone has slacked off, they may want to see last year's captions--so they can finish up the previous year's data entry. I think I'd ask... Option Explicit Private Sub CommandButton54_Click() Unload Me End Sub Private Sub UserForm_Initialize() Dim iCtr As Long Dim myStartDate As Date Dim myDate As Date Dim DateForNewCaptions As Date Dim myYear As Long DateForNewCaptions = DateSerial(Year(Date), 9, 1) If Date = DateForNewCaptions Then myYear = Year(DateForNewCaptions) Else myYear = Year(DateForNewCaptions) - 1 End If 'or just ask myYear = Application.InputBox("What year should I start with?", _ Default:=myYear, Type:=1) If myYear < 2000 _ Or myYear 2020 Then MsgBox "Please try again" Exit Sub End If myStartDate = FirstDOWinMonth(DateSerial(myYear, 7, 1), vbWednesday) For iCtr = 1 To 53 myDate = myStartDate + (7 * (iCtr - 1)) 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: Yep....Not if but when they change OP's, they'll have to seek me out....lol This all started a little over a year ago when my wife accepted the job of keeping track of some data for a club she belongs to...I made up a small WB and did all the entry manually. I developed some Chart Sheets and manually controlled some printouts. They liked the output so well, they asked if I could pass the program on to the next person in line for that job. Knowing that the manual entry would confuse some people, I coded all the things I was doing manually and created the forms that I desired (Two forms involved). The WB is pretty user-friendly now and protected as much as I could so the OP really would have to work a bit to get at the code or even the worksheets. A thought just came to mind.....what's this new code going to do after the first of the year....when it initializes, won't it go to the first Wednesday of July of the new year? If so, could a cell reference be made to the current year (which could be entered as text manually, so that it would not change after the first of year) and called into the code you developed? Maybe I'm approaching this all wrong. I wish I could get Tom's code to work, and see if it actually changes the Caption in the VBE properties window. You said it ran fine for you.....did you happen to look at the Caption in the VBE properties window to see if it actually changed it there? Again, thanks a lot for your time here, Don "Dave Peterson" wrote: 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. Don wrote: Hi again Dave, 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. Thanks again, 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 -- Dave Peterson |
"Renaming Form Buttons From Macro"
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 |
| All times are GMT +1. The time now is 05:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com