Saving and changing values based on a checkbox
Dear All,
I am new in VBA excel and for one day I am fighting for the following problem:
I need to have a checkbox in cell F1, which does the following:
If it is checked, then the values in cells A1:E1 stay as they are and do not change.
If it is unchecked, the values are cleared (with .clearvalues) and in the cells A1:E1, the values of the cells G1:K1 are assigned accordingly. I hope you got what I mean and someone is willing to help.
Any ideas would be appreciated!
Thank you in advance!
Hopefully, I have correctly understood your request.
When the checkbox is unchecked, yYou want to clear A1:E1 and copy the values of G1:K1 to this range?
Assuming you have created a check box from the Forms controls and you have linked the check box to a cell, try adapting this code to your needs.
In this code cell A10 is used as the linked cell for the control - change this reference as needed. Put the code into a regular module then assign it to the check box.
If I've gotten it wrong then explain more clearly what you need.
Please Login or Register to view this content.
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Well, Palmetto, first of all, thank you, I am one step further 10x to you.
Secondly, I will try to explain a little better:
if the checkbox is checked, then
the values A1:E1 stay the same. Something like
When it is not checked, then your code can be used.Please Login or Register to view this content.
Here come my follwoing problems: I have about 2000 of these checkboxes and I cannot write 2000 subs for each one, so I need something clever to save me the loads of work.
Second:
I need the code to be done in a way, that whenever you check or uncheck a checkbox it is always working. E.g. if a user checks, then unchecks and then checks and then unchecks the checkbox, then it should be still executing the code.
Hopefully you understand what I mean.
P.S. I was surprised from the fast and useful answer! Bravo!
Last edited by kaligad; 12-17-2012 at 10:59 AM.
First - please edit your post to add code tags as these are require per forum rules. Just select all of the code, then click the button with the # symbol on it.
OK - 2000 check boxes. Yeesh! and Yuck!
I would use a Marlett Check Box
You worksheet will look much more professional and you only have to use a small bit of code.
Thanks again, I have edited it and I have looked through the article.
But my question is how to assign the VBA code based on the action?
How to tell excel what to do when the checkbox is selected and what to do when it is not?
And how to do it 4000 times (2000 times for selection of the checkbox, 2000 times for the deselection?
See if you can adapt/use this. Test it on a copy of your workbook.
You will need to adjust this code for the target range. Not knowing how you have linked your check boxes, I opted to use column-A, which shifts A1:E1 to B1:F1 (and likewise with G1:K1)
Please Login or Register to view this content.
Hi Palmetto,
The solution is really beautiful, but unfortunately I need checkboxes.
The reason for the checkboxes is that in the current solution it is really easy to change the values just by having the selection over them and thus mistakes may happen.
Anyway, once again I really enjoyed it and even for a moment tought to provide it as a solution, but I really think it will not be stable for my purposes.
Do you have an idea how to do it with activecheckbox?
Thanks again
I have done something, but it is still not working...
Can you advise here?
I want to have a message "Text", appearing every time when I press a button, assigned to the macro "Setting Cells Values", for every checkbox I have checked. Thus if I have 40 checkboxes checked I will have 40 messages.Please Login or Register to view this content.
Anyway, later I will change the message to something more useful for me.
Regards,
And I am still fighting - Can someone improve this in order to work?
What should be improved is that the code must always take the row of the checkbox and to execute the changes with it.Please Login or Register to view this content.
ANY help would be highly appreciated (which means I will send you a big virtual thank you
So last question, I think I am almost there
ny idea how should I modify the line from the code:Please Login or Register to view this content.
in order to make sure that it assigns always the value of the current row, on which the checkbox is present to "i" ? I am sure that if this is done, I will not bother you with questions any more until the end of the week.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks