Trying to reduce the size of code

  • Hi all,


    This is my first post here and I have only be playing with code for a week so be gentle.


    I have manage to create a monster. I have 28 buttons for 28 pieces of equipment. I have 31 specifications that need to be displayed in 31 line charts for each piece of equipment. As you can imagine a little bit of typing and glad I know how to cut and paste.


    The code works. Each button calls a macro and changes the colour of the button so you know which one is selected. I won't bable on anymore and will post the code.




    This one of the 28 macros dumping data from sheet DG # 1 into each chart.




    This is the code that calls a macro and changes the colour of the buttons


    This where the fun starts. In the range comments below the cells U8 and U$2 may be different for each piece of equipment, It is determined by the number of test sample taken/recorded. So the "U" may be an "M" or a "K" but for each of the 31 specs it will be the same.


    Range("A8:U8") (This cell location range has data in it.)


    "='DG # 1'!$B$2:$U$2" (This cell location range has dates in it. Possibly you could examine cells B2 to ??2 to see if it contains a date, not sure if this can be done, as soon as a cell doesn't have a date go back one and use it. This cell letter would also need to be applied to the data range as well.)


    Is there a way I can do an If/Case type of look up? At the moment this sort of thing is way above me so any help would be greatly appreciated.

  • Hello and Welcome to the Forum :smile:


    To give you idea of what might be possible ...


    You could test the following macro:


    Code
    1. Private Sub CommandButton2_Click()
    2. Dim i As Long
    3. Call MacroDG1
    4. For i = 1 To 28
    5. ActiveSheet.Shapes("CommandButton" & i).BackColor = &H8000000F 'grey
    6. Next i
    7. ActiveSheet.CommandButton2.BackColor = &H80000010 'dark grey
    8. End Sub


    Regarding your first macro ... with some more consistency in your worksheet ... you could also have a five line macro ...


    However, it seems you have not kept a logical sequence ... rows 10, 16, 18 to 21, etc ... are missing


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello,


    To make your life a lot easier ... you could insert a sheet fully dedicated to listing all the data you actually need for your 31 Charts ...


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello,


    One more sheet ... but a lot less headaches ... and smooth well structured and shorter macros ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • before run makro press Ctrl + G see if loop is right number using debug.print