Macro - using userform and command button to enter data into a separate spreadsheet row by row creating database

Hi,

I have a fairly easy problem I guess and I know I tried to do this before. I am trying to write a macro that will allow me to copy the data created using the tool u can see in the attached picture.

playground.tiff

Essentially, there should be a button that would allow recording different selection / scenarios into a separate sheet row by row. The database should include columns where the total cost, total reduction and the selected categories and their year of implementation should be recorded. This all should go in ONE row that would be populated after I hit the button "RECORD" to which the macro I am trying to figure out will be assigned.

Also, I would like to add a button that would allow me to delete the database with a little pop-up window asking if i wished to proceed with the deleting procedure.

Is this possible?

Any help will be highly appreciated!

Thank you in advance!

J.
le_johneyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowPartnerCommented:
yes it is possible but the way you paint it with no file attachment make it sound like you want us to design the whole thing for you which is why 'maybe' this question was neglected.

I am sure this is not your intention, and all you want is get closer to your goal. My suggestion to you at this point is to post whatever work you have already and then we can build from there on.

gowflow
le_johneyAuthor Commented:
Hi gowflow,

I understand and hereby I attach a sample of my work for which I need to draft the code. Would you be able to go from here? Thanks.

J.SampleOfWork.xlsx
gowflowPartnerCommented:
WOW !!! I like what you did impressive. I saw graphs and so on, but this is the first time I see a dynamic chart. Congrat on this !!!!

Let me get this correctly

I don't have a problem with the record button but I have a problem with the fact that you want all this information (that is in several rows to be copied into 1 row and you already in sheet database have set the columns to be same as the existing ones)
How can that be done ??

Then

You want to delete the database do you mean a certain item or the whole database ??

What is the purpose of what you are trying to achieve ?? If I know this then I can recommend a way as the way you are asking is not making sense at least not the way you explained it.

gowflow
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

le_johneyAuthor Commented:
Haha...thanks! It's actually quite easy but u better keep it ;) Glad u learned something, too though.

Well, I decided to do it differently as you can see in the 'database' sheet. I would rather have an ID generated and assigned to each isolated decision that the user is going to make - in other words the user will make a discrete choice between the categories A - I by ticking the "include" box. Of course he/she can then tweak the other things - percentage, year, etc. but I only want to put the selected - ticked categories in my database. So let's say User 123 will select categories A,C,D - I will only have those to be recorded in the database sheet. I will then run a pivot table on the multiple discrete choices and sort it and sum it up by user ID.

As far as the database deletion goes - once I hit the delete database button I want a little notification / alert window to pop-up and ask if the user wants to delete it (a little password feature would be useful, too).

Is the purpose clearer now?

Thanks, gowflow! Much appreciated!
le_johneyAuthor Commented:
Any ideas? Or should I add something to help more?
gowflowPartnerCommented:
1) Well when u say user make a decision do you mean that this file could be accessed by different users and we need to save each user credential separately ?

2) When you hit record say we update in the database the info related to all the ticked items with a certain ID

then the user makes an other choice combination and then hit Record we save the combination ticked items after the last item already saved previously giving it a new ID

etc ...

the question is:
How would you recall an item to delete it ? you need a certain lookup in the ID Existing in the database to delete it true ?

or

You want the entire data to be deleted after question asked and passwd confirmation ??

Rgds/gowflow
gowflowPartnerCommented:
Although you have not replied to my last message I assumed the following:

1) Each and every time a user make selections and press on Record he will be prompt to save the data that is selected to the Database with an ID that will be communicated to him which is basically the date time he is actioning the same in the format
dd-mmm-yyyy hh:mm

2) the user can make several save to the database and they all will be recorded after the last item saved.

3) When Delete Database is activated it will prompt for a password and the password is caps sensitive and is:
AmTheBoss
the user can enter up to 3 trials with wrong passwords if it fails he will not be able to delete the records if he succeed to put the correct password he will again be prompt to accept the deletion or refuse.

Pls check all possibilities and test the macro and advise your comments.
Rgds/gowflow
SampleOfWork.xlsm
le_johneyAuthor Commented:
Hi,

thank you so much for the work you've done on this!
However, I am working on Mac and it does not support ActiveX features that seem to be a part of your solution and thus I cannot really test it.
Do you think there is an alternative way for doing this?

