Hello,
I have this code for Checkbox on Userform :
Please Login or Register to view this content.
It tickes Checkbox If cell values are not blank. Code works great, but I need that code for several rows and columns, and different sheets (It's a monthly worksheet). Is there any way that I could drag code like formulas, or do I have to type this code for each Checkbox ?
Any suggestions ?
You can't drag code like a formula.
However you could use the Controls collection of the userform.
I don't know what needs to be changed row/column wise but this might help
Please Login or Register to view this content.
If posting code please use code tags, see here.
I'm not sure what this code means...
Let's say I have only column that need to be changed - I have cells ("C3,C4"), ("C8,C9") and ("C12,C13"). For each must be Checkbox ticked If Application.WorksheetFunction.CountA(Sheet1.Range()) = 2..
How can I create my code then ? There must be 3 Checkboxes, not only one !
The code loops through checkboxes named CheckBox1, CheckBox2, ..., CheckBox10 and sets their value based on the CountA formula.
I used a simple offset so the range would go from C3:C4 to C5:C6 to C7:F9 and so on, which is simply moving down 2 rows each time,
Unfortunately there doesn't seem to be any pattern in the ranges C3:C4, C8:F9 and C12:C13 so a simple offset won't work.
However if you are only concerned with 3 ranges why not just copy and paste then adjust the checkbox name and the range?
I don't have only 3 ranges, I need that for 31 columns and 79 rows - which is exactly 2449 code entries with my existing code
Ok, how can I do that - "copy and paste then adjust the checkbox name and the range?" - Did you mean without VB coding at all ?
ranges are non-contigious as you can see !
Yes I meant without code.
If there is no pattern then I can't see how you can handle this without hard-coding values at some point.
One thing you could try would be to store the checkboxes names and the ranges in 2 separate but equally sized arrays.
Then you could loop through the arrays to set the values of the checkboxes.
By the way, I don't see how dragging would work even if it was formulas you were dealing with.
Whilst you can not use VBA editor to do what you want you could use the worksheet to build your code syntax.
This example allows you to change control counter, column and sheet.
You then copy and paste result into your code.
Are you sure +2k checkboxes is the best approach to your problem though?
Originally Posted by Andy Pope
Ok, and how exactly did you manage this in your example, I don't see how you did It ?
I have attached my sample worksheet, maybe you will understand what I want to do and could give me some advice for different approach ?
Lukael
What exactly do you want to see on the userform?
It's simply formula using parameters to construct the text,
Consider a userform where you select a record by name, from a combo box. The you display information about each day for the user in a listbox.
Ok, how exactly did you mean that ? I don't want to display information about one user for whole month, I want to display information about all users each day.
I attached another sample worksheet and created Userform1, which is activated from rectangular shape with macro. Could you show me example of what you are thinking ?
You could use a combobox to select the date and then have a listbox list the information for all users for that day.
Or you could put all the information in a listbox with a column for the date.
Ok, where I can do that, in Combobox property or with VBA ?
userform with combobox for date and listbox for information display.
Please Login or Register to view this content.
Thanks, that is great, much better than my solution , I think that would take me a month to figure outuserform with combobox for date and listbox for information display.
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