+ Reply to Thread
Results 1 to 10 of 10

Question: user selection to call a sub (module)

  1. #1
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    63

    Question: user selection to call a sub (module)

    Dear all....

    I have made several calculation using macro and each of the process is placed in a module under different subname

    What I would like to have is to let a user choose which calculation they would like to do.

    For the selection, I have made a datavalidation process which allows only "Y" and "N" as input

    What I am unable to do is to create a macro based on selection of the user to call the respective sub.

    See attachement to make it clearer.

    Thanks before!
    Attached Files Attached Files
    Last edited by a_driga; 01-09-2012 at 05:54 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Question: user selection to call a sub (module)

    Hello
    I had a look at your file.. I can't see what you're trying to achieve + there are no macros Would you like to try again?

  3. #3
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Question: user selection to call a sub (module)

    Quote Originally Posted by Philb1 View Post
    Hello
    I had a look at your file.. I can't see what you're trying to achieve + there are no macros Would you like to try again?
    Hi Phil,

    It is just a simplification of my actual files..
    I have made a new file that hopefully explain my objective (please check on my first post)

    What I want is that if any of the cell from B2 to B4 is Y then a respective sub will be called, otherwise if it is N then the corresponding sub will not be called.
    It is to give a freedom for a user to choose which sub to be run.
    Last edited by a_driga; 01-09-2012 at 05:59 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    06-17-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003,excel 2007
    Posts
    678

    Re: Question: user selection to call a sub (module)

    see the attchment "driga.xlsm"- this is just a sample file

    see the text under the three buttons which explain how it can be done.

    post feedback
    Attached Files Attached Files

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Question: user selection to call a sub (module)

    You need to save the file as .xlsm if you want to retain the macros to be uploaded here.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  6. #6
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Question: user selection to call a sub (module)

    Hi Venkant,

    Thanks for your answer, however it does not give what I want..
    What I want is inside the cell, a user can choose "Y" or "N". In my example there are three cells to be filled with either Y or N.
    And based on that choice, another sub/module is called (call if Y, not call if N). If it is filled with N, then next cell will be checked..

    Your solution basically required me to create different block, each corresponding to one sub/module.

  7. #7
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Question: user selection to call a sub (module)

    How about something like this. It's a start anyway.
    You haven't said how many rows you are going to use. Is it only the three?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Question: user selection to call a sub (module)

    Hi Phil

    Sorry for late reply..

    I don't understand with the way your macro works..
    I have never used usermodule before, just using module :D

    So could you explain it to me?

  9. #9
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Question: user selection to call a sub (module)

    I finally decided to use userform, however I got an error message when calling a module:
    Syntax for userform:
    Private Sub bandung_Click()
    Call proc1
    Call bandung
    End Sub

    I have two modules namely proc1 and bandung.

    The error message is:
    Compile error:
    Invalid use of property

  10. #10
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Question: user selection to call a sub (module)

    If you double click Sheet1 in the VBA editor, it will show the event change macro. Its looking for change in rows 2, 3 & 4 in column 2, which is column B. If the change is a Y, it triggers the event change macro & shows Userform1.
    Right click Userform1 in the VBA editor & select View Code. There are 3 command buttons. When a button is pressed it enters the same information you have in your Start, Stop or Move macros into the relevant cell on the worksheet & closes userform1. I entered the same commands as per your Subs.

    As for your new problem. It would be better if you attach a file with an example of the error. A quick guess. It looks like you're trying to do two things simultaneously.
    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1