Solved

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

Posted on 2014-01-07
31
511 Views
Last Modified: 2014-01-10
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.
0
Comment
Question by:le_johney
  • 19
  • 12
31 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39766087
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
0
 

Author Comment

by:le_johney
ID: 39766148
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39766403
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
0
 

Author Comment

by:le_johney
ID: 39766514
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!
0
 

Author Comment

by:le_johney
ID: 39767198
Any ideas? Or should I add something to help more?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39767337
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39767840
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
0
 

Author Comment

by:le_johney
ID: 39769107
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.
0
 

Author Comment

by:le_johney
ID: 39769118
The FormControl code seem to be a problem Mac version cannot overcome...happened to me before.
0
 

Author Comment

by:le_johney
ID: 39769244
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?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39769261
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
0
 

Author Comment

by:le_johney
ID: 39769375
OK...glad to hear this is challenging :)

It doesn't work - macro nor button click.

Here is the picture of the bug

code.tiff
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39769463
ok what is the error that you get on this line what does it say ?
gowflow
0
 

Author Comment

by:le_johney
ID: 39769468
OK...glad to hear this is challenging :)

It doesn't work - macro nor button click.

Here is the picture of the bug

0
 

Author Comment

by:le_johney
ID: 39769479
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
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 29

Expert Comment

by:gowflow
ID: 39769493
ok I am trying a wild guess here.
Try this version
gowflow
SampleOfWork-MAC.xlsm
0
 

Author Comment

by:le_johney
ID: 39769496
now it says out of memory :)
0
 

Author Comment

by:le_johney
ID: 39769510
on windows it says System Error &H80070057 (-2147024809). The parameter is incorrect.
0
 

Author Comment

by:le_johney
ID: 39769516
the delete macro works well though
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39769562
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
0
 

Author Comment

by:le_johney
ID: 39769576
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!!!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39769592
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
0
 

Author Comment

by:le_johney
ID: 39769611
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...
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39769625
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
0
 

Author Comment

by:le_johney
ID: 39769631
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!!!
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39769640
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
0
 

Author Comment

by:le_johney
ID: 39769706
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!
0
 

Author Closing Comment

by:le_johney
ID: 39769710
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39770404
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
0
 

Author Comment

by:le_johney
ID: 39772129
I am very glad for you! One more interesting question coming :)
0
 

Author Comment

by:le_johney
ID: 39772221
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now