Macro needs a guru to enhance and insert a particular funtion of control execution.

I have been blessed by some help from another member here to create a piece of code that will search any number of worksheets (labelled as sections 1 - 7 within my book to retrieve data from particular points based on whethere a cell contains a value "N" and compiles this information in to a REPORT sheet.

When this code is run from 1 button labbeled Compile report it will create on a worksheet where all data from the book it has searched.

What i want to do now is either:

1: Add on the report sheet at the top some Tick boxes labelled 1-7 + ALL which the VBA code uses to determine which sheets to search, so for example you could tick section 1 or 5 or combination or perhaps a box for all and code would then compile data only from those marked.

2 less favourable option is to create a report sheet for each section and the compile report button would run 8 versions of same code where the code was altered slightly to only search the particular section referenced to the sheet its named after.

In my Attached file which is designed for auditing i have entered some random stuff in to section 4 and in section 1 2 items are highlighted red with a yellow background.

If you use the compile report button you will see it compiles entire report based on ALL sheets that exist.

Ideally if option 1 is used then i would need to tick the section 1 and 4 box or all box to get same report.
if i ticked section 4 only then only section 4 would be compiled, or vica cersa if 1 was ticked and 4 was left blank then the oposite would be true.


I hope this makse sense and you inteligent people can help..

Any help with this would be most appreciated i feel option 1 is probbably the best solution for me personally. so cudos to who ever can resolve this issue..

Audit Form.xlsm

Wow you understand what im trying to do so its all good there, however when i open the file i can see the compiled report ok but the formatting of the columns F to I have disappeared

As for the check boxes "yes exactly what i had in mind" however if i hit compile report button from loading this file a fresh it just comes up with an error <run time error 1004 - unable to get the OLEObjects property of the worksheet class> I also can not click the check boxes on or off to change which sheets will be searched for data to compile on report sheet.

I noticed that one or more of your Sheet Names contains trailing blanks. That did not present a problem to me, but it may cause you unintended consequences sometime in the future.

?
if so i need those fields to be formatted how they were effectively blank cells to which the person i give report to would use to enter data manually to address issues i have reported.
Also if it means anything with regards your comment also, the guy who helped me originally with this code made it so that if for example in future i added more sheets such as Section 8 Section 9 etc the reporting would work for those also, in a way future proofing it a little.

im wondering if this an issue with my pc as i just had to do a full reinstall of everything and im wondering if i missed something important to Excel


Any ways thank you for taking time to try and help me and i hope you can help me further :P

This works perfectly now im such a happy bunny!!

in the picture it has copied over the colour of the cells also? but when i compile it does not just the text and text colour any reason for this? ( this is not an issue but im curious)

how do i now edit size of the check boxes? if i so wished or add another for say sheet 8 if i created a sheet 8 ( Section 8)

in the picture it has copied over the colour of the cells also? but when i compile it does not just the text and text colour any reason for this? ( this is not an issue but im curious)

I don't know. It may have to do with the following code that is not supported in Excel 2003:

Please Login or Register  to view this content.

See what happens when you comment out the above code.

how do i now edit size of the check boxes?

Unfortunately, the size of the text boxes is fixed by Excel and can't be changed. I will try to post an update in a couple of days, with a different type of control that has the look and feel of a CheckBox, but can have it's size customized.

if i so wished or add another for say sheet 8 if i created a sheet 8 ( Section 8)

Changes have to be made in two places:
In ModUtilities, the following number has to be changed:

Please Login or Register  to view this content.

In ModUtilities, the following number may have to be changed for PrettyPrint spacing purposes:

Please Login or Register  to view this content.

In Module1, the following number has to be changed.

Please Login or Register  to view this content.

Unfortunately, the size of the text boxes is fixed by Excel and can't be changed. I will try to post an update in a couple of days, with a different type of control that has the look and feel of a CheckBox, but can have it's size customized.

Wow thanks so much i look forward to that...

i will also try the comment out idea see what happens and of course try out what you said about addtional sheet bits also..

Again many thanks for time you have spent on this. this helps me so much

wow, thanks buddy, i will check this out tonight when i get home from work. Lewis = guru. guess putting that word in my title got the reply from the right guy

When i change the size of the box or font size of the label using the code section you have instructed. nothing happens. if i save the file and reload file, it remains the same size? (when changing code values is there a shortcut way to update the file without saving reloading?)

I'm sorry. I forgot to explain how it works. After you change one of those items, run macro CreateActiveXLabelsOnSheetReport(). That will delete the Pseudo CheckBoxes, and then replace them with new ones.
-------------

The number next to the box is really close to the box and i tried to reposition but same issue as above.

I didn't think of an adjustment for that. Good catch. To move the number to the left do the following in Sub
CreateActiveXLabelsOnSheetReport(). Change line 43 to (adding xNumberLabelOffset):

Please Login or Register  to view this content.

After line 31 add the following line (defining the value of the offset constant):

Please Login or Register  to view this content.

-------------

The x when clicked is aligned at the bottom almost of the box can i centre this somehow? it seems differnt when highlighted?

This is a little more difficult. You have to change one or more of the following values until you get the look that you want:

Please Login or Register  to view this content.

finnaly when compiled the form leaves no borders around F1 - I1 to the level of the data (example when compile section 4 borders of F1 to I1 should be done to row 32 see pic)

To the best of my knowledge, I didn't do anything to change your report format. I can't see your picture, because the upload .jpg file didn't take. See my attached .jpg file (in a .zip file - because I can't seem to upload .jpg files correctly - they show as 157 bytes just like yours).