Thanks again very much.

J.
le_johneyAuthor Commented:
The FormControl code seem to be a problem Mac version cannot overcome...happened to me before.
le_johneyAuthor Commented:
could we find an alternative that would work without activeX features? seems like there is a condition missing in the code u provided...am I mistaken?
gowflowPartnerCommented:
let me check you are giving me  challenge never tested before !! did you try to run the macro directly ? not from the button ?

if yes then where do you get an error or a yellow line at what instruction and what does it say ?
gowflow
le_johneyAuthor Commented:
OK...glad to hear this is challenging :)

It doesn't work - macro nor button click.

Here is the picture of the bug

code.tiff
gowflowPartnerCommented:
ok what is the error that you get on this line what does it say ?
gowflow
le_johneyAuthor Commented:
OK...glad to hear this is challenging :)

It doesn't work - macro nor button click.

Here is the picture of the bug

le_johneyAuthor Commented:
It says

Run-time error '-2147024809 (80070057)':
The index into the specified collection is out of bounds.

The forums talk about some tweaking of range 1 and -1. Don't really know as I haven't worked with this before either.

Thanks
gowflowPartnerCommented:
ok I am trying a wild guess here.
Try this version
gowflow
SampleOfWork-MAC.xlsm
le_johneyAuthor Commented:
now it says out of memory :)
le_johneyAuthor Commented:
on windows it says System Error &H80070057 (-2147024809). The parameter is incorrect.
le_johneyAuthor Commented:
the delete macro works well though
gowflowPartnerCommented:
ok try replacing this line
For I = 0 To WS.Shapes.Count

by this line
For I = 1 To WS.Shapes.Count

and if it does not work then change it by this line
For I = 0 To WS.Shapes.Count - 1

gowflow
le_johneyAuthor Commented:
Well, I must say you got it working! The first replacement suggestion seems to do the trick. Now when I run it, I get message Item 09-Jan-2014 13:55 updated 0 choices in the database successfully - I tried to change the input table and run it again and still no luck.

Thank you, seems we'll be there shortly!!!
gowflowPartnerCommented:
ok fine let me try other wild guesses !!
Comment out this line (put a single quote at the beginning of it
If WS.Shapes(I).FormControlType = 1 Then

and Uncomment out this line (remove the single quote at the beginning
'If WS.Shapes(I).FormControlType = xlCheckBox Then

gowflow
le_johneyAuthor Commented:
Runs like a charm on windows - however Mac gives me a notification "Object doesn't support this action"...I essentially need to get it working on Mac...but you are a real master...must tell you that...
gowflowPartnerCommented:
ok give me till tomorrow, its midnite here I will test it on my son's MAC !!! first encounter (I love this !!!) and will get back to you.
Regards/gowflow
le_johneyAuthor Commented:
I appreciate it very much! I love it, too...I think it will be very useful and can be used for diverse purposes. Again, thank you very much for your support!!!
gowflowPartnerCommented:
At least am glad it is working somewhere for you. If anything you need to be modified pls let me know so I incorporate the whole thing.
gowflow

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
le_johneyAuthor Commented:
Got it working!!!

you have to change the references you made to the Worsheets such as WS and WSDatabase into the regular format so ActiveSheet and Worksheets("Database") and it will work on MAC. For some reason 2010 supports and 2011 Excel on MAC does not support the dimension declaration as you wrote it originally in your code.

But it works now!

Thank you again very very much! I may have some additional questions but will open a new thread for that.

Great job gowflow - bull's eye - full score!
le_johneyAuthor Commented:
GowFlow is very determined, passionate, committed and helpful expert. He is very quick and hands on and likes intriguing topics. I would definitely work with him on a project again!

Jan
gowflowPartnerCommented:
Thank you very much for your nice comments and glad you were able to fix this. I am open to help you on any issue you may need pls put a link to the question in here I will be glad to assist.

PS Pls specify when you post a question that it has to work on both PC and MAC and always specify the version of Excel you need your solution to work on as we are seeing so many incompatibilities between versions.

Last but not least, with your question you helped me to qualify to a new position that is now Genius which is when you accumulate as Expert 1,000,000 points !!! tks for helping me to get this level passed. :)

Regards/gowflow
le_johneyAuthor Commented:
I am very glad for you! One more interesting question coming :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.