Running on second click of control button

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

ageren

Board Regular
Joined
Jul 30, 2007
Messages
60
Code:
Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

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


End Sub
 

RichardU

Board Regular
Joined
Aug 3, 2007
Messages
98
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

Active Member
Joined
Jul 7, 2007
Messages
354
Hi, Thanks for your replies.

I have done something along the lines suggested by Richard:

Code:
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

Active Member
Joined
Jul 7, 2007
Messages
354

ADVERTISEMENT

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

Code:
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:
Code:
    Range("A4:X4").Select
    Selection.Copy
    Range("Y4").Select
    Selection.PasteSpecial Paste:=xlPasteValues

I'm really puzzled by this.

vcoder
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I don't know why your code isn't working but there is no need to Select

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

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps you need to qualify the sheet reference:

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

or perhaps

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

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi VogII,

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

Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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.
Code:
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,982
Members
414,489
Latest member
Xlambda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top