Running on second click of control button

[vbacoder]

Hi everyone,

Is there a way to force a control botton linked to some VBA code to run the macro on not the first, but the second click?

Many thanks,

vcoder

 

[ageren]

Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

'write your VBA codes here....
...
...
...
...


End Sub

 

[RichardU]

Thats if its a double click!! Not a second click i.e. click now then 10 mins later click again

What you want is a variable to = 0 when the form opens, each time the button is clicked add one to the variable, then do the code on variable = 2

Simple

 

[vbacoder]

Hi, Thanks for your replies.

I have done something along the lines suggested by Richard:

Public s As Integer
Private Sub commandbutton_Click()
If s = 0 Then
        commandbutton.Caption = "Check"
        MsgBox "Click Yes To Proceed, Esc to abort"
        roll_button.Caption = "Click again to proceed"
        s = 1
    ElseIf s = 1 Then
        MsgBox "Confirmation: Macro To Run?"
        commandbutton.Caption = "Running"
            Call execution           'calling subrouting to execute
        s = 0
    End If
End Sub

This overcomes the problem of accidentally pressing the macro control button, by prompting the user for confirmation. The problem is that when I call my subrouting to execute the macro, copy>paste operations in this macro that once worked fine, now kick up errors. In fact, I have noticed that any form of .select, .value, .copy operation now does not work. I really don't know what I am doing wrong. I tried to add a basic copy>paste instruction to go back to basics, and this executed fine for some reason. So it seems that the code that was present prior to adding sub commandbutton_Click() has suddenly become invalidated in some way. Strange or what?

vcoder

 

[vbacoder]

The same thing happens when I use the double click code, kindly suggested by Ageren:

Private Sub button_dblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    MsgBox "Click OK to proceed, Esc to cancel"
    Call execution_code
    
End Sub

Error message:
'Run-time error '1004'
Select method of range class failed'

Debugger gets stuck on the first line of the following:

...in sub execution_code:

    Range("A4:X4").Select
    Selection.Copy
    Range("Y4").Select
    Selection.PasteSpecial Paste:=xlPasteValues

I'm really puzzled by this.

vcoder

 

[VoG]

I don't know why your code isn't working but there is no need to Select

Range("A4:X4").Copy
Range("Y4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

 

[vbacoder]

I tried this, and it solved the problem with the error message (I don't get one anymore), but the section that should have been copied and pasted remains untouched. I noticed this with other operations I tried out to achieve the same effect (i.e. '.value'... '.value' etc.) and these too sometimes avoided the error but did nothing.

I still don't see why the previous code threw up an error... perhaps this could lead to the source of the problem.

vcoder

 

[VoG]

Perhaps you need to qualify the sheet reference:

Sheets("Sheet1").Range("A4:X4").Copy
Sheets("Sheet1").Range("Y4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

or perhaps

Sheets("Sheet1").Activate
Range("A4:X4").Copy
Range("Y4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

 

[vbacoder]

Hi VogII,

Thanks for your help. In my code, I use the following prior to the copy>paste statements:

Sheets("sheet1").Select

This seems to work fine, and without generating errors, the problem arises once in the sheet. I'm thoroughly mystified...

Must be something with the way in which the commandbutton click code is implemented. When I draw a normal control button, and assign a macro to it, everything works fine. I want this to work fine, with the option of being able to run the macro oon the second click of the control button.

vcoder

 

[tusharm]

I don't know what exactly commandbutton is for that matter roll_button but you can do your checking using just the button caption. More importantly, you need to look up MsgBox and see how to use it as a function and not a statement. Finally, I hate developers who ask me if I meant to click on a button. It's a level of condescension I hope I never stoop to. So, if I was in your shoes I would not use either of the 2 MsgBox's. But, that's me.

Option Explicit

Private Sub CommandButton1_Click()
    If CommandButton1.Caption = "Check" Then
        If MsgBox("Click OK To Proceed else Cancel", vbOKCancel) = vbOK Then _
            CommandButton1.Caption = "Click again to proceed"
    Else
        CommandButton1.Caption = "Check"
        If MsgBox("Confirmation: Macro To Run?", vbOKCancel) = vbOK Then _
            Call Execution           'calling subrouting to execute
        End If
    End Sub
Sub Execution()

    End Sub

Hi, Thanks for your replies.

I have done something along the lines suggested by Richard:

Public s As Integer
Private Sub commandbutton_Click()
If s = 0 Then
        roll_button.Caption = "Check"
        MsgBox "Click Yes To Proceed, Esc to abort"
        roll_button.Caption = "Click again to proceed"
        s = 1
    ElseIf s = 1 Then
        MsgBox "Confirmation: Macro To Run?"
        roll_button.Caption = "Running"
            Call execution           'calling subrouting to execute
        s = 0
    End If
End Sub

This overcomes the problem of accidentally pressing the macro control button, by prompting the user for confirmation. The problem is that when I call my subrouting to execute the macro, copy>paste operations in this macro that once worked fine, now kick up errors. In fact, I have noticed that any form of .select, .value, .copy operation now does not work. I really don't know what I am doing wrong. I tried to add a basic copy>paste instruction to go back to basics, and this executed fine for some reason. So it seems that the code that was present prior to adding sub commandbutton_Click() has suddenly become invalidated in some way. Strange or what?

vcoder