View Full Version : Solved: Command Button Naming
zoom38
03-09-2009, 08:49 PM
Hello all, I believe this is a simple one but not simple enough for me. I am trying to change the caption of a command button when i invoke a macro from module1. The sheet name is named with the year and my macro creates a new worksheet named year +1. I now want the caption of the command button on the new sheet to be named "Create" & year +2 & "Worksheet" but I cannot figure it out. I have not been able to do it from within module1 and when I place CommandButton1.Caption = "Create" & year + 2 & "Worksheet" changes the caption on the original sheet, not the new sheet. Any help would be appreciated.
Thanks
Gary
zoom38
03-09-2009, 09:51 PM
Took me a while but I finally figured it out. The following line did the trick:
'Change The Command Button Caption
ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "Create " & year + 2 & "Worksheet"
Thanks
MaximS
03-09-2009, 09:53 PM
i think you need to reference it better:
Worksheets(2).CommandButton1.Caption = "Create" & year + 2 & "Worksheet"
Greetings zoom,
Sub copysheet()
Dim strShName As String
Dim wks As Worksheet
strShName = ActiveSheet.Name
ActiveSheet.Copy After:=Sheets(ActiveSheet.Index)
Set wks = ActiveSheet
wks.Name = CStr(CInt(strShName) + 1)
wks.OLEObjects("CommandButton1").Object.Caption = "Create " & CStr(CInt(strShName) + 1)
End Sub
I see you marked it solved; was already about to send, so in case of any use...
Mark
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.