PDA

View Full Version : Change name of VBA Created button



cbjamel
08-15-2010, 03:52 PM
:banghead:I have a macro to create a button once they have entered the right info to email me the file. I want to change the buttons name from button to Send.

Thanks,
Shane

Sub Send()
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("G*** Order Form")

If (ws.Cells(20, 11).Value = "c*****") Then

Worksheets("G*** Order Form").Buttons.Add 470, 488, 40, 15

Worksheets("G*** Order Form").Buttons.Add("cmdGenerate").Object.Caption = Send

Else
ws.Cells(20, 11).Value = " "




End If
End Sub

Sub Mail_workbook_1()


Dim wb As Workbook
Dim I As Long

Set wb = ActiveWorkbook

If Val(Application.Version) >= 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If

On Error Resume Next
For I = 1 To 3
wb.SendMail "c****@msn.com", _
"G*** Order"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
End Sub

Emailing working fine, adding the button is fine I just want to change it's name?

Tinbendr
08-15-2010, 04:46 PM
Welcome to VBA Express!

Maybe...

Worksheets("G*** Order Form").Buttons.Add("cmdGenerate").Object.Caption = "Send"

cbjamel
08-15-2010, 06:15 PM
:banghead:I have a macro to create a button once they have entered the right info to email me the file. I want to change the buttons name from button to Send.

Thanks,
Shane

Sub Send()
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("G*** Order Form")

If (ws.Cells(20, 11).Value = "c*****") Then

Worksheets("G*** Order Form").Buttons.Add 470, 488, 40, 15

Worksheets("G*** Order Form").Buttons.Add("cmdGenerate").Object.Caption = Send

Else
ws.Cells(20, 11).Value = " "




End If
End Sub

Sub Mail_workbook_1()


Dim wb As Workbook
Dim I As Long

Set wb = ActiveWorkbook

If Val(Application.Version) >= 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If

On Error Resume Next
For I = 1 To 3
wb.SendMail "c****@msn.com", _
"G*** Order"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
End Sub

Emailing working fine, adding the button is fine I just want to change it's name?
Nope I get a Run time error 449, argument not optional???

Thanks for trying.

Shane

Artik
08-15-2010, 06:46 PM
Sub Send()
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("G*** Order Form")

If (ws.Cells(2, 1).Value = "c*****") Then

With ws.Buttons.Add(470, 488, 40, 15)
.Caption = "Send"
.OnAction = "Mail_workbook_1"
End With

Else
ws.Cells(2, 1).Value = " "

End If

Set ws = Nothing

End Sub

Artik

cbjamel
08-15-2010, 08:12 PM
Solved works like charm.

Thanks,
Shane

geekgirlau
08-15-2010, 08:24 PM
Wouldn't it be simpler to just hide the button, and make it visible only when your criteria is met?