1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

user form(vba macro)

Discussion in 'VBA Macros' started by devendra.dvm, Feb 10, 2017.

  1. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    Hi friends.

    I’m working in stock broking company, I am into commission processing, Every month I need to configure commission structure for multiple Agents (code)
    So I want to simplify the process as I am working on weekends also for the same.

    Scheme: MR

    Plan: w

    Agent: ARN-3077

    Agent Category ID: 1003

    Brok Type ID: 1

    From Date: 01/01/2017

    To Date: 31/03/2017

    From Amount: 0

    To Amount: 9999999999999.99

    Rate: 1.5

    Remarks: Ltr.21789.Dtd.01.02.2017

    City Category: B-15

    frommonth: 366

    tomonth: 1095






    This is the format for the for configuring commission rate. After creating excel file will upload the same in one application.

    Please any one help on the same and create a macro(User form to enter the data) to automate the same.. my requirements is as below

    Suppose If I want to configure commission structure to 100 agents.. 1st I will upload all agent id’s and then I will enter remaining parameters shown in above table after entering all parameters I want an option in user form to choose to select Agent code. After choosing agent code I will hit enter and all fields should get clear so that I will update all fields again with different scheme code.. after finishing I want finish button to hit and all the data get export one excel file…

    Please create one user form as per my requirements....Thank you
    please ignore grammar mistakes.

    Attached Files:

  2. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Yes!
    1) You have there one sheet with data, okay?
    2) How do You upload it (... would like to upload it)?
    3) I added row for 'the format for the for configuring commission rate', okay?
    4) You have there 362 Agents (what are those Your named 100?)
    5) You could select Agents names with Filter-option too, how?
    6) What should happen 'I will hit enter and all fields should get clear'?
    7) Could You make a new sheet to show what would it look after 'Enter'?
    8) Could You make a new sheet to show what would it look after 'Finish'?
    9) Do You want that only 'Finish'-sheet data would be in the new file?
    ... Where do You need 'one UserForm'?
    >> Ideas - Questions - Answers?

    Attached Files:

  3. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    Hi Vletm,

    Thanks for your reply...Please check uploaded attachment for reference...

    In Conditions & Agents sheet: have mentioned Agent list which i want to upload and conditions / Rates to be configured.

    In Template Sheet: Have mentioned user form buttons and requirements

    In outPut Sheet: Have provided required output data.

    Note: Before updating all fields i want to upload agents list( Pls create like this)
    after updating all fields i will hit Apply button so all fields get cleared and updated fields should get added in background.. next i will update same fields with diff scheme code and will select diff agetns.. finally i will hit Finish and all the data should get export into excel worksheet as showed in output sheet.

    Please help on the same...Thank you.

    Attached Files:

  4. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Nice!
    I made a list of questions and
    You didn't make any answers!
    I got Ideas!
    ... but
    That makes the Challenge!
    I don't want just use time to guess > test > guess > test and guess!
    = one step forward two or more steps backward! Not good!
    I could make new questions ... but could I get any answers?
    ... still this is no need to use UserForm-case!
  5. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    iam sorry,...

    yea sure i will answer to all ur questions
  6. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    Hi Vletm,

    Please refer my answers for your questions in earlier message.

    Yes!

    1) You have there one sheet with data, okay?

    Ans1 : Yes


    2) How do You upload it (... would like to upload it)?

    Ans2: Yes I want to upload…I will save Agents list in one excel file and same i want upload



    3) I added row for 'the format for the for configuring commission rate', okay?

    Ans3: Please same format as shown in file(Headings)


    4) You have there 362 Agents (what are those Your named 100?)

    Ans4: Yes..thats sample file to show how output will look like…For example I have mentioned 100Agents but same will increase or decrease as per the instruction from our clients..


    5) You could select Agents names with Filter-option too, how?

    Ans5: Yes..Required filter option to select Agent id’s


    6) What should happen 'I will hit enter and all fields should get clear'?

    Ans6: for example If I am configuring rates for two agents with two different schemes so 1st I will enter for one scheme then I will hit enter and all fields should get clear(Means whatever data I have entered that already get exported to excel sheet) and I will enter other scheme details then I hit finish button and again this data also get export into excel sheet next to previous scheme details




    7) Could You make a new sheet to show what would it look after 'Enter'?

    8) Could You make a new sheet to show what would it look after 'Finish'?
    Ans 7&8: please refer output sheet in my shared file which I have sent with my second msg.



    9) Do You want that only 'Finish'-sheet data would be in the new file?
    Ans9: Yes as shown in shared file



    ... Where do You need 'one UserForm'?

    Yes


    >> Ideas - Questions - Answers?
  7. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Yes .. good answers, but not to my questions...
    like:
    How > Yes ?
    Okay > same format
    100 Agents > gotta modify layout!
    Filter > hmm... Okay .. do it!
    Clear > hmm...
    ... You didn't explain do You want to add one-by-one those Agents or by groups... so both are possible.
    Could You > impossible?
    New file > You'll get it!
    Where > Yes! ... but where?

    Check what kind of file did You wanted? Surprise
    There are minimal instructions too.

    >> Ideas - Questions - Answers?

    Attached Files:

  8. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    Hi Vletm,

    Thanks for your time and effort...

    Please make changes as per the attached exclel.
    Please dont mind and sorry for troubling you...

    Attached Files:

  9. vletm

    vletm Well-Known Member

    Messages:
    2,708
    As I wrote:
    I don't want just use time to guess > test > guess > test and guess!
    = one step forward two or more steps backward! Not good!

    Did You make a new case?
    Your ... make changes ...
    ... means, just delete everything!
  10. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    No no Sorry...
    please dont get angry...thanks for ur effort..
    the file which u have created its good..but some how not reached my expectations...and i know that the mistake is from my side only as i didn't given proper reference to you for the same...now please consider latest attachment for reference...hope u will get clear picture what i am expecting...

    once again i am sorry for the same..
  11. vletm

    vletm Well-Known Member

    Messages:
    2,708
    I don't use 'a.g.y' word, why do You use?
    You wrote:
    hope u will get clear picture what i am expecting...
    which do not work!
  12. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    i am sorry...

    i thot u got angry as iam again and again disturbing you no thats...
    once again i am requesting you..

    please help me..
    please consider my latest attachment...

    i am really sorry for everything..
  13. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    Hi Vletm..Good Morning.

    Pls help me..Pls consider my request.
  14. vletm

    vletm Well-Known Member

    Messages:
    2,708
    You wrote:
    Hi friends.
    I’m working in stock broking company, I am into commission processing, Every month I need to configure commission structure for multiple Agents (code)
    So I want to simplify the process as I am working on weekends also for the same.
    ..then You should know what do mean term 'working'?
    There have to be done the GREATEST PLAN BEFORE even start to work.
    Or do Your company just waist it's money to let employee to do something which won't give money?
    If the company asks 'how' or 'where' ... can You give answers like 'Yes'?
    ... one step forward two steps backward ... and 'free'?
    That is not working at all!
  15. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    Hi Vletm,

    Thanks for your reply..

    Yes i will give answer Yes... i will show them where i got this file and code... i wont say that i created this...

    Dear Friend.... i just wanted to simplify the process as me and my colleagues are spending alot time to configure rates daily basis...some times we use to work late nights also...that's why i am seeking help from you...
  16. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    i can understand that Ur spending ur valuable time for this...

    sincerely I appreciate about the same...

    Last time please consider my request and help on the same...

    Pls consider my last attachment as reference..
  17. vletm

    vletm Well-Known Member

    Messages:
    2,708
    You still continue: 'I want I want I want'
    You had 'a master plan' -> I tired to asked details -> You could make 'empty' answers = more clear -> I tried to do what You has written ->
    You just change so called 'plan' without reasons! Just - 'I want I want I want!'
    1) Try to reread #8 & #9 replies and ...think!
    2) As I wrote, my opinion that Your #8 'attachment' do not work!
    3) There is a positive thing too. Somewhere is 'an idea' that 'routine work should get lighter'.
  18. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    Hi vletm..

    i hve answered ur questions no...i thot u got an idea with my answers..
    if u dont mind...can u please tell me which area u need clarity..

    iwaiting for your reply...thank you
  19. vletm

    vletm Well-Known Member

    Messages:
    2,708
    hmmm..
    1) i hve answered ur questions no...
    means that even You don't know have You answered?
    2) Yes, You answered ... but
    if some asks like is it raining now?
    and You answered like: Yes, I eat ice cream.
    ..helps a lot!
    3) You just change so called 'plan' without reasons!
    If no reason then no idea!
    It's like 'Simon says JUMP!'
    ... me, no need to jump!
  20. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    Hi Vletm,
    in ur file output is not coming as expected..rates are not getting applied and output not getting exported to single file..evry time exporting into new file..thats why i hve changed the plan..
  21. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    that last attachment is my exact requirement...
  22. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    i sincerely apologize for the inconvenience..
  23. vletm

    vletm Well-Known Member

    Messages:
    2,708
    1) Output is JUST as in Your original sample!
    2) Rates comes just as those are written!
    3) It will make a new workbook with Output-sheet data!
    4) If You just want to get new file after every row then it is possible too, just press the button and after that another button to clear output!
    5) You didn't give any idea of new workbooks names - no problem - those will stay opened and You can manually name and save those as You want!
    => if that output is not like Your 'newer version' ... omg ...
    how do You think that I could guess what have You changed before I have seen it? .. There are some 'blacks' and empty rows ... no rules!
  24. devendra.dvm

    devendra.dvm Member

    Messages:
    139
    please check it once agian. in that black cells i wrote description for each output..i just simply keep that empty rows to understand step by step..
  25. vletm

    vletm Well-Known Member

    Messages:
    2,708
    #20 Reply - What was NOT 'challenge'?
    .. that I couldn't guess modified layout of output before I could see it or what?

Share This Page