le_johney
asked on
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.
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.
ASKER
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
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
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
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
ASKER
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!
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!
ASKER
Any ideas? Or should I add something to help more?
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
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
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
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
ASKER
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.
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.
ASKER
The FormControl code seem to be a problem Mac version cannot overcome...happened to me before.
ASKER
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?
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
if yes then where do you get an error or a yellow line at what instruction and what does it say ?
gowflow
ASKER
OK...glad to hear this is challenging :)
It doesn't work - macro nor button click.
Here is the picture of the bug
code.tiff
It doesn't work - macro nor button click.
Here is the picture of the bug
code.tiff
ok what is the error that you get on this line what does it say ?
gowflow
gowflow
ASKER
OK...glad to hear this is challenging :)
It doesn't work - macro nor button click.
Here is the picture of the bug
It doesn't work - macro nor button click.
Here is the picture of the bug
ASKER
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
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
ASKER
now it says out of memory :)
ASKER
on windows it says System Error &H80070057 (-2147024809). The parameter is incorrect.
ASKER
the delete macro works well though
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
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
ASKER
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!!!
Thank you, seems we'll be there shortly!!!
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).FormControlTy
and Uncomment out this line (remove the single quote at the beginning
'If WS.Shapes(I).FormControlTy
gowflow
Comment out this line (put a single quote at the beginning of it
If WS.Shapes(I).FormControlTy
and Uncomment out this line (remove the single quote at the beginning
'If WS.Shapes(I).FormControlTy
gowflow
ASKER
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...
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
Regards/gowflow
ASKER
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!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER
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
Jan
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
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
ASKER
I am very glad for you! One more interesting question coming :)
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