Lewis

Screenie 1.jpg

Again thanks for the reply. yeh i figured it a was something i was doing this morning i could only get the file to show file details in preview using chrome but soon as i opeend in firefox or ie it shows up. weird!! i could see your pic within zip file.

i have tried to post pic again if it works where i have highlighted the boxes that seem to show no borders now. the code i had used originally did this automatically. Is im no pro with original code im wondering if i did something that worked but not in the right way so with your changes it now deletes it or overwrites it somehow.

Basically format wise if there is text within the cells populated by compile then columns F to I although empty i would like the boxes to have single line borders if this makes sense.

i dont expect an answer over weekend but have a beer on me wont ya

Hi,

For borders around cells, see the attached sample file and the code below.

Lewis

Please Login or Register  to view this content.

greetings i hope you had a good xmas,

i have got back to working on this project and have tried to adjust the size and position of the boxes as you have explained how, but when i use the macro delete check boxes it removes them fine, but when i hit create it returns an error code,

It says "error 1004 can not insert object"

when i hit debug it highlights the following in yellow

'Add the 'Active X' Check Box
With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Left:=xLeft, _
Top:=xTop, _
Width:=xCheckBoxWidth, _
Height:=xCheckBoxHeight)

strangly i had same issue on both versions of the files you gave me.

Before i try and adjust size and position using your above instructions to which you say i need to once changes use the create macro. i need it to work 1st.

i know you mentioned excel versions but im hoping this is something you can help fix.


Regards Dave.http://www.excelforum.com/newreply.p...8094&noquote=1

Hi Dave,

Please upload a file you are having trouble with. The link in post #14 is no good.

Lewis

Im not sure even what that link was? i dont recall adding anything as the files were what you had done for me

Anyways you did 2 versions 1 with pseudo boxes another with labels, but they seem to do the same error if i select the sheet report and use macro to delete check box it removes them. if i then run create check boxes it fails.

Obviously if i want to change the size of box or alignment etc as discussed previosuly i cant apply those changes. i did try it at home on my home pc also but it had same version of excel and still failed.

Both file versisons are below.. Thanks again.

ExcelForumAuditForm2-2014-11-15-19-48.xls
ExcelForumAuditForm3-2014-11-19-2014-08-41.xls

The code still works fine for me using Excel 2003.

I think you may be the victim of a Microsoft December 14, 2014 update. One of the following may help you resolve the problem.

The first item is the easiest to do, but probably won't work:
a. Make a copy of the offending sheet
b. Delete the original offending Sheet
c. Rename the copy back to the name of the original

The problem may possibly be corrected by deleting temporary files like *.exd and EXCEL.box in %AppData%\Roaming\Microsoft\Forms and in %temp%\VBE.

http://support.microsoft.com/kb/3025036/EN-US
http://blogs.technet.com/b/the_micro...-updates-.aspx

Please let me know how you make out.

Lewis

i tried to do it manually but it failed, copy / delete failed, HOWEVER
The 1st link allows you to download a tool from microsoft for doing the 2 sugestions which i did and restarted pc and it worked. now i can delete the controls and create them.

I will need to speak with IT guy to see if he can apply this fix to all pc's that may use the files.

I will later have a play and see if i can adjust accordingly for nicer neater effect.

With the second file it has disable and enable label events? what does this do?
also has something about polling?

again thanks for help

I'm glad you got the software to work.

With the second file it has disable and enable label events?

The answer is complicated and simple at the same time.

The original design used CheckBoxes which were too small. When you Clicked a 'CheckBox', Excel automatically put (or removed) a CheckMark in the CheckBox.

The latest design uses Labels to simulate CheckBoxes. To put (or remove) an 'X' in the 'Label' a software assist is required. The assist mechanism is in the form of an Event Handler for each CheckBox. Usually there would be one Event Handler in the Sheet module for each label, and would be called 'Label1_Click(), Label2_Click() etc. Excel automatically knows that when you CLICK Label1, that 'Label1_Click() is supposed to be activated.

In this case there is only ONE Event Handler for all the labels, called 'myLabel_Click()' located in Class Module 'ClassLabelEvent'. Excel has to be told that 'myLabel_Click()' will be the Event Handler for all the labels. This is done by Sub EnableLabelEvents() which is called automatically when you open the workbook (see Workbook_Open()). In a similar manner Sub DisableLabelEvents() is called automatically from Workbook_BeforeClose() when you close the Workbook. Technically speaking DisableLabelEvents() is probably not necessary, but it is good programming practice.

also has something about polling?

Sub PollLabelsOnSheetReport() in module 'ModActiveXLabels' is a test routine I used during Software Development. It is not needed in Production software, but it is a useful diagnostic tool when a problem arises. It polls (reads) the contents of the Labels, and displays the contents of each label (BLANK or 'X') in the IMMEDIATE WINDOW. The Immediate Window is accessed in the debugger either from the 'View Menu' or by pressing CTRL G.

Lewis

I apologize. It's all my fault. Your formatting code was not compatible with my computer (Excel 2003), so I put in a line to exi the formatting routine before it got to the spot that gave me a runtime error.

Very simple fix. In Sub consolidateReport() in Module1, the line in red below (should be line 69) has to be deleted, and your formatting will return to it's former splendor.

Please Login or Register  to view this content.

I apologize again profusely. The problem should never have happened.

Lewis

I would gladly buy you a drink if i could, no appology needed you are the helper i am the helpless

im glad it was an easy fix

YAY it now works really well.... i got 1 issue though and i dont know what to do.

If you take the second file from above which is what im currently goning to use as i like it better.

since deletion of that line 69 it all works great but try this. deselect all tick boxes and hit compile. It then mucks up all the formatting that exists in the header rows.

It does the same thing also if you tick a box but where no non conformances exist to copy over its like there is no tick boxes selected and thus nerfs that formatting up... i think it needs to stop at a certain point if there is nothing to copy over from those section tabs rather than continuing on by trying to apply formatting if there is nothing to format it does it where it sits.

is this fixable you think? hope i made sense.

When we removed line 69, we freed the ugly reformatting Monster. The following should take care of the formatting problem. I also added a line to allow the code run with Excel 2003. Changes are in red:

Please Login or Register  to view this content.

Lewis

hahah ugly formating monster = ME mwhahahahaha

Thanks buddy il give this a try

Just wanted to say this is working really well, have used it a few times now and i have no major issues...

i do have one question for future improvements i may wish to try.

Where in the code it instructs to skip the top 7 rows or row numbers as defined is it possible to change code slightly to enter consolidated data between that defined row and another row number? This would allow me to create a small section under the consolidated data that remains static.

Example being top 7 rows repeat each page and the data is entered starting from the row after..

if with no data consolidated there would be those top 7 rows with a say another 5 rows of information i place in could the consolidated data be INSERTED between that 7th and technally 8th row moving my 8 onwards rows down ?

Where in the code it instructs to skip the top 7 rows or row numbers as defined is it possible to change code slightly to enter consolidated data between that defined row and another row number? This would allow me to create a small section under the consolidated data that remains static.

Example being top 7 rows repeat each page and the data is entered starting from the row after..

if with no data consolidated there would be those top 7 rows with a say another 5 rows of information i place in could the consolidated data be INSERTED between that 7th and technically 8th row moving my 8 onwards rows down ?

Each time I read this, my head spins in confusion more and more. Please post an example, or describe step by step what you want (e.g. copy rows 1 thru 5 from this sheet, add this on the next row, copy as in the existing report next, etc.).

I need a little more direction, before I can describe what to do.

Lewis

Yeh sorry you have done so much for me, i forget that sometimes i bable

Ok So file im using is here:Audit Form.xls

Essentially the code compiles data below row 5 allowing me to have my Format of each column with its headers etc. When the compile button is pressed it enters that data starting from row 6 going down.

What i want to do is add another table of data say row 7 onwards like in pic1 so that when i hit consolidate it INSERTS or creates new rows between the 2 lots of data i have there. so for example it would look like pic2 when consolidated? does this make more sense?pic1.jpgpic2.jpg

i had another look at this today and wasnt able to figure out how to do this.

been using the form without for a while now and works really well otherwise, if hwoever you are able to help in this regard id be most appreciative as the kind of table i wish to place underneath will comprise of mini summary of non conformances like on each section tab at the bottom.

I apologize for the delay in responding, I've been very busy lately. I can do what you want.

It would help a great deal if you could upload a file that contained:
a. a Report Sheet that contained the added items, and
b. A description of where the new tables came from (and/or a worksheet that contained the new table actual locations).

Even after you upload it, I can't promise a response in less than a week or two.

Lewis

Its no problem any time you can spare is always appreciated.

Ok, i hadnt made (said table) yet but i went ahead and made it.

In this file attatched sheet "Report" already has the extra table there (Totals).
I have entered some data in section 7 and selected this in the active x check box.

When you press consolidate it will input the data from Section 7 starting from row 6 but immediately delete the new TOTALS table.

What i would like is to Insert data from row 6 as normal and push down the table contained in rows 7 to row 10 essentially inserting between the HEADER rows and SUMMARY Rows

hope this makes sense, i left a copy of the report sheet in file too as when you hit consolidate you cant CTRL Z undo..

Audit Form.xlsm

When this works all i need to do is enter a formula to search column E for instances of each category of non-conformance "MI,MA,MAF,C" to enter that in the new table.

This would essentially complete the reporting process...

Hi Dark,

I apologize for the delay, but I had a lot of other things I had to do first. Replace Module1 with the following code. Changes are in red.

Please Login or Register  to view this content.

I can add code that either:
a. puts formulas in the totals boxes or
b. Counts the items of each type

In order to do that you have to tell me the rules on how to get those numbers.

Lewis

greetings LJ,
no problem i understand as always i appreciate your help.

So to answer your question, essentially ifd i have say 5 non-conformances in section 7 when consolidate is used. the report form will then consists of 5 reported non-conformances and wil lenter the relevant information from that into the report.

