Code does not run when assigned to a button

  • I have created code that works perfectly when I select 'run' from the vba screen, however when I assign this code to a button it fails to run in the same way and encounters errors.

    The code I am using is as follows:

    Is there a reason why this may be occurring?


  • Re: Code does not 'run' when assigned to a button


    Is there a reason why this may be occurring?

    Obviously there is, but there's not enough information in your post to say why.


    it fails to run in the same way and encounters errors

    You should expend that statement - what does it do, or not do, depending how you start it? What errors occur? (Probably 1004, though - but that's just as unhelpful).

    What type of button is it? ActiveX or Forms?
    What type of module contains the code? (Standard code module/Class module/worksheet class/userform)...?

  • Re: Code does not 'run' when assigned to a button

    Okay, thank-you. Let me try that again.

    I created code that executes correctly when I select Run from the VBA screen. The code is within a Standard Code Module (see below).

    I then assigned the code to Button (Form Control) which has caused the code to run incorrectly.

    The first issue is to do with the part of the code that finds a match on wsSource and then copies the columns to wsTarget.

    When I select Run in the VBA screen this occurs (which is correct)


    When I select the Button the following occurs (which is incorrect):


    The second issue is that when I select the Button I encounter the following error:

    Run-time error '6': Overflow

    When I select Debug I can see that the part of the code that is causing this error is as follows:

    maxRowIndex = ActiveCell.Row - 11

    Any assistance would be greatly appreciated.


  • Re: Code does not 'run' when assigned to a button

    Starting at the simplest... The Error 6 is a result of an invalid assignment to a particular data type. It is such a fundamental concept in any programming language that you need to understand it yourself to avoid it in future. You could start by reading this (That page is from an Access book but the concept is the same for Excel - it's all VBA).

    The remaining issues are not program errors as such but logic errors - it is not doing as you expect. The only pointer I can give here is that there are some references to ranges that are not qualified with a sheet name - it is possible the incorrect sheet is being updated.

    Without a copy of the workbook I can't be any more exact, a quick read of the code doesn't show any glaring issues, so this is one for you to try debugging.

    Debugging is an essential part of any development, you can step through the code line by line testing the values of variables before a line executes to confirm it is working with the right values, you can change the value stored in a variable or even change which like will execute next (within limits). For a good basic primer on debugging, see this page.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!