user form(vba macro)

[devendra.dvm]

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.

 

[vletm]

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?

 

[devendra.dvm]

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.

 

[vletm]

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!

 

[devendra.dvm]

iam sorry,...

yea sure i will answer to all ur questions

 

[devendra.dvm]

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?

 

[vletm]

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?

 

[devendra.dvm]

Hi Vletm,

Thanks for your time and effort...

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

 

[vletm]

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!

 

[devendra.dvm]

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..

 

[vletm]

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!

 

[devendra.dvm]

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..

 

[devendra.dvm]

Hi Vletm..Good Morning.

Pls help me..Pls consider my request.

 

[vletm]

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!

 

[devendra.dvm]

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...

 

[devendra.dvm]

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..

 

[vletm]

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'.

 

[devendra.dvm]

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

 

[vletm]

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!

 

[devendra.dvm]

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..

 

[devendra.dvm]

that last attachment is my exact requirement...

 

[devendra.dvm]

i sincerely apologize for the inconvenience..

 

[vletm]

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!

 

[devendra.dvm]

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..

 

[vletm]

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