+ Reply to Thread
Results 1 to 43 of 43

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

  1. #1
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

    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

  2. #2
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    Hi Dark,

    Try the attached workbook which has 'Active X' CheckBoxes. I provided several utility routines to allow you to customize the CheckBoxes to your liking. I modified your Report routine to read the values of the CheckBoxes in the for loop.

    Please note that I used Excel 2003, which may delete some features of your version of Excel. Use my file for test purposes only, and copy the macros to your file.

    'Active X' CheckBoxes don't always travel well, especially between different versions of Excel. The Utility routines will allow you to Delete and Replace the CheckBoxes if this is a problem. The CheckBoxes are renamed to 'CheckBox1' thru 'CheckBox7' (so 'CheckBox3' is associated with 'Section 3' etc.

    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.

    The following is the code in 'ModUtilities'. It is only needed to create the CheckBoxes and is not needed in a production environment:
    Please Login or Register  to view this content.
    Lewis

    PS. Please note there is a typographical error in the code above and in the file that will cause a 1004 runtime error. On line 63 in 'module ModUtilities' there is a typo that originated when I was cleaning things up. The error has been corrected in the code above.

    The old line is:
    Please Login or Register  to view this content.
    The new line should be:
    Please Login or Register  to view this content.
    The attachment has been deleted and is included in post #4.
    Last edited by LJMetzger; 11-15-2014 at 08:53 PM. Reason: Added Postscript; Deleted attachment which was in error

  3. #3
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  4. #4
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    the formatting of the columns F to I have disappeared
    I didn't notice anything with the formatting, but I don't know what it is supposed to look like. My code shouldn't affect formatting, but you never know. Attached is a .jpg screen capture (in .zip file) of a report I ran using the Checkboxes.


    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.
    The 1004 error is my fault. On line 63 in 'module ModUtilities' there is a typo that originated when I was cleaning things up.

    The old line is:
    Please Login or Register  to view this content.
    The new line should be:
    Please Login or Register  to view this content.
    The error should be corrected in the attached Excel file (in the .zip file).

    If the 'Active X' CheckBoxes continue to cause problems, I can change them to 'Forms' checkboxes, which don't look at nice, or can come up with another similar solution.

    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.
    What I meant by this was that one or more sheets have extra characters in the name. For example Sheet 'Section 7' is really 'Section 7 ' (with an extra space at the end of the name).

    Lewis
    Attached Files Attached Files
    Last edited by LJMetzger; 11-16-2014 at 11:43 AM.

  5. #5
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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)

  6. #6
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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.

  7. #7
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  8. #8
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    Hi,

    I replaced each 'Active X' CheckBoxes with two 'Active X' Labels. One label contains the number of the section, and the other contains a 'Pseudo' CheckBox that is really a label. When you click the 'Label', the value toggles between ' ' (BLANK) and 'x'.

    The following is a code excerpt (irrelevant lines to this discussion have been deleted) containing items you can modify to change either:
    a. the number of Sections that have 'Pseudo' CheckBoxes.
    b. the appearance and spacing of the 'Pseudo' Checkboxes.
    Please Login or Register  to view this content.

    The toggle mechanism requires a bit of additional code. See the attached sample workbook written in Excel 2003. Try out my sample workbook. If you like it, to move the code to your workbook you will have to do the following:
    a. Backup your file
    b. Import Module 'ClassLabelEvent' (class module) - contains the code that toggles the Pseudo 'CheckBoxes'.
    c. Import Module 'ModActiveXLabels' - contains the code that:
    (1) Creates the Pseudo 'CheckBoxes' (needed during development only).
    (2) Enables/disables Pseudo 'CheckBox' click events. This is needed when the workbook is opened/closed and is done automatically in the 'ThisWorkbook' module.
    (3) Incorporate the changes I made to Sub consolidateReport().
    (4) Copy the code from my 'ThisWorkBook' module.

    Sub consolidateReport() code follows (my changes in red):
    Please Login or Register  to view this content.

    'ThisWorkbook ' code follows:
    Please Login or Register  to view this content.
    Lewis
    Attached Files Attached Files
    Last edited by LJMetzger; 11-19-2014 at 10:03 AM.

  9. #9
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  10. #10
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    Had a chance to look over this new file and its AWSOME its perfect!!!! this new file copys over the background colours and everything which is great. will be easy to use this going forward will play at weekend with adding another section or 2 to get used to that process for when or if i need to use it i nthe future.

    do have a few small niggly things with it maybe you can help with?

    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?)

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

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

    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)


    Again thank you so much for the time you put into this, i find it fascinating looking at the code and see how some things work but ignorently not understanding 99% of it
    pic b.jpg
    Last edited by Darkprophecy; 11-21-2014 at 05:08 AM. Reason: forgot pics

  11. #11
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  13. #13
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    Hi,

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

    Lewis

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  15. #15
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    Hi Dave,

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

    Lewis

  16. #16
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  17. #17
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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

  18. #18
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  19. #19
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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

  20. #20
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    ahh wow it is beyond my unsderstanding all i know is it now works, with the second version with the labels i still get a slight formatting issue where column F to I do not have created borders for consolidated data. and the 1st column with item number needs to be centred column A.

    Looks like A i placed an arrow to show you where i mean theres no borders around 1st column A and blank columns from F to I where data will be entered by user when they recieve compiled audit. Column A needs to be centered also which is odd because i recall mentioning this before and you said i think at the time that my formatting was not changed. it doesnt do it in the 1st file or rather it does thes formatting still.
    A.jpg

    Picture B is what it should look like after compile. Borders around A + F to I and A is centre alighned.
    B.jpg
    Maybe this is an simple fix i dont know.
    Last edited by Darkprophecy; 01-12-2015 at 10:30 AM. Reason: forgot pic links

  21. #21
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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

  22. #22
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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.

  23. #23
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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

  24. #24
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    hahah ugly formating monster = ME mwhahahahaha

    Thanks buddy il give this a try

  25. #25
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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 ?

  26. #26
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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

  27. #27
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  28. #28
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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.

  29. #29
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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

  30. #30
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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...

  31. #31
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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

  32. #32
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  33. #33
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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

  34. #34
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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..

  35. #35
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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

  36. #36
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  37. #37
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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

  38. #38
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  39. #39
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

  40. #40
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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

  41. #41
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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.

  42. #42
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

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

    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
    Attached Files Attached Files

  43. #43
    Registered User
    Join Date
    08-27-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    96

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro or Funtion to insert dashes in a number
    By mort.marshall.20 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-29-2014, 06:00 PM
  2. [SOLVED] Userform multipage control exit events code execution not completed before next user entry
    By jane serky in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-10-2013, 04:39 AM
  3. [SOLVED] Code execution stops after .insert method
    By deucejmp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2013, 06:43 PM
  4. How to control/pause SQL Query Execution?
    By Peter Bernadyne in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2007, 02:10 PM
  5. [SOLVED] i push the fx and stupid insert funtion box appears
    By patrick in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2005, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1