The new table: all it needs to do is search Column E for the different types of non conformance and report the number of occurances in the relevant section of table and then total it up. see image...

in the picture i just entered the numbers manually..totals field just adds the above 4 rows together which i conditionally formated to be same colour text wise as background to give illusion of blank cell.

FYI i only have a couple of categorys of non-conformance MI = minor, MA= Major, MAF= Major Fundamental and C = Critcal which you can see in P3 of section 7 tab

hope this makes sense.

example pic.png

Hi,

Thanks for the excellent instructions. I added formulas that will be put in the proper place each time the report is generated. The following routine has been modified. Remove the old routine with the same name, and replace it with this one. Changes are in red.

Please Login or Register  to view this content.

Lewis

Thank you So SO SO much, this works like a dream!!!!

I have incorporated this into my daily work and it saves sooooooooo much time in generation of a report now.

i consider this post now solved and complete, if i could just ask one last thing.

in a different version of the labels on report sheet when consolidated it would copy over not just data but also the format of the sections ie background colours etc.

this is not done in this version of file and i was wondering if its possible to do that? if i recall there was an issue with the version of excel i used hence when you had file on yours it worked that way for both.

My version of excel is 2010 for your information and to be honest im happy the way it is but it would be a nice added bonus if it could do that also.

Again thanks for everything..

I'm glad everything worked out. Thanks for the rep points.

The following code in Sub consolidateReport() gets rid of the background color. You added this code before you posted the first version in this thread.

Please Login or Register  to view this content.

Comment out or delete the above code to get your colors to return.

Lewis

This worked also thank you,

the only issue i have however is where there is created coloumns not referenced in the consolidated function ie. Column A (item No and Columns F to I there is nothing to refer to for formating. so its just a white background.

Can we code this to be same background as Cell E6? or colour RGB 242, 242, 242?

Its no biggy if it cant be, its purely for visual enhancement

Column A (item No and Columns F to I there is nothing to refer to for formatting. so its just a white background. Can we code this to be same background as Cell E6?

Try the changes below in red:

Please Login or Register  to view this content.

Lewis

hahaha WICKED...... thank you so much that worked perfectly..

Thank you for everything you have done for me, trully appreciate all your effort and time..

Kind regards
Dave.R

Greetings,
been a while and been using the file alot, it really helps speed things up.

Whislt using it"CF143 SCLA - Internal Systems Audit Form (BRCv6) Issue 1.xlsm" i have come accross a small problem or bug? (see pic 1 and 2)

the formatting seems to disapear after a certain number issues / non-conformances are consolidated.

Not sure why this is happening and i have lived with it for a while whilst i tried to find issue but not been successful.

If you could or anyone could take a look see if they can see the issue i would be most appreciative.
Pic1 is section 4 only seleceted
pic1.jpg

Section 2 is 4 + 5 selected.
pic2.jpg

I have seen something like this when there is bad data in one of the section sheets. There must be data in columns I, K, and P. This 'feature' has always been there and is part of your original code. Tomorrow, I will see if I can correct the original code, to reduce the possibility of a problem like this.

Lewis

i appreciate that,

i confess i never noticed it before its just this last 2 weeks where i have done section 4 and 5 this issue came up as a result of many issues in those sections.

I think I have isolated the problem to bad data in the form of blank cells. This not only caused the white cells at the bottom, but also caused data to shift.

See the attached sample workbook which contains old code and displays how the error occurs.

The following corrected macro should fix the problem.

Please Login or Register  to view this content.

Lewis

Sorry been ill so not been working on this for a week or so. i could plainly see in your example what the issue apeared to be.

Where consolidation looks for data in a box and returns that info from that row pushed data down if there was nothing to report.

The new code will report even the blanks now providing "N" is entered for a given row.

Works and again i thank you, hopefuly no further issue will arise through actual use

Cheers again