Button on condition

[Ricky Pang]

Hello Experts,
I'm having trouble adding a Button because the macro recorder has preset
the Button name to be Button 1. If I were to delete this button and run
the macro again, the code will try to name the button as Button 1 again
but Excel wants to increment it by 1 digit. What's the work around?

Before this button appears, I'd like to have a msgbox first. Lastly,
what's the code for deleting this button after execution?

'Msgbox Required
'"More changes may be required" --- Line 1
'"Are you ready to Export?" --- Line 2
'Option Yes = Call Export macro
'Option No = Stop any other actions and run this following code only

Application.CommandBars("Forms").Visible = True
ActiveSheet.Buttons.Add(232.5, 8.25, 93.75, 36).Select
Selection.OnAction = "ChangeRevenueTitles"
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Export"
With Selection.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
End With
Range("A5").Select
ActiveWindow.FreezePanes = True
Application.CommandBars("Forms").Visible = False
End Sub


'Within the Call Export code, what's the code for deleting this
button after execution?
'and if no button exists, no error message (something like On Error
Resume Next)

Sincerely, thank-you in advance.

Ricky

 

[Norman Jones]

Hi Ricky,

I am not sure that I have understood fully your scenario, but try something
like:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim myButton As Button
Dim res As VbMsgBoxResult
Dim sStr As String

Set SH = ActiveSheet '<<==== CHANGE

sStr = "More changes may be required" & vbNewLine _
& "Are you ready to Export?"

res = MsgBox(Prompt:=sStr, Buttons:=vbYesNo)

If res = vbYes Then
Call Export
End If

Set myButton = SH.Buttons.Add(232.5, 8.25, 93.75, 36)
With myButton
.OnAction = "ChangeRevenueTitles"
.Caption = "Export"
With .Characters.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
End With
End With
End Sub
'<<=============


'=============>>
Sub ChangeRevenueTitles()
'Your code
ActiveSheet.Buttons(Application.Caller).Delete
End Sub
'<<=============

 

[Ricky Pang]

Thank-you so much Norman,
This is exactly what I'm looking for. It worked perfectly. I
appreciate your help.

Ricky