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

 

[vbacoder]

Hi Tusharm,

Thanks for your post. I agree, the message box code could be improved to eliminate some of the unnecessary steps, but this is new to me so I was making is a bit 'flowery' to learn the ropes. Thanks for your update with the OK and Cancel options - this looks much better.

The main objective I want to accomplish is to prevent the macro running following an accidental click.

The problem I am facing when incorporating this 'click check' to my existing macro is that, in its new form, it does not want to carry our basic functions that once ran fine. For e.g. I get errors at range("...").select operations, that do not arise when the 'click check' code is not present. When using other code to copy data to new locations (.copy ..., and .value... .value), no errors arise, but copying is not successful either - it's like the operation is skipped altogether.

I really want to solve this problem - it's really bugging me and I'm spending too much time on it...

vcoder

 

[vbacoder]

Okay, I think I see what's happening.

The macro is not in a separate module. It is assigned to a control buttin in a sheet, and as such can only operate on data in that sheet. This is why references to other sheets are rejected. When I try to manipulate data in the same sheet, everything goes fine.

My problem seems to be assigning a macro from a module to a control button. After drawing the control button, I can select 'view code' which opens a private sub, embedded in the sheet in which the button sits. If I draw a generic shape and assign a macro from a module to this, functions like CommandButton1.Caption ... are recognised. So I need a formal control button to make the code work, but the downside is that it will only perform operations on the same sheet...

There must me a way around this. Can I assign a macro from a module to a control button, such that CommandButton.. statements work?

vcoder

 

[GMorris]

I've run into similar problems before, and I ran across it while trying to generate random lottery numbers with different command buttons. What I finally ended up doing was to Call a Sub/Function from each button's Click code block. If you put the code to run in a standard module and Call it from the click event, your problem should go away, but I've never had any luck trying to "hook" a command button click and run it in some other place. I hope that makes sense...

 

[tusharm]

Okay, I think I see what's happening.

The macro is not in a separate module. It is assigned to a control buttin in a sheet, and as such can only operate on data in that sheet. This is why references to other sheets are rejected. When I try to manipulate data in the same sheet, everything goes fine.
{snip}

No, that's not correct. If you properly reference the sheet you can refer to ranges in it. In fact, you can even refer to ranges in a sheet in another workbook. Just qualify the references as in

worksheets("some other sheet").range("a1:B10").value=1
workbooks("another workbook").worksheets("sheet in other book").range("A1:B10").value=1

As far as your problem goes, you may want to share the code (or at least the relevant portions if the actual code is too long).

 

[vbacoder]

Hi,

The code is in preceeding posts in this thread.

I have tried to reference sheets in the same workbook using sheets("...").select, and once the program reaches an operation in this sheet, I get an error. It just doesn't work...

If I use a normal button (i.e. one that I have drawn from the drawing tab) and 'assign macro' to it, this can use macros in modules, which in turn reference numerous sheets in the same workbook. The problem is that I cannot get the 'commandbutton' click checking code to execute when I do this. It only works with a 'formal' command button, Data > Customise > commands > control box > control button .... and this only seems to allow the code to refer to operations on the sheet in which the command button sits.

vcoder

 

[vbacoder]

What I finally ended up doing was to Call a Sub/Function from each button's Click code block. If you put the code to run in a standard module and Call it from the click event, your problem should go away, but I've never had any luck trying to "hook" a command button click and run it in some other place.

Hi GMorris,

Thanks for your advice. Do you mean you added a call to another sub in the first line of the click code? I am calling subs from the click code block, but the problem is that when the program execution reaches an operation (in the called sub) that involves a sheet other than the one containing the command button, it breaks down and I get an error. Did you experience this?

vcoder

 

[vbacoder]

If you put the code to run in a standard module and Call it from the click event, your problem should go away

I see what you mean now. I tried this initially, but I wasn't able to call modules from the click block (I used the standard call... statement). I could only call subs from the same module. I would be very grateful if you could tell me how to reference a separate/external module in the same workbook.

Many thanks for your continued assistance with this matter.

vcoder

 

[vbacoder]

I think the problem boils down to the types of modules used in excel: Standard, Sheet, Class and User form.

In order to get a button to execute the double-click function I want, I need to use a designated command button - I cannot simply draw a shape and assign a macro to it, as the double-click code will not work that way. If I could, this would solve my problem.

When you use a command button, you have only one option to assign code to it - through 'View Code' - which opens a private module within the sheet - i.e. a 'Sheet' module from the classification in the 1st paragraph.

The Sheet module appears not to be able to call subs or modules external to it. Nor can it execute operations on sheets other than the one that it is linked with.

What I want is a button that i) runs a macro on a second click (i.e. after confirmation that accidental clicking hasn't arisen), and ii) is capable of calling subs located in other modules, which in turn can execute operations in any sheet in my workbook.

Has anyone tried something similar and succeeded?

vcoder

 

[GMorris]

First, you need to get a better understanding of the available modules and how they interact. The modules that are assigned to Sheet, Workbook and UserForm objects are, in fact, Class modules. This would not be a big deal, but over time I've discovered that the best way to code anything that will be shared (like little utilities and such) is in a standard Module, one that you add yourself or that is created by recording a macro. If you are going to use your own class, you must then create a reference to it either in an event or in a standard module. Once you code with Office for a few years, this will all make SO much more sense! I rarely use the Workbook or Sheet modules except when I have code that will ONLY use those objects (like Workbook_Open and such). The rest of the time I put any shared code in a standard module, or, if the project is big enough and warrants the extra effort, I'll create my own classes to get a little more object-oriented. If you've never had to use your own classes, this probably won't be of much use either. If the whole Class thing doesn't make a lot of sense now, it will later, but think about the fact that the only Macros you can use from the Excel Menus are the ones defined as Public Subs in standard Modules (well, they will show up preceded by ThisWorkbook! or something, but don't expect them to always work as intended)...

But, to get on with it, what I generally do is make a Module and put something like:

Public Sub DoSomethingCool(x as String)
	Debug.Print "Here is your argument: " & x
End Sub

Then, in the Form or other event code something like:

Private Sub cmdDoSomething_Click()
	DoSomethingCool "My String!"
End Sub

The situation you are describing would probably work best by using a static variable to keep track of how many clicks have been caught, then resetting it in the code that finally executes so that the count can start over again. Potentially, you could drive a user crazy by making them click the button a couple of dozen times before the static var reaches the count that eventually makes the code run. Right off, I can't recall how static variables act in a Form event, but I'm know they work well in a standard Module, where you can define Global variables, system dll calls, etc. that you can't with a Class Module (whether it's your class or one of Excel's). Again, I hope that makes sense, but you seem to know enough about it to figure it out from there.

Oh, and I still have a copy of my Lotto Generator workbook somewhere, which is quite old (Excel97 or 2000, I think), but does illustrate some of these things if you want a copy. Just let me know and I'll dig it up and zip it up for you. Bear in mind that I was still learning at the time, so it's a little messy in places, but is commented pretty good to help make up for it. There aren't any user-defined Classes in it, though in hindsight it probably would have been a good idea in some places.